join 3 sets of data using linq

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!

1 answer

  • answered 2018-11-08 06:11 Xing Zou

    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
    
                          };