"The entity or complex type cannot be constructed in a LINQ to Entities query" in Controler

Question

i have this code in my controller

IQueryable<SellingItem> sellingitems = db.SellingItems
            .GroupBy(s => new { s.ItemId ,s.Condition,s.ExpInDays})
            .Select(si => new SellingItem
            {
                Item = si.First().Item,
                Condition = si.First().Condition,
                ExpInDays = si.First().ExpInDays,
                Qty = si.Sum(i => i.Qty),
            });

and when i try to run it i get an error

The entity or complex type cannot be constructed in a LINQ to Entities query

now it look to me that my linq query is too complex for entity framework to handle,

so i have 2 workarounds, but i don't like both of them.

1. load the whole table in memory and make the query like this

2. use a SQL statement, which will be faster but will not follow the entity framework rules

is there a better way?

-----UPDATE---------------------------------

as it turn out (thanks a lot guys)

i was wrong by stating

now it look to me that my linq query is too complex for entity framework to handle,

and it didn't work because of the fact that i used the same class for the result. i created a new class and now it works amazing!!

Answer 1

You don't need to resort to any of your workarounds to fix that exception per se. The problem is that SellingItem is a class that is part of your Entity Framework model. The reason for this is explained in the comments on this answer.

Either select an anonymous object like so:

IQueryable<SellingItem> sellingitems = db.SellingItems
.GroupBy(s => new { s.ItemId ,s.Condition,s.ExpInDays})
.Select(si => new
{
    Item = si.First().Item,
    Condition = si.First().Condition,
    ExpInDays = si.First().ExpInDays,
    Qty = si.Sum(i => i.Qty),
});

Or create an object specifically for the select that you are trying to do:

public class NewClass
{
    public ItemClass Item { get;set; }
    public ConditionClass Condition { get;set; }
    public in ExpInDays { get;set; }
    public int Qty { get;set; }
}

Naturally you will need to make sure that the types in this specific class match up to their respective types.

You can then use the new class to do the select:

 // Use new class
 IQueryable<SellingItem> sellingitems = db.SellingItems
.GroupBy(s => new { s.ItemId ,s.Condition,s.ExpInDays})
.Select(si => new NewClass
{
    Item = si.First().Item,
    Condition = si.First().Condition,
    ExpInDays = si.First().ExpInDays,
    Qty = si.Sum(i => i.Qty),
});