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.

the only functions I know are from [SqlFunctions](https://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions%28v=vs.110%29.aspx). Your cast is not present in these functions. — tschmit007, Dec 03 '15 at 10:26
Did you try EntityFunctions ? For example `EntityFunctions.Truncate(item.Cost / (1M - item.Markup), 2)` : see https://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions(v=vs.110).aspx — Raphaël Althaus, Dec 03 '15 at 10:27
@RaphaëlAlthaus `Truncate` does not change the scale / precision of the returned value. (It actually translates it to a SQL Server Round). — just.another.programmer, Dec 03 '15 at 10:29
You're right. The other ways would be to work with computed columns (but.. seems that you're working with esql, not sql server), or make a view and try to query from the view. — Raphaël Althaus, Dec 03 '15 at 10:30
@RaphaëlAlthaus I'm working w/ SQL Server. I only suggested ESQL b/c it has the `cast` operator I need. I know I could use a computed column or a view. I'd like to do it in code if possible. — just.another.programmer, Dec 03 '15 at 10:31
Well, there's also a cast operator in sql server, that's why I got confused. Anyway, the computed column will probably be the only / better solution in this case. — Raphaël Althaus, Dec 03 '15 at 10:35

score 2 · Accepted Answer · answered Dec 03 '15 at 17:42

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.

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

1 Answers1

Linked