Table Joins in LINQ – Good, bad, and complexly ugly…

Let’s take a look at the different ways we can join tables together using LINQ.

I’ve seen quite a few examples of using the join command in a LINQ query, but a few concerns that it typically creates two objects (one from each table) rather than “joining” them.

gvIncidents.DataSource = from i in db.Incidents

     from it in db.IncidentTypes

                          select new { i, it };

This code simply creates a new object with two components. Why?  Because the generating SQL code does exactly what you asked for; newing up an object that contains both an instance of Incidents (i) and an instance of IncidentTypes (it).

So, what if we explicity create our object?

gvIncidents.DataSource =

from i in db.Incidents

from it in db.IncidentTypes

where (i.IncidentTypeId == it.IncidentTypeId)

orderby i.IncidentId descending

select new { IncidentId = i.IncidentId,

DateEntered = i.DateEntered,

EnteredBy = i.EnteredBy,

SchoolId = i.SchoolId,

CallerName = i.CallerName,

ParentIds = i.ParentIds,

StudentIds = i.StudentIds,

IncidentTypeName = it.IncidentTypeName };

 

Well, that worked.  We got our information… (with a bit of ordering too!) 

The LINQ command reads just like a SQL Statement, but wow, for huge objects, specifying each and every one of those attributes will be a pain and will need to be updated each and every time you update the page—a huge DRY violation.

So, what about using the ToList() method to simply populate a generic list?

gvIncidents.DataSource =

from i in db.Incidents

       from it in db.IncidentTypes

       where i.IncidentTypeId == it.IncidentTypeId

       select i).ToList();

Oh, well, that’s cool.  Now we have a nifty generic List<Incident> (since it knew what type of objects it was populating).  Unfortunately, our Join ended up Joined as:

listItem.IncidentTypes.ColumnName

So, how would you work with that in a GridView?  If you try to use the dot notation, you get a kick-back from the compiler.

<asp:boundfield DataField=”IncidentTypes.IncidentTypeName”
HeaderText=”Incident Type”>

A field or property with the name ‘IncidentTypes.IncidentTypeName’ was not found on the selected data source.

Oh, well, that’s not good.  So how can we fix it?  We have to outsmart it.  I’m not sure if this is an Orcas B1 flaw, a “feature”, or what, but changing the column type in the GridView works like a champ.

If we convert from a BoundField to a TemplateField:

<asp:templatefield HeaderText=”Incident Type”>

<itemtemplate>

<asp:Label ID=”IncidentTypeNameLabel” runat=”server”

Text=’<%# Eval(“IncidentTypes.IncidentTypeName”) %>‘ />

</itemtemplate>

</asp:templatefield>

There we go! Our GridView displays correctly without any issues!

The new method, simply using ToList() resolves our DRY issue—if the columns change, we only need to make sure our GridView is updated, not touching teach call to the column.

Now, as with all “ways”, there’s different applications to each.  If we wanted to join, but NOT return every column, then our first working join would be great.  Imagine if we just wanted the IncidentType and a Count of how many times each occured or some other totally crazy thing…

gvIncidents.DataSource = from i in db.Incidents

from it in db.IncidentTypes

       where (i.IncidentTypeId == it.IncidentTypeId)

       orderby it.IncidentTypeName descending

       group i by it.IncidentTypeName into grouping

       select new { CountOfIncidents = grouping.Count(),

                     IncidentTypeName = grouping.Key };

While that code will take a bit to get used to writing, it’ll take a bit more for me to get used to the fact that I could simply write the SQL statement below… but then I’d have to put it into a DataRecord or reader or something… and not have cool IntelliSense.  Oh the troubles we put ourselves through for pretty IntelliSense.

 

SELECT COUNT(i.IncidentId), it.IncidentTypeName

FROM Incidents i, IncidentTypes it

WHERE i.IncidentTypeId = it.IncidentTypeId

GROUP BY it.IncidentTypeName

Tags: linq, dlinq, lambda+expression, sql