Can I use a CAST inside a LINQ to Entities query?

Question

I have a LINQ to Entities query

From item In ctx.Items
Select new {
    ListPrice = item.Cost / (1M - item.Markup)
};

Can I specify to EF that I want it to apply a cast to the list price before querying and materializing it¹? Is there something like EntityFunctions.Cast maybe? Or can I use the ESQL cast function?

I want the LINQ to generate a SQL query along these lines

SELECT cast((Cost / (1 - Markup)) as decimal(10, 2)) AS ListPrice

¹My goal is to get rid of a bunch of precision/scale the query. Because there's decimal subtraction and division, the result of the math is a decimal(38, 26)! That's way more than .NET can handle and more than I need.

Answer 1

EF allows you to map CLR functions to database functions using the DbFunction attribute. Unfortunately, it looks like the built in cast and convert are not functions and it does not look like you can map to them.

Instead you can create a UDF which does the cast and map it in the DbModel. The mapping API is complicated so I would use the Code First Functions library to do it for you. (If your using Database first or Model first, you can do the mapping manually in your SSDL and CSDL¹). Also, there's no way to do dynamic casting inside a UDF so you'll need to pick write separate functions for each cast you want. Here's an example for a cast(field as decimal(10,4).

-- In SQL Server
CREATE FUNCTION ClrRound_10_4
(
    @value decimal(28, 10)
)
RETURNS decimal(10,4)
AS
BEGIN
    DECLARE @converted decimal(10,4)

    SELECT @converted = cast(round(@value, 4) as decimal(10,4))

    RETURN @converted

END
GO

//In your DbContext class
using CodeFirstStoreFunctions;

public class MyContext : DbContext {
    protected override void OnModelCreating(DbModelBuilder builder) {
        builder.Conventions.Add(new FunctionsConvention("dbo", typeof(Udf));
    }

    //etc
}

//In a static class named Udf (in the same namespace as your context)
using System.Data.Entity;

public static class Udf {
    [DbFunction("CodeFirstDatabaseSchema", "ClrRound_10_4")]
    public static decimal ClrRound_10_4(decimal value) {
        throw new InvalidOperationException("Cannot call UDF directly!");
    }
}

//In your LINQ query
from item in ctx.Items
select new {
    ListPrice = Udf.ClrRound_10_4(item.Cost / (1M - item.Markup))
};

¹ See this blog post or this MSDN article for more details.