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?

see this http://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query — Zaki, Oct 18 '12 at 15:56

DanTheMan · Accepted Answer · 2012-10-18T16:31:37.507

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; } }

Only problem with anon type is that I am building a cumulative list of the AllPrice type, I sometimes add clean AllPrice types into it, then sometimes I add ones on which I amended the price, since now they become anon I cannot merge the two together in a List... which creates pain. — sprocket12, Oct 18 '12 at 16:18
I've edited my answer, but this sounds like a bad idea from the surface. A list of the AllPrice entity should be only a reflection of what's in the database. If you're creating other, temporary lists of data, you should convert both the AllPrice's and other types into a third class type. — DanTheMan, Oct 18 '12 at 16:29
Hmmm, I reread your question and all you want is a slightly modified AllPrice. I've re-edited my answer in hopes to help! — DanTheMan, Oct 18 '12 at 16:32

Calculation in EF Projection

1 Answers1