Add list of items using linq

c# entity-framework linq

Question

I have a class that holds Categories.

public class CategoryDto
{
    public int Id { get; set; }
    public int PortfolioId { get; set; }
    public string Description { get; set; }
    public List<SubCategoryDto> SubCategories { get; set; }

    public CategoryDto()
    {
        SubCategories = new List<SubCategoryDto>();
    }
}

It has a List in it, which is a list of SubCategory classes:

public class SubCategoryDto
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    public CategoryDto Category { get; set; }
    public string Description { get; set; }
}

I then populate this item, but I am getting a list bases on a 'PortfolioId'.

   var cats = (from p in Context.transaction_category
                    where p.account_portfolio_id == portfolioId
                          && p.deleted == null
                    select new CategoryDto
                        {
                            Id = p.id,
                            Description = p.description,
                            PortfolioId = p.account_portfolio_id
                        }).ToList();

The Categories table has a foreign key to SubCategories. Each category has 0:n sub categories. So, the entity framework model has a Context.transaction_category.transaction_sub_categories collection.

So now what I do is, foreach through the categories in the list above, and populate the sub categories.

Is there a way to do this in the same link statement? The Categories object has a List list. Can it be done in the above Linq statement?

Edit: This is the fix attempt, as recommended, but is presenting an error:

var cats = (from p in Context.transaction_category
                        where p.account_portfolio_id == portfolioId
                              && p.deleted == null
                        select new CategoryDto
                            {
                                Id = p.id,
                                Description = p.description,
                                PortfolioId = p.account_portfolio_id,
                                SubCategories = (from s in Context.transaction_sub_category where s.transaction_category_id == p.id
                                                 && s.deleted == null
                                                     select new SubCategoryDto
                                                         {
                                                             Id = s.id,
                                                             Description = s.description,
                                                             CategoryId = s.transaction_category_id
                                                         }).ToList()
                            }).ToList();

LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[Objects.SubCategoryDto] ToList[SubCategoryDto](System.Collections.Generic.IEnumerable1[Objects.SubCateg‌​oryDto])' method, and this method cannot be translated into a store expression.

1
0
1/6/2014 2:04:18 AM

Accepted Answer

You can do it like this:

var cats = (from p in Context.transaction_category
                where p.account_portfolio_id == portfolioId
                      && p.deleted == null
                select new CategoryDto
                    {
                        Id = p.id,
                        Description = p.description,
                        PortfolioId = p.account_portfolio_id,
                        SubCategories = (from s in Context.transaction_category.transaction_sub_categories
                           where s.CategoryId == p.Id
                              select new SubCategoryDto {
                                    Id = s.Id,
                                    Description = s.Decription    
                        }).ToList()
                    }).ToList();

Update: To make it easier change your SubCategories and Category properties like this:

public virtual List<SubCategoryDto> SubCategories { get; set; }

public virtual CategoryDto Category { get; set; }

Then you can use include and simply load your sub categories like this:

var cats = Context.transaction_category
              .Where(p => p.account_portfolio_id == portfolioId && p.deleted == null)
              .Include(p => p.SubCategories);
1
1/6/2014 2:09:13 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow