The entity cannot be constructed in a LINQ to Entities query

Question

There is an entity type called Product that is generated by entity framework  I have written this query public IQueryable lt Product gt  GetProducts int categoryID        return from p in db Products            where p CategoryID   categoryID            select new Product   Name   p Name      The code below throws the following error     quot The entity or complex type Shop Product cannot be constructed in a LINQ to Entities query quot   var products   productRepository GetProducts 1  Tolist     But when I use select p instead of select new Product   Name   p Name   it works correctly  How can I preform a custom select section

User · Answer

Here is one way to do this without declaring aditional class:

public List<Product> GetProducts(int categoryID)
{
    var query = from p in db.Products
            where p.CategoryID == categoryID
            select new { Name = p.Name };
    var products = query.ToList().Select(r => new Product
    {
        Name = r.Name;
    }).ToList();

    return products;
}

However, this is only to be used if you want to combine multiple entities in a single entity. The above functionality (simple product to product mapping) is done like this:

public List<Product> GetProducts(int categoryID)
{
    var query = from p in db.Products
            where p.CategoryID == categoryID
            select p;
    var products = query.ToList();

    return products;
}

User · Answer

In many cases  the transformation is not needed  Think for the reason you want the strongly type List  and evaluate if you just want the data  for example  in a web service or for displaying it  It does not matter the type   You just need to know how to read it and check that is identical to the properties defined in the anonymous type that you defined  That is the optimun scenario  cause something you don t need all the fields of an entity  and that s the reason anonymous type exists    A simple way is doing this   IEnumerable lt object gt  list   dataContext Table Select e   gt  new   MyRequiredField   e MyRequiredField   AsEnumerable

User · Answer

You can solve this by using Data Transfer Objects  DTO s    These are a bit like viewmodels where you put in the properties you need and you can map them manually in your controller or by using third-party solutions like AutoMapper   With DTO s you can     Make data serialisable  Json  Get rid of circular references Reduce networktraffic by leaving properties you don t need  viewmodelwise  Use objectflattening   I ve been learning this in school this year and it s a very useful tool

User · Answer

if you are Executing Linq to Entity you can t use the ClassType with new in the select closure of query only anonymous types are allowed  new without type   take look at this snippet of my project         var dbQuery   context Set lt Letter gt                     Include letter   gt  letter LetterStatus                   Select l   gt  new  Title  l Title ID   l ID  LastModificationDate   l LastModificationDate  DateCreated   l DateCreated LetterStatus   new  ID   l LetterStatusID Value NameInArabic   l LetterStatus NameInArabic NameInEnglish   l LetterStatus NameInEnglish                                       without type                                                                                                             without type   of you added the new keyword in Select closure even on the complex properties you will got this error      so remove the ClassTypes from new keyword on Linq to Entity queries      because it will transformed to sql statement and executed on SqlServer   so when can I use new with types on select closure   you can use it if you you are dealing with  LINQ to Object  in memory collection     opecations in tempList   LINQ to Entities  so we can not use class types in select only anonymous types are allowed var tempList   dbQuery Skip 10  Take 10  ToList      this is list of  lt anonymous type gt  so we have to convert it so list of  lt letter gt     opecations in list   LINQ to Object  so we can use class types in select list   tempList Select l   gt  new Letter  Title   l Title  ID   l ID  LastModificationDate   l LastModificationDate  DateCreated   l DateCreated  LetterStatus   new LetterStatus  ID   l LetterStatus ID  NameInArabic   l LetterStatus NameInArabic  NameInEnglish   l LetterStatus NameInEnglish      ToList                                           with type    after I executed ToList on query it became in memory collection  so we can use new ClassTypes in select

User · Answer

You can use this and it should be working --  You must use toList before making the new list using select   db Products      where x  gt x CategoryID    categoryID  ToList        select x  gt new Product   Name   p Name   ToList

User · Answer

you can add AsEnumerable to your collection like the follow    public IQueryable lt Product gt  GetProducts int categoryID        return from p in db Products AsEnumerable              where p CategoryID   categoryID            select new Product   Name   p Name

User · Answer

In response to the other question which was marked as duplicate  see here  I figured out a quick and easy solution based on the answer of Soren   data Tasks AddRange      data Task AsEnumerable   Select t   gt  new Task          creator id     t ID          start date     t Incident DateOpened          end date       t Incident DateCLosed          product code   t Incident ProductCode            so on              data SaveChanges      Note   This solution only works if you have a navigation property  foreign key  on the Task class  here called  Incident    If you don t have that  you can just use one of the other posted solutions with   AsQueryable

User · Answer

