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

Advertisements

Article Posted: AutoCompleteComboBox for Silverlight

I have posted an article at CodeProject on customization of the Silverlight AutoCompleteBox to be used as a type-ahead ComboBox in LOB applications at CodeProject. The AutoCompleteComboBox can be used in typical Object-to-Object associations (one that we typically encounter when creating associations in Entity Framework) as well as Foreign Key associations (the new association type introduced with Entity Framework 4).

Here’s a sample usage of the control in a typical MVVM scenario:

  • Object to Object Association:

    Example data structure:

    public class SalesOrderDetail
    {
        Product product;
        public Product Product
        {
            get { return product; }
            set { product = value; }
        }
    }
    

    Example control usage:

     <custom:AutoCompleteComboBox
       SelectedItemBinding="{Binding Product, Mode=TwoWay}"
       ItemsSource="{Binding Path=Products, Source={StaticResource ViewModel}}"
     />
    
  • Foreign Key Association:

    Example data structure:

    public class SalesOrderDetail
    {
        int productID;
        public int ProductID
        {
            get { return productID; }
            set { productID = value; }
        }
    }
    

    Example control usage:

     <custom:AutoCompleteComboBox
       SelectedValue="{Binding ProductID, Mode=TwoWay}"
       SelectedValuePath="ProductID"
       ItemsSource="{Binding Path=Products, Source={StaticResource ViewModel}}"
     />
    

To view the implementation details, you can read the full article at :
http://www.codeproject.com/KB/silverlight/AutoComplete_ComboBox.aspx

The source code along with a demo project can be downloaded from the article as well as here (28 KB). Remember to rename the file as zip for extraction.