Left Outer Join using LINQ

Left outer joins are another example of something that's seems intuitive in SQL but appears foreign in LINQ.  Here we have two tables, TableOne and TableTwo both with a common TableOneId column.  TableOne always has a record and TableTwo has 0 to many records for each TableOne record.  The first query will return every record from both tables even if the TableOne record has no TableTwo record.  The second query will only return records from TableOne only if they have no corresponding TableTwo record.  By the way, this is VB.

'Without a where clause

Dim Query = 
    From t1 In TableOnes
    Group Join t2 In TableTwos On t1.TableOneId Equals t2.TableOneId Into gj = Group
    From grouping In gj.DefaultIfEmpty
    Select t1, grouping


'With a where clause

Dim Query = 
    From t1 In TableOnes
    Group Join t2 In TableTwos On t1.RecordId Equals t2.RecordId Into gj = Group
    From grouping In gj.DefaultIfEmpty
    Where grouping Is Nothing
    Select t1, grouping
 

Query XML files with LINQ

If you regularly work with XML, knowing a technique to directly query it can be a real asset.  I have found LINQ to XML together with LINQPad to be really helpful.  The best thing to do is to simply show an example.  Here we have a trivial XML file that we load in and display:

Then, let's say we want to list all plants from the file:

The tricky part is that if there are namespaces in your xml (and there usually is), then you need to include the namepace in braces.

The Distinct Statement in LINQ

The Distinct statement in LINQ is pretty straight-forward:

Dim SearchResults = (
    From a In EntityName
    Select a.Column1, a.Column2, a.Column3
).Distinct()

However, if EntityName is a view, you may notice the Distinct statement might not work.  You may find that your results contain duplicate rows.  How can this be?

The Distinct capability is only available on columns that aren't set as "unique".  And this makes sense, I suppose, because why would anyone want to do a Distinct on a column where duplicates are impossible?

But if your EntityName is a view, and one of the columns comes from a unique table column, then Entity Framework marks it as an "Entity Key" in the EDMX data model.  Of course, if your view is joining tables, like they often do, you could certainly expect some duplicates in these key fields.  This is exactly the case where a Distinct statement would not work for you.

The lesson here is to open up the EDMX data model, and set the Entity Key property of any non-unique columns in your view to False.  Then Distinct should work properly.

Grouping with Linq

Linq offers pretty much the same aggregate functions that SQL does.  And like SQL, often times you want to group results together when aggregating.  Grouping is a little less intuitive with Linq especially when using VB to write your queries.  Here is an example:

Dim MostRecentByCategory = (
    From mt In MyTable
    Group mt By mt.CategoryId Into g = Group
    Select CategoryId, MostRecentDate = g.Max(Function(mt) mt.CreatedDate) )

In our example, we have a table called MyTable. Each record has a category specified by a CategoryId field. If you wanted the most recent date that a record was created in each category, this query should do the trick. Of course, this is not real practical. More than likely, you are going to want the whole record. In other words, you're going to want a result set that represents the most recent record for each category (not just the date). As far as I know, this cannot be accomplished in just one Linq query.  To accomplish this, I would just create a new linq query and join MyTable to the result set we just queried (on CreatedDate and CategoryId).

As far as I can tell, you can only group when a single table or result set exists in your query.  If you want to group across multiple tables, you'll need to join the tables in a result set and then group the single result set.

For example, let's say there is a table named Category and each record in that table has CategoryId and Description.  How can we group MyTable by CategoryId and display the description of each category?  First we need to join the tables in a result set like this:

Dim Records = ( _
    From a In MyTable
        Join b In Category
            On a.CategoryId Equals b.Category
    Select New With {.CategoryId = a.CategoryId, .CategoryDescription = b.Description})

Now, have a single result set with the field that we need (Description) in it.  Then group this result table like above:

Dim CountsByCategory = (
    From r In Records
    Group r By r.CategoryDescription Into g = Group
    Select CategoryDescription, NumberFound = g.Count() )

What is IEnumerable(Of T)?

IEnumerable is the base interface for all generic collections that can be enumerated (iterated through).  Examples of classes that implement IEnumerable include Lists, Arrays, Stacks, and Queues.  You can also write your own classes that implement it.  Any class that implements this interface can have LINQ queries performed on its enumerator.  You can also iterate through the collection using the For Each operator.  T is the type of item that the collection stores.  When you perform LINQ queries, you often get anonymous classes that implement the IEnumerable interface in return.

The System.Linq namespace contains all kinds of extension methods that operate on classes that implement the IEnumerable interface.  This is what allows you to write LINQ queries against these objects.