Is it possible to use 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 tell EF that I want it to apply cast to the price of the list before requesting and materializing it ¹ ? Maybe something like EntityFunctions.Cast ? Or can I use the ESQL cast function?

I want LINQ to create an SQL query on these lines

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

¹ My goal is to get rid of the heap of accuracy / scale of the request. Since there is decimal subtraction and division, the result of mathematics is decimal (38, 26)! Thus, more than .NET can handle more than I need.

Answer 1

EF allows you to map CLR functions to database functions using the DbFunction attribute. Unfortunately, it seems that the built-in cast and convert are not functions, and it looks like you cannot match them.

Instead, you can create a UDF that does the throw and maps it to the DbModel . The mapping API is complex, so I would use the Code First Functions library to do this for you. (If you are using Database or Model first, you can manually perform the mapping in SSDL and CSDL ¹ ). In addition, there is no way to do dynamic casting inside UDF, so you will need to select separate functions for each selected cast. Here is an example for 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 MSDN for more details.