How I can optimize my LINQ query?

c# entity-framework-core linq sql-server

Question

I have written following LINQ query to return list of Groups and then iterating list separately.

I only need list of Groups of User with specific email.

I am sure this might not be the right away.

Can i re-write it in a better way (performance-wise) ?

var groups = _context.Users.Where(m => m.Email == email)
                           .Include(g => g.Customer)
                           .ThenInclude(r => r.CustomerGroups)
                           .ThenInclude(t => t.Group)
                           .First().Customer.CustomerGroups;

foreach (var group in groups)
{
    var s = group.Group;
    //do something
}
1
2
8/25/2018 5:17:49 PM

Accepted Answer

If you need just CustomerGroup entities with the related Group entity (as I interpret your clarification in the comment section) it's inefficient to fetch other related entities (User and Customer). You can make EF to fetch only the entities you are interested in like this:

var groups = 
(
    from user in _context.Users
    from customerGroup in user.Customer.CustomerGroups
    where user.Email == email
    select customerGroup
).Include(cg => cg.Group);

Or when CustomerGroup stores no relevant data, just relationships:

var groups = 
(
    from user in _context.Users
    from customerGroup in user.Customer.CustomerGroups
    where user.Email == email
    select customerGroup.Group
);
2
8/25/2018 4:15:49 PM

Popular Answer

Try this :

That is make query on CustomerGroups table so You don't need Include Customer and CustomerGroups .

var customerGroups = _context.CustomerGroups.Where(m => m.Customer.User.Email == email)
                    .Include(t => t.Group).
                     Select(s=> new CustomerGroupModel {
                           A= s.A,
                           B= s.B,
                           …
                           Group = s.Group
                     }).ToList();

Or

  var customerGroups = _context.Customer.Where(m => m.User.Email == email)
                .Include(r => r.CustomerGroups).ThenInclude(t => t.Group).
                 Select(s=> new CustomerGroupModel {
                       A= s.CustomerGroups.A,
                       B= s.CustomerGroups.B,
                       …
                       Group = s.CustomerGroups.Group
                 }).ToList();


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