Retrieve value of Scalar Function

Question

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

Thank you!

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

---

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);
    }
}

---

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
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;
            }
        }
    }
}

Answer 1

Sql function can not be called directly, they are used anywhere you would use a column value in a sql statement.

The simplest sql would be:

Select dbo.functest()

So in EF you need a select

var test = ss.Select(r => testDbContext.FuncTest()).First();

Which would generate the above sql

Answer 2

The code don't work.

It is helpful if you share the enough code to reproduce what "don't work".

The following is working example straight from the EF Core Raw query documentation.

[HttpGet]  
public int Get()  
{  
    var result = _context.Set<MyFunctionResult>().FromSqlRaw("select dbo.FuncTest() as value").FirstOrDefault();  
    return result.Value;  
}  

Model

    [NotMapped]  
    public class MyFunctionResult  
    {  
        public int Value { get; set; } = 0;  
    }  

Registration

protected override void OnModelCreating(ModelBuilder modelBuilder)  
{  
    modelBuilder.Entity<MyFunctionResult>().HasNoKey().ToTable("MyFunctionResult", t => t.ExcludeFromMigrations());  
    base.OnModelCreating(modelBuilder);  
}