join 3 sets of data using linq

Question

I'm using Microsoft Entity Framework and .Net Core 2.1 to create a web application.

In one of my controllers, I'm trying to join 3 different sets of data.

Here is how I'm handling filtering the data:

    var eventSponsers = await _context.Sponsers
                    .Where(s => s.catalogId != null).ToListAsync();

    var eventBands = await _context.Bands
        .Where(b => b.typeId == 3).ToListAsync();

    var eventTickets = await _context.Tickets
        .Where(t => t.dateTimeStart >= startDate && t.dateTimeStart <= endDate).ToListAsync();

Now, I need to do something like this(tsql):

    select *
    from eventBands eb
    left join eventTickets et ON et.venueID = eb.venueID
    left join eventSponsers es ON es.eventID = et.eventID

Is there a way to do that with c# and linq?

Thanks!

Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Rui Jarimba, Nov 07 '18 at 18:48
See also https://stackoverflow.com/a/1971086/558486. It's a VB.NET question but should be easy enough to convert the code to C# ;) — Rui Jarimba, Nov 07 '18 at 18:51
Perhaps my [SQL to Linq Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you. The principles apply even for LINQ to Objects. — NetMage, Nov 07 '18 at 22:26

Ryan · Accepted Answer · 2018-11-09T02:27:17.413

2

Try to use below linq to join three sets of data:

var result = from eb in eventBands
                      join et in eventTickets on eb.venueID equals et.venueID into bt
                      from x in bt.DefaultIfEmpty()

                      join es in eventSponsers on x.eventID equals es.eventID into st
                      from y in st.DefaultIfEmpty()
                      select new
                      {
                          // Add your assignments
                          //eb = eb.venueID,
                          //et = x.venueID,
                          //es = y.eventID

                      };

Update:

To return the result to view, try to use ViewModel.

 var result = ...
                      select new LinqViewModel
                      {


                      };
 return View(result.ToList());

In View:

@model IEnumerable<LinqViewModel>

edited Nov 09 '18 at 02:27

answered Nov 08 '18 at 06:11

Ryan

19,118
10
37
53

How would I return that to my view, something like this? " return View(result.FirstOrDefault()); " Thanks! – SkyeBoniwell Nov 08 '18 at 15:07
I'm sorry but I have another question. In your example, what is "from x" and "from y" ? When I try to use those in my project, they are not recognized. Thanks! – SkyeBoniwell Nov 08 '18 at 15:16
Use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. x represents a Ticket class in bt and y represents a Sponser class in st. x,y could be changed to other words.See https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/join-clause#left-outer-join – Ryan Nov 09 '18 at 01:39
If you would like to return the result to view, you need to specify a ViewModel and use "select new ViewModel{}" instead of anonymous type. Then return the result.ToList() and use @model IEnumerable in view – Ryan Nov 09 '18 at 02:21

join 3 sets of data using linq

1 Answers1