The Entity Cannot Be Constructed in a Linq to Entities Query

The entity cannot be constructed in a LINQ to Entities query

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

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

If this query works fine:

var queryToList = (from ac in ctx.AuthorisationChecks
                   where wedNumbers.Contains(ac.WedNo)
                   orderby ac.WedNo, ac.ExpAuthDate, ac.ActAuthDate
                   select new AuthorisationCheck
                   {
                       Blah = ac.Blah
                   }).ToList();

then this should also work:

model.AuthorisationChecks = (from ac in ctx.AuthorisationChecks
                   where wedNumbers.Contains(ac.WedNo)
                   orderby ac.WedNo, ac.ExpAuthDate, ac.ActAuthDate
                   select new AuthorisationCheck
                   {
                       Blah = ac.Blah
                   }).ToList();

and in your first case you don't need to project again, you can directly assign it to model propeerty:

model.AuthorisationChecks = queryToList;

UPDATE:

As it is Linq To Entities,you have to do something like this using anonymous type:

var queryToList = (from ac in ctx.AuthorisationChecks
                       where wedNumbers.Contains(ac.WedNo)
                       orderby ac.WedNo, ac.ExpAuthDate, ac.ActAuthDate
                       select new 
                       {
                           Blah = ac.Blah
                       }).ToList();

and then:

model.AuthorisationChecks = queryToList.Select(x => new AuthorisationCheck
    {
        Blah = x.Blah
    }).ToList();

The entity or complex type cannot be constructed in a LINQ to Entities query in Controler

You don't need to resort to any of your workarounds to fix that exception per se. The problem is that SellingItem is a class that is part of your Entity Framework model. The reason for this is explained in the comments on this answer.

Either select an anonymous object like so:

IQueryable<SellingItem> sellingitems = db.SellingItems
.GroupBy(s => new { s.ItemId ,s.Condition,s.ExpInDays})
.Select(si => new
{
    Item = si.First().Item,
    Condition = si.First().Condition,
    ExpInDays = si.First().ExpInDays,
    Qty = si.Sum(i => i.Qty),
});

Or create an object specifically for the select that you are trying to do:

public class NewClass
{
    public ItemClass Item { get;set; }
    public ConditionClass Condition { get;set; }
    public in ExpInDays { get;set; }
    public int Qty { get;set; }
}

Naturally you will need to make sure that the types in this specific class match up to their respective types.

You can then use the new class to do the select:

 // Use new class
 IQueryable<SellingItem> sellingitems = db.SellingItems
.GroupBy(s => new { s.ItemId ,s.Condition,s.ExpInDays})
.Select(si => new NewClass
{
    Item = si.First().Item,
    Condition = si.First().Condition,
    ExpInDays = si.First().ExpInDays,
    Qty = si.Sum(i => i.Qty),
});

Cannot be constructed in a LINQ to Entities query

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.

Linq query throws entity or complex type cannot be constructed in a linq to entity, even when i remove the class name just using select new { ..}

Your problem is that, as the error message says, L2E doesn't know about your entity. The way it works is that nothing happens until you enumerate the query. In your case, calling ToList() will do that. At that point, the database will try and execute the query, find it doesn't know about your entity, and give an exception.

What you need to do is construct your query in such a way that the bit that is executed on the database (ie before you enumerate) doesn't contain any reference to your entities, then after you've enumerated (by which time the results will be in memory, and detached from the database) you can create the entity.

Try the following...

var queryAllUsers = (from ru in db.rpm_usr
                     join ei in db.emp_info on ru.wwid equals ei.wwid
                     let cdis_eml = ei.dmn_addr + ";"
                     where ru.inact_ind == "N" && ei.inact_ind == "N" && ei.dmn_addr != null
                     orderby ei.dmn_addr
                     select ru)
                     .ToList()
                     .Select(ru => new rpm_scrty_rpm_usr {
                       usr_id = ru.usr_id,
                       usr_lnm = ru.usr_lnm,
                       usr_pwd = ru.usr_pwd,
                       usr_fnm = ru.usr_fnm,
                       wwid = ru.wwid,
                       apprvr_wwid = ru.apprvr_wwid,
                       chg_dtm = ru.chg_dtm,
                       chg_usr_id = ru.chg_usr_id,
                       dflt_ste_id = ru.dflt_ste_id,
                       cre_dtm = ru.cre_dtm,
                       cre_usr_id = ru.cre_usr_id,
                       lst_pwd_chg_dtm = ru.lst_pwd_chg_dtm,
                       lst_accs_dtm = ru.lst_accs_dtm,
                       email_id = ru.email_id,
                       inact_ind = ru.inact_ind,
                       salt = ru.salt,
                       tel = ru.tel
                     };

As you can see, there isn't any mention of your entities inside the brackets, so when ToList() is called, the database won't have a problem. You then create the entity in memory.

The above code is based on the fact that it looks to me as if you are just selecting ru as the result of your query. If you needed data from related entities, then you would need to do two selects, one as you had before (but without reference to the entity), and one after calling ToList() to create the entity.

By the way, the line let cdis_eml = ei.dmn_addr + ";" seems to be not needed, so you could remove it.

Hope that helps. As I'm not familiar with your database structure, it's hard to know if this is exactly what you want, so if you have any more questions, feel free to reply.

The entity or complex type X cannot be constructed in a LINQ to Entities query

You can simply select just necessary field instead of creating the DTO, annonymous type and Worker type:

var code= FactoryDB.Worker
    .OrderBy(x => x.Code)
    .Select(x=> x.Code).FirstOrDefault();

It requires the less code and should construct the optimized sql

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

In Linq-to-Entities you can only project to an anonymous type or a regular class. You can't project to an existing entity type. You can with linq-to-objects like so

var tasks = (from i in data.Incidents
            join a in data.Accounts on i.CustomerID equals a.Acct_CID
             select new
             {
               creator_id = a.ID,
               start_date = i.DateOpened,
               end_date = i.DateCLosed
              // ...
             }).AsEnumerable().Select(x => new Tasks {
               creator_id = x.creator_id,
               start_date = x.start_date,
               end_date = x.end_date              
             }).ToList();

---

Related Topics