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)
               };
1
0
7/17/2015 6:41:09 AM

Accepted Answer

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; }
}
1
1/22/2018 11:29:39 PM

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


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