Cannot be constructed in a LINQ to Entities query

Question

What is issue in below code showing error

public IList<specialisation_sub> GetAllActiveSpecialisation_Sub(
    int specialisationid)
{
    var queryList = _db.specialisation_sub
                        .Where(obj => obj.isdeleted == false &&
                            obj.specialisationid == specialisationid)
                        .Select(obj => new specialisation_sub()
                        {
                            Id = obj.Id,
                            name = obj.name
                        }).ToList();

    return queryList.ToList<specialisation_sub>();
}

I have required to select only two columns as above

Showing below error

The entity or complex type 'JobPortalModel.specialisation_sub' cannot be constructed in a LINQ to Entities query.

Do you have some code in specialisation_sub constructor or in properties? — Sergey Berezovskiy, Nov 04 '13 at 15:22

score 6 · Accepted Answer · answered Nov 04 '13 at 15:15

You cannot project results into an arbitrary CLR type. You can, however, project into an anonymous type:

var queryList = _db.specialisation_sub
                   .Where(obj => obj.isdeleted == false && obj.specialisationid == specialisationid)
                   .Select(obj => new { Id = obj.Id, name = obj.name })
                   .ToList();

If necessary, you can the convert the results to another type:

return queryList.Select(o => new specialisation_sub { Id = o.Id, name = o.name })
                .ToList();

If you do such a conversion, feel free to replace the first ToList() call with AsEnumerable() to avoid allocating an unnecessary intermediate list.

The key difference between my answer and @Tilak's is that mine will only retrieve the desired two columns from the database, whereas @Tilak's answer will retrieve all columns, despite only using two of them in the final result. Depending on the size of each entity, this may be a significant amount of memory and/or IO overhead.

It's a limitation of the Entity Framework. I would assume it's because additional server-side operations may be performed on the projection, and the EF team wanted to keep things simple by making sure all result types map directly to EF entities or simple structural types. Since some operations are performed on the server, and not in the CLR, they can't necessarily mimic the client-side behavior of arbitrary types, which may have self-mutating logic in the constructors and/or property setters. — Mike Strobel, Nov 04 '13 at 15:26

Cannot be constructed in a LINQ to Entities query

1 Answers1