LINQ: How to write queries with complex join conditions

This small post is a follow-up of my previous post in which I discussed some common scenarios and how we can handle them in our LINQ queries. This time, we will see how to write queries that have not-so-simple conditions as join predicates. Note that LINQ directly supports only equi-joins, that is, joins based on equal conditions. Hence, for simple primary / foreign key fields, we can simply use the syntax:

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID

For composite primary / foreign keys, we need to create an anonymous type with same data type and member names in the join predicate:

from c in Customers
join o in Orders on new {c.CompanyID, c.CustomerID} equals {o.CompanyID, o.CustomerID}

However, sometimes, we have join predicates that do not check equality, e.g. our join condition is based on a between check as in the following T-SQL query.

Select ..
 from Order o
 join AccountingPeriod ap
  on o.OrderDate between ap.PeriodStart and ap.PeriodEnd

Such situation can be handled in LINQ by using an always true join condition and transferring the join predicate to the where part. Here’s the LINQ translation for the above SQL query:

from o in dc.Orders
join ap in dc.AccountingPeriods on true equals true
where o.OrderDate >= ap.PeriodStart && o.OrderDate <= ap.PeriodEnd
select …

Ane even better version as pointed by zn00p in the comments section is:

from o in dc.Orders
from ap in dc.AccountingPeriods 
where o.OrderDate >= ap.PeriodStart && o.OrderDate <= ap.PeriodEnd
select …

Notice that this concept is valid for inner joins only. Since for inner joins, the join predicate can be moved to the where part of the TSQL query. The above trick is valid for both LINQ to SQL and LINQ to Entities (Entity Framework).

About these ads

18 Responses to “LINQ: How to write queries with complex join conditions”

  1. DotNetShoutout Says:

    LINQ: How to write queries with complex join conditions « Mehroz’s Experiments…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. ivowiblo Says:

    I don’t understand why you will need this:
    from c in Customers
    join o in Orders on c.CustomerID equals o.CustomerID

    in a well desgined model you will need something more like:

    from c in Customers from o in c.Orders;

  3. zn00p Says:

    why not just use another from instead of this allways true condition …

    from o in dc.Orders
    from ap in dc.AccountingPeriods
    where o.OrderDate >= ap.PeriodStart && o.OrderDate <= ap.PeriodEnd
    select …

  4. Syed Mehroz Alam Says:

    @ivowiblo
    Agreed. If the database has relationships defined then the implicit version you posted is more preferable.

    @zn00p
    That’s even better! I will update the post with this approach. Thanks for sharing.

  5. juan Says:

    from o in dc.Orders
    join ap in dc.AccountingPeriods on true equals true
    where o.OrderDate >= ap.PeriodStart && o.OrderDate <= ap.PeriodEnd
    select …

    and how could i write this query in vb.net? i tried but the compiler says that i cannot put TRUE because i need one field from first table before EQUALS, and one field from the second one after EQUALS

  6. Balaji Says:

    wahhh..

  7. Balaji Malkapure Says:

    dhiraj khobragade

  8. Water Damage Restoration Says:

    How do you perform multiple select returns…. returning certain fields from the domainservice???

  9. Scrape IT! Says:

    Fascinating blog! Is your theme custom made or did you download it from somewhere?
    A design like yours with a few simple tweeks
    would really make my blog jump out. Please let me know where you
    got your design. Thanks – Scrape IT!.

  10. http://pkvguide.wordpress.com/2014/06/30/why-you-cannot-afford-to-get-sick-without-health-insurance/ Says:

    Wonderful blog! I found it while surfing around on Yahoo News.

    Do you have any tips on how to get listed in Yahoo News?
    I’ve been trying for a while but I never seem to get there!

    Cheers

  11. Lauren Says:

    Heya! I just wanted to ask if you ever have any
    trouble with hackers? My last blog (wordpress)
    was hacked and I ended up losing months of hard work due to no data backup.

    Do you have any solutions to prevent hackers?

  12. Gail Says:

    Good day! Would you mind if I share your blog with
    my myspace group? There’s a lot of people that I think would really
    appreciate your content. Please let me know. Many thanks

  13. http://dsl-verfugbarkeit.webnode.com/ Says:

    Currently it looks like WordPress is the top blogging platform available right now.
    (from what I’ve read) Is that what you’re using on your blog?

  14. https://storify.com/privatekranken/analyzing-the-options-of-public-and-private-health Says:

    Good day! This is my first visit to your blog! We are a
    collection of volunteers and starting a new initiative
    in a community in the same niche. Your blog provided us beneficial information to work on. You have done a outstanding job!

  15. Bradley Says:

    Hello! I’ve been following your site for a long time now and finally got
    the bravery to go ahead and give you a shout out from Dallas Texas!

    Just wanted to say keep up the good work!

  16. http://ranktrackersolutions.jimdo.com/ Says:

    Have you ever thought about writing an ebook or guest authoring on other websites?
    I have a blog centered on the same topics you discuss and would love to have you share some stories/information. I know
    my audience would appreciate your work. If you are even remotely interested, feel
    free to send me an e mail.

  17. DSL Vergleich Says:

    Howdy! I just noticed your site: LINQ: How to write queries with complex
    join conditions when I was surfing around digg.com.
    It looks as though someone loved your site so much they decided to bookmark it.
    I’ll definitely be coming back here more often.

  18. Phil Says:

    Your web site appears to be having some compatibilty problems in my ie browser.
    The content appears to be running off the webpage pretty bad.
    If you want you can email me at: phil.logsdon@gmail.com and
    I’ll shoot you over a screen shot of the problem.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 57 other followers

%d bloggers like this: