Linq query and group by data from table which has one to many relationship?

Question

i have 3 tables, 1 is user table which store all user data, another one is application table, the last is user education table, the relationship between these tables :

an user can have many applications, and also can have many education background

the problem here is i want to select data for monthly report from application table and i want to group by my data by each education level, based on each user highest education, like this :

user A - high school
user A - bachelor degree
user A - master degree
user B - high school
user C - bachelor degree
user D - high school

will result :

master degree : total 1
bachelor degree : total 1
high school : total 2

i have tried this :

var edu = CTX.user_applications.Where(a => a.applied_date >= new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)
                                                        && a.applied_date <= DateTime.Now
                                                      ).GroupBy(g => (g.user_list.user_edus.OrderByDescending(o => o.edu_lvl_id).FirstOrDefault()))
                                                           .Select(x => new ReportObject
                                                           {
                                                               Key = x.Key.ToString(),
                                                               Count = x.Count().ToString()
                                                           }).ToList();

error :

A group by expression can only contain non-constant scalars that are comparable by the server. The expression with type 'System.Data.Linq.EntitySet`1[OTOKarir.Models.user_edu]' is not comparable

it does not work, it give me an error, there's must some trick to do it correctly, btw i am not really sure that it will give me the desired result, i am stug with this, need some guidance, any idea will be very great? thx.

Answer 1

You're ordering by an object: a user_edu. This should be a scalar instead. That can easily be accomplished by selecting its edu_lvl_id.

Showing only the essential parts, this is what you should do:

var edu = CTX.user_applications
             .GroupBy(g => (g.user_list
                             .user_edus.OrderByDescending(o => o.edu_lvl_id)
                             .FirstOrDefault()
                             .edu_lvl_id)); // This is added

Side note: grouping in LINQ-to-SQL is very inefficient, because for each group a separate query is executed to populate the items in the group. You may consider fetching the data first an doing the grouping in memory (LINQ-to-objects). And then you can group by the object again.

Answer 2

Take a look at this answer: LINQ GroupBy on multiple ref-type fields; Custom EqualityComparer

It explains that GroupBy can only be used for types that implement the IEqualityComparer<T> interface.

In order to know whether your query is correct or not, we would need the DB structure/types of data on which you perform the query.