How to make a mapping from c# method to a SQL function

JJ TT 141 Reputation points
2023-05-21T15:59:57.3233333+00:00

Goal:
Get value from DBFunction TestBool by using user-defined function mapping (EF UDF binding)

Problem:
I don't get enough information about how to make the DbFunction TestBool (C# code) to be working.

What part of the code am I missing.

I need a simple and pedagogical explanation about how to achieve the goal.

Other info:
*Using .net 6 with entity framework core 6
*I tried to apply instruction from this page (https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping) I still don't know how to apply it.

Thank you!


using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace CreateFunctionApp.Entities
{
    public partial class JanaruContext : DbContext
    {
        public JanaruContext()
        {
        }

        public JanaruContext(DbContextOptions<JanaruContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Ttest1> Ttest1s { get; set; } = null!;
        public virtual DbSet<Ttest2> Ttest2s { get; set; } = null!;
        public virtual DbSet<Ttest3> Ttest3s { get; set; } = null!;

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Ttest1>(entity =>
            {
                entity.ToTable("TTest1");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.Date)
                    .HasColumnType("datetime")
                    .HasColumnName("date");

                entity.Property(e => e.Isbn)
                    .HasMaxLength(50)
                    .IsUnicode(false)
                    .HasColumnName("isbn");

                entity.Property(e => e.Score).HasColumnName("score");
            });

            modelBuilder.Entity<Ttest2>(entity =>
            {
                entity.ToTable("TTest2");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.Date)
                    .HasColumnType("datetime")
                    .HasColumnName("date");

                entity.Property(e => e.Isbn)
                    .HasMaxLength(50)
                    .IsUnicode(false)
                    .HasColumnName("isbn");

                entity.Property(e => e.Score).HasColumnName("score");
            });

            modelBuilder.Entity<Ttest3>(entity =>
            {
                entity.ToTable("TTest3");

                entity.Property(e => e.Ttest3Id).HasColumnName("TTest3Id");

                entity.Property(e => e.Date)
                    .HasColumnType("datetime")
                    .HasColumnName("date");

                entity.Property(e => e.Isbn)
                    .HasMaxLength(50)
                    .IsUnicode(false)
                    .HasColumnName("isbn");

                entity.Property(e => e.Score).HasColumnName("score");
            });

            OnModelCreatingPartial(modelBuilder);
        }


        [DbFunction]
        public static int TestBool(bool test)
        {
            throw new NotImplementedException();
        }


        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}
using CreateFunctionApp.Entities;
using Microsoft.AspNetCore.Mvc;

namespace CreateFunctionApp.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {
        public WeatherForecastController(ILogger<WeatherForecastController> logger, JanaruContext db)
        {
            this._logger = logger;
            this._db = db;
        }

        private readonly ILogger<WeatherForecastController> _logger;
        private JanaruContext _db;

        [HttpGet(Name = "GetWeatherForecast")]
        public string Get()
        {

            var ttest1s = _db.Ttest1s.ToList();
            var ttest2s = _db.Ttest2s.ToList();
            var ttest3s = _db.Ttest3s.ToList();

            var dddf = JanaruContext.TestBool(true);



            return "Test";

        }
    }
}


CREATE TABLE [dbo].[TTest2](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [isbn] [varchar](50) NOT NULL,
    [date] [datetime] NOT NULL,
    [score] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

----

CREATE TABLE [dbo].[TTest1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [isbn] [varchar](50) NOT NULL,
    [date] [datetime] NOT NULL,
    [score] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

----

CREATE TABLE [dbo].[TTest3](
    [TTest3Id] [int] IDENTITY(1,1) NOT NULL,
    [isbn] [varchar](50) NOT NULL,
    [date] [datetime] NOT NULL,
    [score] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [TTest3Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

----

CREATE FUNCTION TestBool (
    @test BIT
)
RETURNS BIT AS
BEGIN
    RETURN @test
END;
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
724 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,613 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Jack J Jun 24,491 Reputation points Microsoft Vendor
    2023-05-22T07:45:19.0666667+00:00

    @JJ TT, Welcome to Microsoft Q&A, based on my test, I reproduced the same problem with you.

    Also, I find that the c# mapped method can not be called directly and we have to use it in linq query.

    Here is a code example you could refer to.

      protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDbFunction(typeof(ExampleContext).GetMethod(nameof(TestBool), new[] { typeof(bool) }))
        .HasName("TestBool");    //-> here you need to add the code
          ..............
        }
    
    
        [DbFunction]
        public  bool TestBool(bool test)  // we need to define the return type as bool type the same as bit in sql server
        {
            throw new NotImplementedException();
        }
    
    
    
    

    After changing the above code, you could try the following code in your controller:

      [HttpGet(Name = "GetWeatherForecast")]
            public string Get()
            {
    
                var ttest1s = _db.Ttest1s.ToList();
                var ttest2s = _db.Ttest2s.ToList();
                var ttest3s = _db.Ttest3s.ToList();
    
                ExampleContext exampleContext = new ExampleContext();
           
                var result = from test1 in exampleContext.Ttest1s
                             where exampleContext.TestBool(true) == true
                             select test1;
    
    
    
                return "Test";
    
            }
    
    
    
    

    Then, you could check his sql query if used the related sql function:

    User's image

    Hope my solution could be helpful for you.

    Best Regards,

    Jack

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

    0 comments No comments