c# only show numbers after decimal point with linq

Question

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

I want to be able to strip out the pounds and pence into separate Properties of my projection, however when using functionality 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 as it cannot be translated into a store expression. How can I get the pounds value from this query?

Answer 1

If you don't need to do anything else in the database after that, the simplest approach is just to perform the truncation client-side:

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 might also want to just cast to int - and that might be supported in EF already.

EDIT: As noted in comments, you may want to perform a projection first, e.g.

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 you're interested in, as an example - I doubt that you only want the price.)

This will avoid the entire entity being fetched when you only want a few properties.

Answer 2

You may try:

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

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