当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - LINQ join lambda syntax - need to translate this from query

问题描述:

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)!

网友答案:

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

网友答案:

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);
...
分享给朋友:
您可能感兴趣的文章:
随机阅读: