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).
April 13, 2010 at 11:12 PM
LINQ: How to write queries with complex join conditions « Mehroz’s Experiments…
Thank you for submitting this cool story – Trackback from DotNetShoutout…
April 14, 2010 at 5:57 PM
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;
April 14, 2010 at 6:28 PM
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 …
April 14, 2010 at 8:47 PM
@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.
September 18, 2010 at 10:26 PM
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
July 8, 2011 at 11:24 AM
wahhh..
July 8, 2011 at 11:25 AM
dhiraj khobragade
October 27, 2011 at 2:04 AM
How do you perform multiple select returns…. returning certain fields from the domainservice???
August 12, 2014 at 8:43 PM
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!.
August 15, 2014 at 6:41 PM
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
August 19, 2014 at 11:58 AM
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?
August 20, 2014 at 11:34 PM
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
August 24, 2014 at 2:14 AM
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?
August 24, 2014 at 9:53 AM
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!
August 24, 2014 at 12:32 PM
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!
August 26, 2014 at 2:01 AM
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.
August 26, 2014 at 9:21 PM
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.
August 29, 2014 at 12:19 AM
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.
January 18, 2015 at 10:42 AM
[…] LINQ: How to write queries with complex join conditions … – 13/4/2010 · LINQ: How to write queries with complex join conditions April 13, 2010 — Syed Mehroz Alam… […]