Problem mapping scalar function in EF core 2.1

entity-framework entity-framework-core sql-server

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);
1
2
2/12/2019 8:07:07 PM

Accepted Answer

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

2
2/12/2019 8:18:29 PM

Popular Answer

As described in the previous answer, you can use functions, defined in that way, in the LINQ queries only. To call SQL scalar-valued functions directly, you should define it using DbContext.Database.ExecuteSqlCommand method. Like this, it should work:

public decimal ProjectMaterial_GetCostPrice(int ProjectMaterialID, decimal ExtCost)
{
    System.Data.SqlClient.SqlParameter resultParam =
        new System.Data.SqlClient.SqlParameter
    {
        ParameterName = "@resultCost",
        SqlDbType = System.Data.SqlDbType.Money,
        Direction = System.Data.ParameterDirection.Output
    };
    System.Data.SqlClient.SqlParameter parMaterialID =
        new System.Data.SqlClient.SqlParameter("@MaterialID", ProjectMaterialID);
    SqlParameter parExtCost =
        new System.Data.SqlClient.SqlParameter("@ExtCost", ExtCost);
    Database.ExecuteSqlCommand(
        "select @resultCost = [Project].[ProjectMaterial_GetCostPrice](@MaterialID, @ExtCost);",
        resultParam, parMaterialID, parExtCost);
    return (decimal)resultParam.Value;
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow