How can I group by a navigation property member in linq to sql?

Question

How can I group by a navigation property member in linq to sql?

c# entity-framework-6 linq-to-sql

Question

I have three tables holding Users Groups and their association, UserGroups as laid out in this fiddle:

I am trying to obtain the maximum level among the users' groups as shown in the query in the fiddle using linq2sql.

However, EntityFramework obfuscates the join table, TblUserGroup and instead just gives me the navigation properties: TblGroups.Users or User.TblGroups

This is what I have put together thus far but Linqpad tells me it cannot execute:

var maxGroup = from ua in ctx.TblGroups
               group ua by ua.TblUsers.Select(s=>s.UserId)
               into g
               select new
               {
                   UserId= g.Key,
                   MaxLevel = g.Max(s => s.GroupLevel)
               };

Matthew

Popular Answer

Does it work for you?

var maxGroup = ctx.TblUsers
    .Where(u => u.TblUserGroups != null)
    .Select(u => new 
            {
                UserId = u.UserId, 
                MaxGroupLevel = u.TblUserGroups.TblGroups.Max(g => g.GroupLevel) 
            }
    );

Adil Mammadov

View more on Stack Overflow

Hossein Narimani Rad · Accepted Answer · 1/22/2018 11:29:39 PM

Seems you can do it like this:

var result = users.Select(u => new 
                               {
                                   UserId = u.Id, 
                                   MaxLevel = u.Groups.Max(g => g.GroupLevel) 
                               });

Having:

class User
{
    public int UserId { get; set; }

    public string UserName { get;set; }

    public List<Group> Groups { get; set; }
}

class Group
{
    public int GroupId { get; set; }

    public string GroupName { get; set; }

    public int GroupLevel { get; set; }

    public List<User> Users { get; set; }
}

How can I group by a navigation property member in linq to sql?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related