Problem mapping scalar function in EF core 2.1

Question

I'm trying to mapp an scalar Function of my DB that has a custom schema. This is how I'm registering the function in the context:

 [DbFunction("ProjectMaterial_GetCostPrice","Project")]
 public static decimal ProjectMaterial_GetCostPrice (int ProjectMaterialID, decimal ExtCost)
 {
    return 0;
 }

I'm registering the Scalar function in a partial class of the context. And this is the Schema of the Scalar Function in the DB:

-- Select Project.ProjectDriver_GetCostPrice (5456921)

ALTER FUNCTION [Project].[ProjectMaterial_GetCostPrice] (@ProjectMaterialID int, @ExtCost money) 
    RETURNS MONEY
AS

Also I change the body of the method with a throw as the documentation suggest:

throw new NotSupportedException();

And it's thrown the exception instead of calling the function

This is how I call the function:

 var newCostPrice= NsiteDBContext.ProjectMaterial_GetCostPrice(projectMaterial.ProjectMaterialId, projectMaterial.CostPrice.Value);

Answer 1

Using the call itself it throws the exception because it actually executes the C# code. The reason it is recommended to throw an exception is exactly this, to avoid inadvertent use, ie by directly calling it. That signature will be interpreted by the given LINQ provider and translate into the proper SQL statements.

To do so EF context needs to know how to use so some way might be

var items = await ctx.Materials.Select(c = > new {
   Material= c,
   CostPrice = ProjectMaterial_GetCostPrice(c.ProjectMaterialId, c.CostPrice.Value),
}).ToListAsync();

Now the ctx object will know how to translate the ProjectMaterial_GetCostPrice signature when it parses the expression trees.

Doing outside a select statement, even via the static call won't work as expected, and it throws that exception (to inform us this).