select subcategory list inside category list

Aleena Saviour

8y2.7kLINQ

this is my query for selecting categories and subcategories.

List lstcat = new List();

var categ = _db.Category

.Select(c => new Category()

{

PKCatId = c.PKCatId,

CatName = c.CatName,

Icon = c.Icon,

ActiveIcon = c.ActiveIcon,

Subcat = GetChildren(_db, c.PKCatId)

})

.ToList();

public static List GetChildren(HomeDBContext _db, int parentId)

{

return

_db.subCat.Where(c => c.PKCatId == parentId)

.Select(c => new SubCat

{

PKSubCatId = c.PKSubCatId,

SubCatName = c.SubCatName,

PKCatId = c.PKCatId

})

.ToList();

}

It gives error like "The entity or complex type 'EcomHomeFurniture.Models.Category' cannot be constructed in a LINQ to Entities query."

can anyone help me to solve this?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ruchi Sharavat8y ago
Accepted answer
Not sure for the solution but can try by using viewmodel for subcat instead of direct entity:
public class SubCatViewModel
{
public int PKSubCatId {get;set;}
public string SubCatName {get;set;}
public int PKCatId{get;set;}
}
public static List GetChildren(HomeDBContext _db, int parentId)
{
var subcatlist=
_db.subCat.Where(c => c.PKCatId == parentId)
.Select(c => new SubCatViewModel
{
PKSubCatId = c.PKSubCatId,
SubCatName = c.SubCatName,
PKCatId = c.PKCatId
})
.ToList();
return subcatlist;
}
Or can try this too:
public static List GetChildren(HomeDBContext _db, int parentId)
{
var list=
_db.subCat.Where(c => c.PKCatId == parentId).ToList();
var subcatlist=list
.Select(c => new SubCat
{
PKSubCatId = c.PKSubCatId,
SubCatName = c.SubCatName,
PKCatId = c.PKCatId
})
.ToList();
return subcatlist;
}
Karthik Chintala8y ago
The error clearly states that you cannot use an Entity inside of the select query.

I think your Category class is a mapped entity for your DbContext. You cannot perform projection onto your mapped entities in EF. However, you can project onto a DTO or an anonymous type.

All you've to do is create another class, which is not a mapped entity or just project anonymously.

This is what you've to do
public class CategoryDTO
{
    public long PKCatId {get; set;}
    public string CatName {get; set;}
    public SubCategoryDTO Subcat {get; set;}
    //... etc
}

public class SubCategoryDTO
{
    public long PKSubCatId {get; set;}
    public string SubCatName {get; set;}
    //... etc
}

public void Method1(){
    List lstcat = new List();
    var categ = _db.Category
                    .Select(c => new CategoryDTO()
                    {
                        PKCatId = c.PKCatId,
                        CatName = c.CatName,
                        // Icon = c.Icon,
                        // ActiveIcon = c.ActiveIcon,
                         Subcat = GetChildren(_db, c.PKCatId)
                    })ToList();
}
public static List GetChildren(HomeDBContext _db, int parentId)
{
    return
    _db.subCat.Where(c => c.PKCatId == parentId)
            .Select(c => new SubCategoryDTO
            {
                PKSubCatId = c.PKSubCatId,
                SubCatName = c.SubCatName
                // PKCatId = c.PKCatId
            }).ToList();
}
If you don't want to create DTO classes you could return properties anonymously.
Hope this helps.