The Entity or Complex Type ' ' 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),
});

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.

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.

The entity or complex type cannot be constructed in a LINQ to Entities query - using lambda expression

You cannot project into model type directly. Try using annonymous type or DTO, that is data transfer object.

Annonymous type

List<Unit_Of_Measurement> list = db.Unit_Of_Measurement.Where(x=>x.name.Contains(SearchText) && x.is_deleted == 0).Select(x => new { name = x.name}).ToList().Select(x => new Unit_Of_Measurement { name = x.name}).ToList();

Using DTO

Create new class for DTO.

public class UnitOfMeasurementDTO
{
    public string Name { get; set; }
}

var list = db.Unit_Of_Measurement.Where(x=>x.name.Contains(SearchText) && x.is_deleted == 0).Select(x => new UnitOfMeasurementDTO { Name = x.name}).ToList();

Then you need to update your "PartialView" to support new DTO rather than Unit_Of_Measurement model.

Update

This is an Entity Framework (EF) restriction. LINQ to entities will convert your LINQ query in to command tree query which will execute against EF in order to return objects.

• Construct an ObjectQuery instance from ObjectContext.
• Compose a LINQ to Entities query in C# or Visual Basic by using the
  ObjectQuery instance.
• Convert LINQ standard query operators and expressions to command
  trees.
• Execute the query, in command tree representation, against the data
  source. Any exceptions thrown on the data source during execution are
  passed directly up to the client.
• Return query results back to the client.

There are some restrictions when building command trees which can be execute against EF, so that project into model type directly does not support EF. Which means nothing wrong in your LINQ query, but cannot execute against EF. Anonymous types are not tracked in EF and completely separate from your model, so that it will work without any issues. These restrictions may have introduced later versions of EF, you might have used old sample code.

---

Related Topics