In a LINQ to Entities query, the entity cannot be created.

Problem

Entity framework generates an entity type called Product. This question was written by me.

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

The following code generates the following error:

var products = productRepository.GetProducts(1).Tolist();

However, everything works correctly when I use select p instead of select new Product Name = p.Name.

What is the best way to create a custom choose section?

Asked by Ghooti Farangi

Solution #1

You can’t project onto a mapped entity (and shouldn’t be able to). You can, however, project onto a DTO or an anonymous type:

public class ProductDTO
{
    public string Name { get; set; }
    // Other field you may need from the Product entity
}

And the result of your procedure will be a List of DTOs.

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

Answered by Yakimych

Solution #2

You can project into an anonymous type and then into a model type from there.

public IEnumerable<Product> GetProducts(int categoryID)
{
    return (from p in Context.Set<Product>()
            where p.CategoryID == categoryID
            select new { Name = p.Name }).ToList()
           .Select(x => new Product { Name = x.Name });
}

Edit: I am going to be a bit more specific since this question got a lot of attention.

There is no way around this because you can’t directly project into model type (EF constraint). The only way to proceed is to project into an anonymous type (1st iteration) and then to model type (2nd iteration).

Please keep in mind that entities that are partially loaded in this manner cannot be modified, thus they should be left alone.

I’ve never quite grasped why this isn’t conceivable, and the responses in this thread don’t provide compelling evidence (mostly speaking about partially loaded data). True, an entity in a partially loaded state cannot be modified, however this entity would then be disconnected, making unintentional efforts to save them impossible.

Consider the strategy I used earlier: as a result, we still have a partially loaded model entity. This entity is not attached to anything.

Consider the following (wish-to-exist) code:

return (from p in Context.Set<Product>()
        where p.CategoryID == categoryID
        select new Product { Name = p.Name }).AsNoTracking().ToList();

We wouldn’t have to do two iterations if this resulted in a list of detached entities. It would be clever for a compiler to notice that AsNoTracking() has been called, which will result in detached entities, allowing us to perform this. If AsNoTracking() were to be omitted, the same exception would be thrown as it is now, warning us that we need to be more precise about the result we desire.

Answered by Goran

Solution #3

Another method that I discovered works is to create a class that derives from your Product class and use it. Consider the following example:

public class PseudoProduct : Product { }

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

I’m not sure if this is “authorized,” but it does the job.

Answered by Tomasz Iniewicz

Solution #4

Here’s one method to do that without having to declare a new 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 following is how the above feature (basic product to product mapping) is implemented:

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;
}

Answered by Bojan Hrnkas

Solution #5

Another simple method:

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;
}

Answered by Soren

Post is based on https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query