Linq expression with multiple joins with condition in each join

Question

I have number of tables like:

User_cs
UserEducation_cs
Experience

following is the database diagram

What I need is users info

their basic info from User_cs
Their last education(i.e. latest degree that they acquired)
Where they are currently working(if user is NOT working then) fields in Experience_cs will be NULL.

I so far able to do following but I am having trouble 1) how in the middle of lamda expression I do (IsWorking==true) and following that join to next table. Following is my expression

 List<Models.UserInfo> v = context.User_cs .Join(context.UserEducation_cs, u => u.UserName, ue => ue.UserName, (u, ue) => new UserEducation_cs { UserName = ue.UserName, EducationId = ue.EducationId, StartDate = ue.StartDate, EndDate = ue.EndDate }). Join(context.Education_cs, ue => ue.EducationId, e => e.EducationId, (ue, e) => new { UserName = ue.UserName, EducationId = ue.EducationId, StartDate = ue.StartDate, EndDate = ue.EndDate, Title = e.Title, Major = e.Major, MajorDetails = e.MajorDetails, Info = e.Info }). Join(context.Experiences, lst => lst.UserName, ex => ex.UserName, (lst, ex) => new Models.UserInfo { UserName = ex.UserName, EducationId = lst.EducationId, StartDate = lst.StartDate, EndDate = lst.EndDate, Title = lst.Title, Major = lst.Major, MajorDetails = lst.MajorDetails, Info = lst.Info, IsWorking = ex.IsWorking, StartDate_ex=ex.StartedDate, }). Where(iw => iw.IsWorking == true).ToList();

Any help will be appreciated

its just doing join right now, but I want is to have conditions that I need to check 1)IsWorking in Experience_cs, should be true 2) in Education_cs, only lastest LastDate rows should be included in join — user786
– user786, Commented Nov 22, 2016 at 7:38
I think you have to use a .GroupJoin(). In that case the right side is not a single element, but an IEnumerable of all matching elements. Then you can apply further conditions or filters on that sequence to grab the desired element(s) (e.g. one, multiple or none) you need. — Oliver
– Oliver, Commented Nov 22, 2016 at 7:57

slawekwin · Accepted Answer · 2016-11-22 07:53:25Z

I'm not entirely sure if I understand your question completely. I think you need to filter your lists before applying the (inner) join to them. To get only newest Education_cs entry for each User_cs I suppose you could use grouping. Also, I have not exactly tested this code:

List<Models.UserInfo> v = context.User_cs .Join(context.UserEducation_cs, u => u.UserName, ue => ue.UserName, (u, ue) => new UserEducation_cs { UserName = ue.UserName, EducationId = ue.EducationId, StartDate = ue.StartDate, EndDate = ue.EndDate }). Join(context.Education_cs.GroupBy(q => q.UserName).Select(q => q.OrderByDescending(w => w.StartDate).First()), ue => ue.EducationId, e => e.EducationId, (ue, e) => new { UserName = ue.UserName, EducationId = ue.EducationId, StartDate = ue.StartDate, EndDate = ue.EndDate, Title = e.Title, Major = e.Major, MajorDetails = e.MajorDetails, Info = e.Info }). Join(context.Experiences.Where(ex => ex.IsWorking), lst => lst.UserName, ex => ex.UserName, (lst, ex) => new Models.UserInfo { UserName = ex.UserName, EducationId = lst.EducationId, StartDate = lst.StartDate, EndDate = lst.EndDate, Title = lst.Title, Major = lst.Major, MajorDetails = lst.MajorDetails, Info = lst.Info, IsWorking = ex.IsWorking, StartDate_ex=ex.StartedDate, }).ToList();

you understood it right, I also want to select only rows from experience_cs where user is currently working
@Alex, isn't that what IsWorking does? If not, how do you determine that?
for some reason UserName = ue.FirstOrDefault().UserName...,is working. are u forgot to include FirstOrDefault()?
@Alex, you mean in second Join you are getting a collection? First after OrderByDescending should have flatten the groups to single (newest) elements, but maybe I'm missing something. Also, I have just now noticed that you could apply the same grouping and selection in the first Join instead of the second one (as it is now, the first Join does nothing).

Collectives™ on Stack Overflow

Linq expression with multiple joins with condition in each join

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related