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.
Top comments (2)
Since the Internet is so damn packed with infollution, it was a hell of a hardship to find this peace of gold information. Thanks a lot.
One quick note. The name of the variable in your class has to match your function name. Here is how I did it in the light of @rramname 's information:
Created a class under the Model folder:
public class GetUserRecipeCount{
public int get_recipe_count_for_user { get; set; } // Note that this variable's name has to match your function name.
}
Created a virtual DbSet in my Context file:
public virtual DbSet<GetUserRecipeCount> get_recipe_count_for_user { get; set; }So lastly I navigated my controller and used the function:
`var count = _context.get_recipe_count_for_user.FromSqlInterpolated(
$"SELECT * FROM get_recipe_count_for_user(1)").First().get_recipe_count_for_user;
Also this is my PostgreSQL db function if anyone is curious:
`create function get_recipe_count_for_user(recipe_user_id integer) returns integer
language sql
as
$$
SELECT COUNT(*) FROM recipes r WHERE r.user_id = recipe_user_id;
$$;
alter function get_recipe_count_for_user(integer) owner to halil;`
Thanks again @rramname :)
Beautiful! Thank you.