The entity or complex type cannot be constructed in a linq to entities query

Question

Problem :

I have one entity type called product which is generated by the entity framework. I have tried to write below query

public IMyQueryable<Product> GetProducts(int myCategoryID)
{
    return from prod in db.Products
    where prod.CategoryID== myCategoryID
    select new Product { Name = prod.Name};
}

My below written code throws a following error :

var myproducts = myproductRepository.GetProducts(1).Tolist();

 

“The entity or complex type Shop.Product cannot be constructed in a LINQ to Entities query”

However when I try to use select p instead of the long one select new Product { Name = p.Name}; it miraculously works correctly.

How can I perform the custom select section?

Answer 1

You simply cannot (and must not be able to) project onto the mapped entity. You will but, you will need to project onto the anonymous type or onto the DTO:

public class MyProductDTO
{
    public string MyName { get; set; }
    // Other fields which you may need from a Product entity
}

And now your method will return you the List of DTO's as shown in below code snippet.

public List<MyProductDTO> GetProducts(int myCategoryID)
{
    return (from prod in db.Products
    where prod.CategoryID == myCategoryID
    select new MyProductDTO { MyName = prod.Name }).ToList();
}

Hope above explanation will clear your doubts.