Add list of items using 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.

where is your sub categories ? and could you share your subcategory class? — Selman Genç
– Selman Genç, Commented Jan 6, 2014 at 1:14

Selman Genç · Accepted Answer · 2014-01-06 02:09:13Z

1

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

edited Jan 6, 2014 at 2:09

answered Jan 6, 2014 at 1:18

Selman Genç

102k15 gold badges126 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Craig Over a year ago

Thanks. That should work. Just out of interest... would that cause SQL to do some sort of sub query (slowish), or would it use an INNER JOIN to get the data of the related sub queries?

Leo Over a year ago

No it doesn't. It will make a round-trip to the database everytime you construct/select a CategoryDto. So if you have 100 categories you will be hitting the database 101 times

Selman Genç Over a year ago

and I guess it would be better if you change your properties to Navigation properties.Then you can use include instead of long linq queries like this.take a look at here: msdn.microsoft.com/en-us/data/jj713564.aspx

Craig Over a year ago

I get an error with this now: LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[Objects.SubCategoryDto] ToList[SubCategoryDto](System.Collections.Generic.IEnumerable1[Objects.SubCategoryDto])' method, and this method cannot be translated into a store expression.

Selman Genç Over a year ago

it's my mistake there is one extra semicolon,try again?

|

Collectives™ on Stack Overflow

Add list of items using linq

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related