Trying Extract List of Categories with Corresponding Tickets using Linq

user_10054965 0
Viewed 56

I am trying to extract list of Categories with the corresponding Tickets for a specific userId using Linq Lambda expression.

Category:

public class Category
    {
        public int Id { get; set; }
        public string CategoryName { get; set; }
        public ICollection<Ticket> Tickets { get; set; }
    }

Ticket:

public class Ticket
    {
        public int Id { get; set; } 
        public string Title { get; set; }

        public User User { get; set; }
        public Category Category { get; set; }
    }

User

public class User 
    {
        public string Id { get; set; }
        public string UserName { get; set; }
        public ICollection<Ticket> Tickets { get; set; }
    }

This is what I tried so far. But it is not even close.

public async Task<IEnumerable<Category>> GetCategories(string userid)
        {
            var categories =  _context.Categories
                                            .Include(c => c.Tickets)
                                            .AsQueryable();
            

            categories = categories
                      .Where(c => c.Tickets.Any(t =>t.User.Id.Equals(id)));            

            return await categories.ToListAsync();
        }

How can I have a list of categories with corresponding tickets for a specific userId?

entity-framework-core lambda asp.net linq c#
1 Answers

The issue is this line of code:

categories = categories.Where(c => c.Tickets.Any(t =>t.User.Id.Equals(id)));

This will return all categories that contain at least 1 ticket with the specified user id, but all tickets in the category will be included. My understanding is, you want to keep only the tickets that belong to the specified user id.

The fact that your Tickets are represented by an ICollection<Ticket> instead of IEnumerable<Ticket>, make this a bit more difficult - since LINQ works on IEnumerables. That's the reason for the ToList() call, which causes enumeration - something to be aware of here - Tickets collection is no longer lazy past that point.

This code will do what you want:

IEnumerable<Category> selection = (from c in categories
                                    select new Category
                                    {
                                        Id = c.Id,
                                        CategoryName = c.CategoryName,
                                        Tickets = (from t in c.Tickets
                                                    where t.User.Id.Equals(id)
                                                    select t).ToList()
                                    }).Where(c => c.Tickets.Count() > 0);

but, I think, both performance-wise and readability-wise, you might want a loop instead:

List<Category> selection = new List<Category>();
foreach (var category in categories)
{
    category.Tickets = category.Tickets.Where(t => t.User.Id.Equals(id)).ToList();

    if (category.Tickets.Count > 0)
    {
        selection.Add(category);
    }
}

Finally, since your Ticket class carries the Category info anyway, it may be worth to flatten the list using SelectMany:

var selection = categories.SelectMany(c => c.Tickets.Where(t => t.User.Id.Equals(id)));

This returns a flattened list of Tickets - but only the ones that match the specified user id, from all categories. This has the advantage of staying lazy (it's still an IEnumerable), being simple, and very likely of higher performance than the other options; but, you no longer have the nested list.

Take your pick!

user_27148370
Related