C # only show numbers after decimal point with linq

Question

I have a linq query that succeeds, one of the returned columns is a decimal type that is used to represent prices in pounds and pensions (there will never be any negative values)

I want to remove pounds and pence into separate properties of my projection, however when using features such as

var result= from j in context.Products select new{ Price = t.Price, PricePounds = Math.Truncate(t.Price) }; 

I get an error that Math.truncate is not supported because it cannot be translated into a storage expression. How can I get the pound value from this query?

Answer 1

If after that you no longer need to do anything else in the database, the easiest approach is to simply do the client side truncation:

 var query = context.Products .AsEnumerable() // Everything from here is LINQ to Objects .Select(p => new { p.Price, PricePounds = Math.Truncate(p.Price) }); 

Note that you can also just add to int - and this may already be included in EF.

EDIT: As noted in the comments, you can do a projection first, like

 var query = context.Products .Select(p => new { p.Price, p.SomethingElse }) .AsEnumerable() // Everything from here is LINQ to Objects .Select(p => new { p.Price, PricePounds = Math.Truncate(p.Price), p.SomethingElse }); 

(Where SomethingElse is another property that interests you, as an example - I doubt that you only want a price.)

This will avoid getting the whole object when you need only a few properties.

Answer 2

You can try:

 var result= from j in context.Products select new { Price = t.Price, PricePounds = EntityFunctions.Truncate(t.Price, 0) }; 

The case of Math.Truncate cannot be translated into SQL, where EntityFunctions.Truncate should be.