System.NotSupportedException : The entity or complex type cannot be constructed in a LINQ to Entities query

7 months ago / mcchu28


Possibly save 1 hour of your time: This issue happens when you are projecting the entity framework query directly into an entity. The work around is to put the query into a new anonymous type, and then select the result into the entity class.


using (var context = new YourDatabaseContext())
            {
                var subquery = (from a in context.YourEntityName
                    group a by new { a.Year, a.Mode }
                    into g
                    select new 
                    {
                        Year = g.Key.Year,
                        Mode = g.Key.Mode,
                        Value = g.Sum(x => x.Value),
                        ValueUsd = g.Sum(x => x.ValueUsd)
                    }).ToList();


                var join = subquery..ToList();

                var returnList = join.Select(x => new YourEntityName
                {
                    Year = x.Year,
                    Mode = x.Mode,
                    Value = x.Value,
                    ValueUsd = x.ValueUsd
                }).ToList();
                
                return returnList;
            }

The alternative solution is that you don't use your entity for the projection. Instead you create a new class which will be identical to your entity and project directly into that class.


using (var context = new YourDatabaseContext())
            {
                var subquery = (from a in context.YourEntityName
                    group a by new { a.Year, a.Mode }
                    into g
                    select new YourClassName
                    {
                        Year = g.Key.Year,
                        Mode = g.Key.Mode,
                        Value = g.Sum(x => x.Value),
                        ValueUsd = g.Sum(x => x.ValueUsd)
                    }).ToList();
               
                return subquery;
            }