Making an outer join on two anonymous type lists using LINQ in Entity Framework

Question

I have the following two lists:

var feedbackTaskHistory = _context.FeedbackTasks
   .Select(ft => new { ft.Id, ft.DateCreated })
   .GroupBy(ai => ai.DateCreated.ToString(@"yyyy-MM-dd"))
   .Select(g => new
   {
       Id = g.Key,
       a= g.Where(ad => ad.DateCreated <= Convert.ToDateTime(g.Key)).Count(),
   })
   .ToList();
//[{Id=2019-11-09, a=3}, {Id=2019-11-10, a=3}, {2019-11-11, a=5}]


var discussionPostHistory = _context.RubricItemScoreComments
   .Select(ft => new { ft.Id, ft.RubricItemId, ft.DateCreated })
   .GroupBy(ai => ai.DateCreated.ToString(@"yyyy-MM-dd"))
   .Select(g => new
   {
       Id = g.Key,
       b= g.Where(ad => ad.DateCreated <= Convert.ToDateTime(g.Key)).Count(),
   })
   .ToList();
//[{Id=2019-11-10, b=1}, {2019-11-11, b=2}, {2019-11-12, b=1}]

I want to merge these two lists using OUTER JOIN to get the following list:

  [
      {Id=2019-11-09, a=3, b=0},
      {Id=2019-11-10, a=3, b=1},
      {Id=2019-11-11, a=5, b=2},
      {Id=2019-11-12, a=0, b=1},
  ]

I am planning to use iterate through the lists but this does not sound efficient. I may have around 100 items in both lists. I wonder if there is a performance-efficient way of achieving this task. Any suggestions?

NetMage · Accepted Answer · 2019-11-12 21:18:37Z

Using my extension methods for Full Outer Join, you can just do:

var ans = feedbackTaskHistory.FullOuterJoin(discussionPostHistory,
                                            f => f.Id,
                                            d => d.Id,
                                            (key, left, right) => new { Id = key, a = left?.a ?? 0, b = right?.b ?? 0});

Efficiency wise, the enumerable versions combine a regular LINQ Left Outer Join with a custom Right Anti-Semi Join based on a HashSet to produce the correct answer. This does mean the left and right enumerables are processed twice.

You could also use my OrderableFullOuterJoin in this case (written for a deleted question) which only enumerates each side once, but is quite a bit more complicated. If you know the items are sorted, the OrderedFullOuterJoin is faster still. In either case, for 100 items, I doubt if there is a significant performance difference.

Collectives™ on Stack Overflow

Making an outer join on two anonymous type lists using LINQ in Entity Framework

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