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

Question

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;

Answer 1

@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:

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.