Calculation in EF Projection

Question

Is there a way I can achieve the following?

// colourInfo.Discount = 75, but can change
// allPrice type has Part, Desc, Type

var a = allPricesForPgs.Where(x => x.PG == c && x.ColourCode == colourInfo.ColourCode).Select(y=> new AllPrice {Part=y.Part, Desc=y.Desc, Price=y.Price*(colourInfo.Discount/100)}));

I get the error : The entity or complex type 'Portal.AllPrice' cannot be constructed in a LINQ to Entities query.

It seems the EF cannot handle calculations, what are my options since I am getting a dynamic value from one table to do a calculation on another?

Answer 1

Sam1's comment is correct. You cannot project into another entity. Other options you have can be to create an anonymous type like so:

var a = allPricesForPgs
        .Where(x => x.PG == c && x.ColourCode == colourInfo.ColourCode)
        .Select(y=> new 
            {
                Part=y.Part, 
                Desc=y.Desc, 
                Price=y.Price*(colourInfo.Discount/100)
             }
         ));

Or to create a class that will hold the temporary data (such as a DTO).

Since it seems like all you need to do is have this information to modify some other entity, you should be able to do it with the anonymous type.

EDIT:

You could add a '.ToList()' right before the .Select(...). You'd essentially be using LINQ TO OBJECTS instead of LINQ TO ENTITIES, so if a lot of entities might match the allPricesForPgs.Where(...) statement, you should keep away from that.

But, if you want these as AllPrice's, why are they not added to the AllPrice DB? Are you keeping a separate list of some AllPrice's from Entity Framework and some AllPrice's from this list? This could get confusing and cause errors.

A final option would be to extend the class. All entities are declared PARTIAL. You can create another class like:

partial class AllPrice
{
    Double DiscoutedPrice { get { Price * myDiscount/100; } }