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

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.

Comments

[Susamate]

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;

            userVm.UserRecipeCount = count;`

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 :)

[MNemteanu]

Beautiful! Thank you.