CODEWITHSUNDEEP
sql-serverentity-frameworkentity-framework-core
ocuenca
ocuenca

Reputation: 39346

Problem mapping scalar function in EF core 2.1

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

Upvotes: 4

Views: 3800

Answers (3)

Extragorey
Extragorey

Reputation: 1764

I just encountered this use case myself and came up with the following solution. Suppose you have a user-defined scalar function dbo.GetCost(@MatID int, @ExtCost money) that returns a decimal(18, 2). Then define a method in your DbContext as follows:

[DbFunction]
public decimal GetCost(int matID, decimal extCost) {
    NonEmptyTable.Take(1).Select(x => GetCost(matId, extCost))
        .SingleOrDefault();
}

Usage:

decimal cost = db.GetCost(matID, extCost);

There's a bit of magic happening here, but it makes sense when you think it through. You're defining a C# method mapping to the database function as the EF Core docs suggest, but instead of the method body throwing an exception, it's actually invoking the same method in a Linq-to-SQL context, then returning the result (you could do this with two separate methods to avoid the appearance of recursion, but this is more concise).

What's important is that NonEmptyTable is mapped to a table that is never empty, otherwise it will return a default value (you could also use .Single() to throw an exception in this case, if preferred).

Upvotes: 2

Sergei Tkachenko
Sergei Tkachenko

Reputation: 21

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;
}

Upvotes: 1

Adrian Iftode
Adrian Iftode

Reputation: 15673

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

Upvotes: 3

Related Questions