Entity Framework Code-First Execute Scalar-Valued Functions

Question

How can I execute a scalar function using code first? Below is what I have tried but only the query itself is being returned, not the return value.

using (var dbContext = new FTTRContext())
        {

            queryResult =
                dbContext.Database.SqlQuery<string>("SELECT [dbo].[ufnGetTotalUsers] (GETDATE())").ToString();
        }

Answer 1

SqlQuery returns an instance of DbRawSqlQuery. This class is an enumerable and it expects you to enumerate it via either the standard LINQ operators, or via foreach, etc. .ToString() on this object simply returns the query that will be executed. To get the result you want, use .Single() or .SingleAsync().

queryResult = dbContext.Database
    .SqlQuery<string>("SELECT [dbo].[ufnGetTotalUsers] (GETDATE())")
    .Single();

This should return the scalar string result you are looking for.

That being said, your query looks like invalid SQL. Are you just trying to just get the date from SQL Server? If so, the query should probably be SELECT GETDATE(). Once you do that, you might have to use .SqlQuery<DateTime>() since the type of that value is not a string.

Answer 2

An updated answer for EF Core would be described here

TL;DR: Make a keyless entity and use .FromSqlRaw. Note: Name the column in your query!

You can omit the stored procedure. In the following example I have used a sequence directly:

Entity:

    public class MySequence
    {
        public int MyValue { get; set; }
    }

Db Context:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<MySequence>().HasNoKey();
    }

Usage:

public static IntSequence GetNextIntSequenceSynchronous(this DbContext context)
    {
        return context.MySequence
                .FromSqlRaw("SELECT NEXT VALUE FOR MyDbSequence AS MyValue")
                .AsEnumerable().First();
    }