CODEWITHSUNDEEP
c#lambda
NNassar
NNassar

Reputation: 485

Join 5 tables using Lambda

I'm trying to use lambda to join 5 tables. What I would like to know is if I'm doing it right and if there's a better way of doing it. Any suggestion is much appreciated.

 var myList = _context.Event
            .Join(_context.EventHost,
                e => e.EventHostId,
                eh => eh.Id,
                (e, eh) => new { Event = e, EventHost = eh })
            .Join(_context.EventAddress,
                eh => eh.EventHost.EventAddressID,
                ea => ea.ID,
                (eh, ea) => new { EventAddress = ea, EventHost = eh })
            .Join(_context.OrderHeaderGroup,
                ea => ea.EventHost.Event.Id,
                ohg => ohg.EventId,
                (ea, ohg) => new { EventAddress = ea, OrderHeaderGroup = ohg })
            .Join(_context.OrderHeader,
                ohg => ohg.OrderHeaderGroup.OrderHeaderGroupID,
                oh => oh.OrderHeaderGroupID,
                (oh, ohg) => new { OrderHeader = oh, OrderHeaderGroup = ohg })
            .Select(c => new {
                c.OrderHeader.OrderHeaderGroup.EventId,
                c.OrderHeader.EventAddress.EventAddress.Name,
                c.OrderHeader.EventAddress.EventAddress.Phone,
                c.OrderHeader.EventAddress.EventAddress.Address1,
                c.OrderHeader.EventAddress.EventAddress.Address2,
                c.OrderHeader.EventAddress.EventAddress.City,
                c.OrderHeader.EventAddress.EventAddress.Country,
                c.OrderHeader.EventAddress.EventAddress.PostalCode,
                c.OrderHeader.EventAddress.EventAddress.Email,
                c.OrderHeader.EventAddress.EventHost.Event.ConsultantId,
                c.OrderHeader.EventAddress.EventHost.Event.StartDate,
                c.OrderHeader.EventAddress.EventHost.Event.EndDate,
            }).Where(x => x.ContactId == contactId && x.StartDate >= newStartDate && x.EndDate <= newEndDate).ToList();

Upvotes: 1

Views: 144

Answers (2)

Suamere
Suamere

Reputation: 6238

The issue here isn't preferential code readability. Which, BTW, I think Lambda is far more readable than Query form. But that's because I'm a programmer.

The issue is that you are trying to take a SQL Developer's perspective and SHOVE It into programming. I did that when LinqToEntities was first a thing, where I start in SSMS et al, I write a query, then I do Transliteration into C#.

The solution is to use your ORM. If you use a feature rich ORM, you should have something called Navigation Properties. These already have implicit joins for you that are either implicitly, or explicitly declared in your Fluent mapping. So you shouldn't have to re-describe every join every time you write a LINQ statement.

If you trust the language and configure your system correctly, you can end up with something like this:

// This is not shortened, this should be the whole thing.
var myList = _context.OrderHeader
.Where(x => x.EventAddress.Something.ContactId == contactId 
      && x.EventAddress.Something.StartDate >= newStartDate 
      && x.EventAddress.Something.EndDate <= newEndDate)
.Select(c => new DetailEntityView(c)).ToList();

I also suggest using "View" classes. These aren't entities that are configured to persist to the DB, but are like DB Views, in C#. They are less used classes that just transfer data.

public class DetailEntityView
{
    public int EventId { get; set; }
    public string Name { get; set; }
    // ... etc

    public DetailEntityView(OrderHeader c)
    {
        EventId = c.OrderHeaderGroup.EventId;
        Name = c.EventAddress.EventAddress.Name;
        // ... etc
    }
}

Now, some ORMs are less featured than the EF's and NH's of the world. So in that case, I'm sorry you or your team chose something else.

Also, all of your joins are on IDs, which is as it should be. But SOME Custom queries join on field that are not constrained, like name fields or other values. In those cases, you do need to add those specific joins to your query. But 1.) Ew. And 2.) Why? And 3.) Then you can do what you did in your OP.

Upvotes: 2

Athanasios Kataras
Athanasios Kataras

Reputation: 26342

Yes, use the query syntax, as is the recommendation from Microsoft: Check here

At compile time, query expressions are converted to Standard Query Operator method calls according to the rules set forth in the C# specification. Any query that can be expressed by using query syntax can also be expressed by using method syntax. However, in most cases query syntax is more readable and concise.

from event in _context.Event
join eventHost in _context.EventHost 
    on event.EventHostId equals eventHost.Id
join eventAddress in _context.EventAddress 
    on eventHost.EventAddressId equals eventAddress.Id
// etc
select new {/*You new anonymous type here*/}

Upvotes: 2

Related Questions