Outer Join on two List<> objects returned from entity framework query

Question

I have two Lists (List<>) that I want to perform outer join.

revisionsByDate looks like this:

DateCreated  RevisionCount
10-09-2019   15
11-09-2019   17
12-09-2019   5
13-09-2019   7

progresUpdates looks like this:

DateCreated  Progress
10-09-2019   60
13-09-2019   90
14-09-2019   100

I need to do an outer join on these List objects. I tried the following code, which works fine but does an inner join:

    var resultsToReturn = revisionsByDate.Join(
         progresUpdates,
         revision => revision.DateCreated,
         action => action.DateCreated,
         (revision, action) => new {
             revision.RevisionCount, action.Progress, action.DateCreated });

Output is:

DateCreated Progress  RevisionCount
10-09-2019  60        15
13-09-2019  90        7
14-09-2019  100       0

But, I want to achieve the following:

DateCreated  Progress  RevisionCount
10-09-2019   60        15
11-09-2019   60        17
12-09-2019   60        5
13-09-2019   100       7
14-09-2019   100       0

You may notice that the latest progress 60 is repeated if there is no matching value for the DateCreated value of the revisionsByDate.

Any ideas to convert my code to outer join? Also, I wonder if I should do this already in sql? I am using Entity Framework Core.

Possible duplicate of LINQ to SQL multiple tables left outer join — jazb
– jazb, Commented Jul 23, 2019 at 7:15

silkfire · Accepted Answer · 2019-07-24 08:05:14Z

What you need is both a left and a right join, one after the other.

Try this:

var resultsLeft = revisionsByDate.Select(r => new
                                              {
                                                  Revision = r,
                                                  Action = progressUpdates.Where(pu => pu.DateCreated <= r.DateCreated)
                                                          .OrderByDescending(pu => pu.DateCreated)
                                                                          .First()
                                              })
                                 .Select(_ => new
                                             {
                                                 _.Revision.DateCreated,
                                                 _.Action.Progress,
                                                 _.Revision.RevisionCount
                                             })
                                 .ToList();

var resultsRight = progressUpdates.GroupJoin(revisionsByDate,
                                             pu => pu.DateCreated,
                                             r => r.DateCreated,
                                            (pu, rr) => new                                                                    
                                                        {                                                                        
                                                           ProgressUpdate = pu,                                                                          
                                                           NoMatch =  !rr.Any()                                                                            
                                                        })
                                  .Where(pu => pu.NoMatch)
                                  .Select(pu => new
                                                {
                                                    pu.ProgressUpdate.DateCreated,
                                                    pu.ProgressUpdate.Progress,
                                                    RevisionCount = 0
                                                })
                                  .ToList();


var results = resultsLeft.Concat(resultsRight)
                         .OrderBy(r => r.DateCreated)
                         .ToList();

Collectives™ on Stack Overflow

Outer Join on two List<> objects returned from entity framework query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related