LINQ join lambda syntax - need to translate this from query

Question

I've been banging my head on this one for about 2 days now.

I have a really advanced SQL Server query that I need translated to LINQ.

It involves:

6 inner joins
A select that includes columns from each of the tables
A specific groupby that involves a ton of columns from each table.

I also need to be able to dynamically build a where clause predicate (using predicatebuilder), so if I want the where clause applied in the correct spot, I think I need to use lambda expressions (after much trial and error).

Here is the part that I'm having a very difficult time translating:

var query = from order in orders                         
            join customer in customers on order.CustomerID equals customer.ID
            join ordersection in ordersections on order.ID equals ordersection.OrderID
            join ticket in tickets on ordersection.ID equals ticket.OrderSectionID
            join evt in events on ticket.EventID equals evt.id
            join client in clients on evt.ClientID equals client.id
            join venue in venues on evt.VenueID equals venue.VenueID

Thanks so much for your time (in advance)!

Is the current query posted with Joins working, or you want solution for this too — Mrinal Kamboj
– Mrinal Kamboj, Commented Oct 8, 2016 at 1:57
There's no actual question here. This is just a bunch of statements. You should read How to Ask. We need to have something concrete to work with and a clear problem to solve. — Enigmativity
– Enigmativity, Commented Oct 8, 2016 at 4:33
@Enigmativity, indeed more time is spent in understanding the question, than devising a solution — Mrinal Kamboj
– Mrinal Kamboj, Commented Oct 8, 2016 at 4:43
Not sure if you own database or not. It's better to implement this query as view and query from that view in LINQ. SQL query is much maintainable than LINQ in such case. — qxg
– qxg, Commented Oct 8, 2016 at 8:52
What you show doesn't need translating to LINQ. It is LINQ. And it's the syntax --query syntax-- that's most suitable for joins. There's no point in translating it to the far more clunky method syntax, if that's your question. Let the compiler do that for you. So what exactly is your question when it come to translating? As for building a dynamic where clause, look for "predicate builder + LINQ" and take your pick from numerous hits. — Gert Arnold
– Gert Arnold, Commented Oct 8, 2016 at 18:41

Mrinal Kamboj · Accepted Answer · 2016-10-08 07:07:44Z

2

Following is the Linq version of your query as expected:

var query = orders.Join(customers, o => o.CustomerID, c => c.ID, (o, c) => new { o, c })
                       .Join(ordersections, o => o.o.ID, os => os.OrderID, (o, os) => new {o = o.o, c = o.c,os})
                       .Join(tickets, o => o.os.ID, t => t.OrderSectionID, (o, t) => new {o = o.o, c = o.c,os = o.os,t})
                       .Join(events, o => o.t.EventID, e => e.id, (o, e) => new {o = o.o, c = o.c,os = o.os,t = o.t,e})
                       .Join(clients, o => o.e.ClientID, cl => cl.id, (o, cl) => new {o = o.o, c = o.c,os = o.os,t = o.t,e = o.e,cl})
                       .Join(venues, o => o.e.VenueID, v => v.VenueID, (o, v) => new {o = o.o, c = o.c,os = o.os,t = o.t,e = o.e,cl = o.cl,v});

Final Result / Schema of query is an anonymous type consisting of order,customer,ordersection,ticket,evt,client,venue, which you may want to transform into typed entity / DTO.

In this case we are projecting the result of every join and taking forward complete objects instead of few properties

edited Oct 8, 2016 at 7:07

answered Oct 8, 2016 at 6:59

Mrinal Kamboj

11.6k5 gold badges45 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jon Skeet Over a year ago

Note that you don't need all the names in o = o.o etc - just new { o.o, o.c, o.os etc } would work fine.

Mrinal Kamboj Over a year ago

@JonSkeet thanks for the information, I thought this might lead to multiple levels of referenced access

Jon Skeet Over a year ago

No, it's exactly the same - it just infers the name from the expression used to initialize it.

Chris Over a year ago

@MrinalKamboj thank you very much! This was the perfect solution and worked brilliantly. Thank you Jon also!

Community · Accepted Answer · 2017-05-23 10:27:47Z

The Mrinal Kamboj answer already shows how you can translate the sample query syntax to method syntax. However, that's not strictly necessary because you can achieve the same by simply adding anonymous type projection at the end, and then perform the rest (dynamic filtering, final projection) on that:

var query =
    from order in orders                         
    join customer in customers on order.CustomerID equals customer.ID
    join ordersection in ordersections on order.ID equals ordersection.OrderID
    join ticket in tickets on ordersection.ID equals ticket.OrderSectionID
    join evt in events on ticket.EventID equals evt.id
    join client in clients on evt.ClientID equals client.id
    join venue in venues on evt.VenueID equals venue.VenueID
    select new { order, customer, ordersection, ticket, evt, client, venue };

query = query.Where(dynamicFilter);
...

Collectives™ on Stack Overflow

LINQ join lambda syntax - need to translate this from query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related