It won t let you map back onto Product since that is your table you are querying  You need an anonymous function  then you can add it to a ViewModel  and add each ViewModel to a List lt MyViewModel gt  and return these  It s a slight digression  but I include caveats about handling nullable dates because these are a pain in the behind to deal with  just in case you have any  This is how I handled it   Hopefully you have a ProductViewModel   public class ProductViewModel        Key      public string ID   get  set        public string Name   get  set        I have dependency injection repository framework where I call a function to grab my data  Using your post as an example  in your Controller function call  it would look like this   int categoryID   1  var prods   repository GetProducts categoryID     In the repository class   public IEnumerable lt ProductViewModel gt  GetProducts int categoryID       List lt ProductViewModel gt  lstPVM   new List lt ProductViewModel gt         var anonymousObjResult   from p in db Products                             where p CategoryID    categoryID                              select new                                                               CatID   p CategoryID                                  Name   p Name                                            NOTE  If you have any dates that are nullable and null  you ll need to            take care of that   ClosedDate    DateTime  p ClosedDate    DateTime Now             If you want a particular date  you have to define a DateTime variable             assign your value to it  then replace DateTime Now with that variable  You            cannot call a DateTime Parse there  unfortunately              Using                new Date  1   1   1800                works  though   I add a particular date so I can edit it out later               I do this foreach below so I can return a List lt ProductViewModel gt               You could do  return anonymousObjResult ToList    here            but it s not as clean and is an anonymous type instead of defined            by a ViewModel where you can control the individual field types          foreach  var a in anonymousObjResult                                        ProductViewModel pvm   new ProductViewModel                pvm ID   a CatID                pvm Name   a Name              lstPVM Add rvm                         Obviously you will just have ONE item there  but I built it             like this so you could bring back the whole table  if you wanted            to remove your Where clause  above           return lstPVM          Back in the controller  you do    List lt ProductViewModel gt  lstProd   new List lt ProductViewModel gt       if  prods    null             For setting the dates back to nulls  I m looking for this value         DateTime stdDate   DateTime Parse  01 01 1800         foreach  var a in prods                ProductViewModel o prod   new ReportViewModel            o prod ID   a ID          o prod Name   a Name            o prod ClosedDate   a ClosedDate    stdDate   null   a ClosedDate          lstProd Add o prod           return View lstProd       use this in your View as     model IEnumerable lt ProductViewModel gt

User · Answer

If you are using Entity framework  then try removing property from DbContext which uses your complex model as Entity I had same problem when mapping multiple model into a viewmodel named Entity   public DbSet lt Entity gt  Entities   get  set      Removing the entry from DbContext fixed my error

User · Answer

There is another way that I found works  you have to build a class that derives from your Product class and use that  For instance   public class PseudoProduct   Product      public IQueryable lt Product gt  GetProducts int categoryID        return from p in db Products            where p CategoryID   categoryID            select new PseudoProduct     Name   p Name       Not sure if this is  allowed   but it works

User · Answer

Another simple way     public IQueryable lt Product gt  GetProducts int categoryID        var productList   db Products          Where p   gt  p CategoryID    categoryID           Select item   gt               new Product                               Name   item Name                         ToList            AsQueryable       actually it s not useful after  ToList     D      return productList

User · Answer

You cannot  and should not be able to  project onto a mapped entity  You can  however  project onto an anonymous type or onto a DTO   public class ProductDTO       public string Name   get  set           Other field you may need from the Product entity     And your method will return a List of DTO s   public List lt ProductDTO gt  GetProducts int categoryID        return  from p in db Products             where p CategoryID    categoryID             select new ProductDTO   Name   p Name    ToList

User · Answer

only add AsEnumerable      public IQueryable lt Product gt  GetProducts int categoryID        return from p in db Products AsEnumerable              where p CategoryID   categoryID            select new Product   Name   p Name

User · Answer

You can project into anonymous type  and then from it to model type  public IEnumerable lt Product gt  GetProducts int categoryID        return  from p in Context Set lt Product gt                where p CategoryID    categoryID             select new   Name   p Name    ToList               Select x   gt  new Product   Name   x Name         Edit  I am going to be a bit more specific since this question got a lot of attention   You cannot project into model type directly  EF restriction   so there is no way around this  The only way is to project into anonymous type  1st iteration   and then to model type  2nd iteration    Please also be aware that when you partially load entities in this manner  they cannot be updated  so they should remain detached  as they are   I never did completely understand why this is not possible  and the answers on this thread do not give strong reasons against it  mostly speaking about partially loaded data   It is correct that in partially loaded state entity cannot be updated  but then  this entity would be detached  so accidental attempts to save them would not be possible   Consider method I used above  we still have a partially loaded model entity as a result  This entity is detached   Consider this  wish-to-exist  possible code   return  from p in Context Set lt Product gt            where p CategoryID    categoryID         select new Product   Name   p Name    AsNoTracking   ToList      This could also result in a list of detached entities  so we would not need to make two iterations  A compiler would be smart to see that AsNoTracking   has been used  which will result in detached entities  so it could allow us to do this  If  however  AsNoTracking   was omitted  it could throw the same exception as it is throwing now  to warn us that we need to be specific enough about the result we want

[c#] The entity cannot be constructed in a LINQ to Entities query

Examples related to c#

Examples related to entity-framework