Goal

Enable to use a SQL Server scalar-valued function when you use C# application by using Entity Framework Core.

Problem

The code doesn't work when I want to retrieve the value of SQL scalar function using EF Core in my C# application.

What part of the code am I missing?

Info:

• .Net Core version 5
• Using C# application console
• Using EF core v5

Thank you!

Code:

CREATE DATABASE testDB;

CREATE TABLE Persons
(
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Persons (PersonID, LastName, FirstName)
VALUES (1, 'Cardinal','Tom B. Erichsen'),
       (2, 'Cardinal','Jerry B. Erichsen'),
       (3, 'Cardinal','Ben B. Erichsen'),
       (4, 'Cardinal','James B. Erichsen');
    
CREATE FUNCTION dbo.FuncTest()
RETURNS int 
AS
BEGIN
    RETURN 5
END

C# code:

using System;
using System.Collections.Generic;

#nullable disable

namespace TestConsoleApp.Models
{
    public partial class Person
    {
        public int PersonId { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
    }
}

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

#nullable disable

namespace TestConsoleApp.Models
{
    public partial class testDBContext : DbContext
    {
        public testDBContext()
        {
        }

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

        public virtual DbSet<Person> Persons { get; set; }

        [DbFunction("FuncTest", "dbo")]
        public static int FuncTest()
        {
            throw new NotImplementedException();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "Latin1_General_CI_AS");

            modelBuilder.Entity<Person>(entity =>
            {
                entity.HasNoKey();

                entity.Property(e => e.FirstName)
                    .HasMaxLength(255)
                    .IsUnicode(false);

                entity.Property(e => e.LastName)
                    .HasMaxLength(255)
                    .IsUnicode(false);

                entity.Property(e => e.PersonId).HasColumnName("PersonID");
            });

            modelBuilder.HasDbFunction(() => FuncTest()).HasName("FuncTest").HasSchema("dbo");

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

using System;
using TestConsoleApp.Models;

namespace TestConsoleApp
{
    public class Program
    {
        static void Main(string[] args)
        {    
            using (var ss = new testDBContext())
            {
                var test = testDBContext.FuncTest();

                int test2 = 23;
            }
        }
    }
}

CodePudding user response：

Answer from Luuk

var x = ss.Persons.Where(x => x.PersonId < testDBContext.FuncTest()).ToList();

Entity Framework Core, UD sql function throws System.NotImplementedException: 'The method or operation is not implemented.'