>Query keywords – join, let

>

join: Similar to SQL join, the join keyword is used to join sets. One thing you should note that, if there is a direct relationship in the object model a join is not needed. This behavior differs from SQL. Consider the infamous Northwind database. In SQL server (and in VFP northwind.dbc sample database) there are relations between the tables defined.  You can’t simply create joins without specifying a relation in your SQL:

select * from Customers, Orders

would be a newbie’s error (most of the time) in an attempt to join two tables and expect them to be joined using CustomerId. End result would be a Cartesian join matching each Customer with every Order in Orders set. With Linq you can make the same mistake but it is harder to do:

from c in Customers
from o in Orders
select new { c.CustomerID, o.OrderID }

With the above SQL our intention would likely be an equijoin:

select Customers.CustomerID, Orders.OrderID from Customers, Orders where Customers.CustomerID = Orders.CustomerID

select Customers.CustomerID, Orders.OrderID from Customers inner join Orders on Customers.CustomerID = Orders.CustomerID

select Customers.CustomerID, Orders.OrderID from Customers left join Orders on Customers.CustomerID = Orders.CustomerID

First two are typical inner join queries and 3rd left outer join. In Linq writing such queries is easy. You could write the inner join like this one:

from c in Customers
from o in c.Orders
    select new { c.CustomerID, o.OrderID }

and left outer join like this one:

from c in Customers 
    select new { c.CustomerID, OrderID = c.Orders.Select( o => o.OrderID ) }

While you could use "join" in these, join in Linq is for creating joins on properties that doesn’t already define a relationship in object model (it is easy to forget we are doing a query against objects and object models). Within a database it is unlikely to make join queries that are not already defined with relationships but you may never know what you need. And you wouldn’t always use Linq against collections that come from database based or some other type of well defined object model. We will however see "join" here with already well defined relationships just for the sake of your familiarity with those table and key field names. It makes it easier to guess the result without actually seeing it then.

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

I should also note that using join on primary – foreign key properties causes an "inner join" to be generated. There is also another type of join called "Group Join". You can create group join using "join" and "into" clauses (keywords) together:

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID into orderGroup
select new {c.CustomerID, orderGroup }

Or if we remember that we are working with objects could write it like this as well:

from c in Customers
join o in Orders on c equals o.Customer into orderGroup
select new {c.CustomerID, orderGroup }

But also there is one another and maybe easier to write and understand variation of the same query (and both generate the same left outer join) – provided there is a relationship that we can use in object model (you may call it "relationship" or "navigational property"):

from c in Customers select new {c.CustomerID, c.Orders}

let: Let is a cool keyword  that allows you to store inline expressions. Those who use SQL server 2005 and later know that how much CTE (Common Table Expression – with {…} as ) is welcomed easing the writing of queries and increasing the performance as well. Let keyword is not CTE of course but looks very similar. Using it you assign a variable a complete expression inline creating a "sub-expression". That sub-expression is evaluated only once and can be used in operations within the outer expression. Now let’s see how let lets you < g > write more succinct expressions. I will start with an exact copy from LinqPad’s 5 minutes samples:

from p in Products
let spanishOrders = p.OrderDetails.Where (o => o.Order.ShipCountry == "Spain")
where spanishOrders.Any()
orderby p.ProductName
select new
{
    p.ProductName,
    p.Category.CategoryName,
    Orders = spanishOrders.Count(),   
    TotalValue = spanishOrders.Sum (o => o.UnitPrice * o.Quantity)
}

Here spanishOrders is an inline variable (well not very true to call it variable, purists should forgive me) whose value is assigned from 

p.OrderDetails.Where (o => o.Order.ShipCountry == "Spain")  

expression. This expression is the same as this one written in comprehension syntax:

from o in p.OrderDetails where o.Order.ShipCountry == "Spain" select o

(and you could replace it with this one enclosing it in parentheses). It is later used in 3 places:

where spanishOrders.Any()
orderby p.ProductName
select new
{
    p.ProductName,
    p.Category.CategoryName,
    Orders = spanishOrders.Count(),   
    TotalValue = spanishOrders.Sum (o => o.UnitPrice * o.Quantity)
}

I think you can see it without any further explanation. It is acting like an extraordinary SQL subquery (but it doesn’t need to be something like a subquery, any expression would do and if that expression is returning something queryable you can query it). Here is another sample (this sample is from .Net documentation, you can see the original here):

string[] strings =
        {
            "A penny saved is a penny earned.",
            "The early bird catches the worm.",
            "The pen is mightier than the sword."
        };

// Split the sentence into an array of words
// and select those whose first letter is a vowel.
var earlyBirdQuery =
    from sentence in strings
    let words = sentence.Split(‘ ‘)
    from word in words
    let w = word.ToLower()
    where w[0] == ‘a’ || w[0] == ‘e’
        || w[0] == ‘i’ || w[0] == ‘o’
        || w[0] == ‘u’
    select word;

earlyBirdQuery.Dump();

There are two let keywords here. First one gets each sentence and stores the result of Split(‘ ‘) function to words. "words" is another enumerable ( words of sentences in string ) and it is followed by another query ( from word in words ). For each word second "let" stores the result of word.Lower() to "w". "where" clause use that w multiple times. Without "let" word.Lower() itself would be called multiple times ( where w[0] == ‘a’ || w[0] == ‘e’ … w[0] == ‘u’ ).

Here is another sample which I wrote as a reply on MSDN C# forum. The question was (as I remember it):

There is tickdata like this one:

create table tickdata (timed datetime, price money, quantity int)
insert into tickData values (’06:01′,100,20)
insert into tickData values (’06:02′,90,30)
insert into tickData values (’06:03′,129,10)
insert into tickData values (’06:11′,112,8)
insert into tickData values (’06:12′,150,60)
insert into tickData values (’06:20′,110,10)
insert into tickData values (’06:23′,120,5)
insert into tickData values (’06:24:59′,130,15)
insert into tickData values (’06:25′,140,35)

From this data generate stock trade like data that looks like:

period open low high close volume
06:00 – 06:05 100.0000 90.0000 129.0000 129.0000 60
06:10 – 06:15 112.0000 112.0000 150.0000 150.0000 68
06:20 – 06:25 110.0000 110.0000 130.0000 130.0000 30
06:25 – 06:30 140.0000 140.0000 140.0000 140.0000 35

where period is defined as 5 minute intervals starting from the first available tickdata time and shows for each period with data. This was the Linq solution I came up with (besides "let" keyword there is grouping and query methods):

from myData in Tickdata.OrderBy( t => t.Timed ).AsEnumerable()
  group myData by ((int)myData.Timed.Value.TimeOfDay.TotalMinutes) / 5 into barData
  let bStart = TimeSpan.FromMinutes( barData.Key * 5 ).ToString()
  let bEnd   = TimeSpan.FromMinutes( (barData.Key+1) * 5 ).ToString()
  select new
  { period = bStart.Substring(0,5) + " – " + bEnd.Substring(0,5),
    open   = barData.FirstOrDefault().Price,  
    low    = barData.Min( bd => bd.Price ),
    high   = barData.Max( bd => bd.Price ),
    close  = barData.LastOrDefault().Price,
    volume = barData.Sum( bd => bd.Quantity ) }

This concludes our query keywords series. Other keywords are conceptual ones used to complete syntax (in, on, equals, by, ascending, descending).

About cetinbasoz

A developer working primarily with C#,F#,VFP,SQL server, Silverlight,Linq ...
This entry was posted in Uncategorized. Bookmark the permalink.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s