ROHIT's Blog
Call a SQL function in EF Core 3 without Linq queries, mapping and translation

Call a SQL function in EF Core 3 without Linq queries, mapping and translation

ROHIT's photo
ROHIT
·

2 min read

I have come across couple scenarios where I need to call an already existing scalar function in SQL server that has some logic into my C# EF Core 3 application for reusability. But most the guides out there show how can this be done only by mapping it to a C# function with Throw new Notimplemented exception and using Linq queries like official documentation. I did not need to use Linq queries so I am doing it in a different way as below.

Let’s say you have a SQL function.

CREATE FUNCTION dbo.IsStrogestAvenger(@Superhero varchar(100))
RETURNS bit
AS
BEGIN
    declare @result bit=0
    if(@Superhero ='Thor')
        set @result=1
    else
        set @result=0
RETURN @result
END

Now turn to our C# code. For result, lets create a model to hold this output.

public class IsStrongestAvengerResult
{
   public bool IsStrongestAvenger { get; set; }
}

Map it in context class as below

public virtual DbSet<IsStrongestAvengerResult> IsStrongestAvenger{ get; set; }

And

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IsStrongestAvengerResult>(e => e.HasNoKey());
}

Now we use FromSqlInterpolated to call this function and map.

public bool IsStrongestAvenger(string Superhero)
  {
    return context.IsStrongestAvenger.FromSqlInterpolated($"select   dbo.IsStrogestAvenger ({Superhero}) as IsStrongestAvenger").FirstOrDefault().IsStrongestAvenger;
}

Important thing to note above is the use of AS >IsStrongestAvenger and it should match with the property name in >our model IsStrongestAvengerResult.IsStrongestAvenger

Now this function can be directly call from C# to get the output.

Thank you for reading. Comments and Suggestion are highly appreciated.

C#dotnetcore