Exploring many ways to JOIN with multiple conditions

Hello folks,

Here I demonstrate different ways to perform a INNER JOIN with multiple clauses.

Please consider the following scheme I will use for the examples and pay attention to the Type of Multiplicity (0..1):

Product has Zero or one Brand
Product has exactly one Category
Brand has unlimited number of Products
Category has unlimited number of Products
Category has zero or one ParentCategory (which is Category itself)
Category has Zero or more SubCategories

 
First way: Using Annonymous Type
Second Way: Using where clauses

Given a ParentCategory = 2 (Beverages), this is how you would retrieve Beverages products

var query =
    from p in db.Products
    join c in db.Categories
        on new
        {
            CategoryID = p.CategoryID,
            ParentCategoryID = 2
        }
        equals new
        {
            CategoryID = c.CategoryID,
            ParentCategoryID = c.ParentCategoryID.Value
        }
    select p;

Of course you could simply write the WHERE clause…

var query2 =
    from p in db.Products
    join c in db.Categories on p.CategoryID equals c.CategoryID
    where c.ParentCategoryID == 2
    select p;

…that would work as well.

And here’s a third way which makes an exactly same T-SQL out. To have the T-SQL syntax displayed, check this other post out.

var query3 =
    from p in db.Products
    join c in db.Categories.Where(c => c.ParentCategoryID == 2)
        on p.CategoryID equals c.CategoryID
    select p;

Now, a complex scenario… Let say I I have a checkbox in my UI where users can select if they want to see all products or filter by a specific category (in this case Beverages).

To write this in a single query:

var query =
    from p in db.Products
    join c in db.Categories
        on new
        {
            CategoryID = p.CategoryID,
            ParentCategoryID = 2
        }
        equals new
        {
            CategoryID = c.CategoryID,
            ParentCategoryID = (checkBox.Checked) ? c.ParentCategoryID.Value : 2
        }
    select p;

The same using WHERE:

var query2 =
    from p in db.Products
    join c in db.Categories on p.CategoryID equals c.CategoryID
    where (checkBox.Checked) ? c.ParentCategoryID == 2 : true
    select p;

or:

var query3 =
    from p in db.Products
    join c in db.Categories.Where(c => (checkBox.Checked) ? c.ParentCategoryID == 2 : true)
        on p.CategoryID equals c.CategoryID
    select p;

 

Advertisements

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