The entity cannot be constructed in a LINQ to Enti

Question

There is an entity type called product that is generated by entity framework. I have writen this query

public IQueryable<Product> 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 :

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

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?

Answer 1

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<ProductDTO> GetProducts(int categoryID)
{
    return (from p in db.Products
            where p.CategoryID == categoryID
            select new ProductDTO { Name = p.Name }).ToList();
}

Answer 2

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<Letter>()
                .Include(letter => letter.LetterStatus)
                .Select(l => 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 <anonymous type> so we have to convert it so list of <letter>

//opecations in list , LINQ to Object; so we can use class types in select
list = tempList.Select(l => 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

Answer 3

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 => 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()".

Answer 4

only add AsEnumerable() :

public IQueryable<Product> GetProducts(int categoryID)
{
    return from p in db.Products.AsEnumerable()
           where p.CategoryID== categoryID
           select new Product { Name = p.Name};
}

Answer 5

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<object> list = dataContext.Table.Select(e => new { MyRequiredField = e.MyRequiredField}).AsEnumerable();

Answer 6

Another simple way :)

public IQueryable<Product> GetProducts(int categoryID)
{
    var productList = db.Products
        .Where(p => p.CategoryID == categoryID)
        .Select(item => 
            new Product
            {
                Name = item.Name
            })
        .ToList()
        .AsQueryable(); // actually it's not useful after "ToList()" :D

    return productList;
}