Home > .net 3.5, c#, LINQ, Visual Studio 2008 > Table Joins in LINQ – Good, bad, and complexly ugly…

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

July 11, 2007

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

  1. Muthukrishnan
    December 28, 2008 at 10:30 pm

    joins in linq which is very interesting one. i ‘ll go to implement in my project.

  2. joe
    June 25, 2009 at 6:41 am

    Why use LINQ? Better to use the old stuff SQL you already know… Adds nothing!!!

  3. Bob
    August 6, 2009 at 5:58 am

    I agree why use Linq at all what does it add? No seems to be able to properly explain the benefits, plus there are no complex examples anywhere, again a new MS technology backed up by piddly stupid little 3 line examples.. what about more complex stuff?

    Just seems like a pile of rubbish to me.

  4. Bob
    August 6, 2009 at 6:07 am

    How about someone show me how to write this up in Linq?

    — GET ALL all Booking_Slots and Any Users that have booked them for a particular
    — Booking_Resource_ID and date
    IF @Mode = 61
    BEGIN

    Select xx.*,u2.First_Name + ‘ ‘ + u2.Last_Name as Opponent_Name from
    (
    Select ROW_NUMBER() over (order by BookingSlots.Booking_Slot_ID) as RowIndex
    ,Booking_ID
    ,BookingSlots.Booking_Slot_ID,DayBookings.User_ID,u.First_Name + ‘ ‘ + u.Last_Name as User_Name from
    (
    Select * from booking_slots where
    Booking_Resource_Type_ID =

    (SELECT max(Booking_Resource_Type_ID) FROM Bookings a
    inner join Booking_Resources b on a.Booking_Resource_ID = b.Booking_Resource_ID
    WHERE
    a.Booking_Resource_ID = @Booking_Resource_ID
    and a.Deleted = 0 and a.Active = 1 )

    ) BookingSlots
    left outer join
    (

    SELECT * FROM Bookings
    WHERE
    Booking_Resource_ID = @Booking_Resource_ID and
    Booking_Date = @Booking_Date
    and Deleted = 0 and Active = 1

    ) DayBookings on DayBookings.Booking_Slot_ID = BookingSlots.Booking_Slot_ID
    left outer join users u on u.user_id = DayBookings.user_ID
    )
    xx
    left outer join Booking_Attendees B_A on B_A.Booking_ID = xx.Booking_ID
    left outer join Users u2 on u2.User_ID = B_A.User_ID
    where xx.User_ID is not null
    order by xx.Booking_Slot_ID

    END

  5. August 6, 2009 at 6:34 am

    @Bob-

    LINQ itself is cool. Don’t confuse LINQ-to-SQL and LINQ as they are separate beasts. Being able to use LINQ to traverse an object tree, collection, etc. is fantastic, but for plain-jane ORM, it’s a bit clunky right now.

    I’m assuming all of your SQLs are sprocs or something that you’re looking to convert? If so, I’d honestly recommend LEAVING them as sprocs and calling them as methods from your LINQ-to-SQL model–especially that massive one.

    Without seeing your object model/database model, I’m not sure where to start for either of your examples (since I can’t see the table relationships). As I mentioned before, it might be more prudent to simply call them as sprocs if the joins are as lengthy (left outer joins) as they look. Keep that pain on the database servers CPUs…

  6. Anahita
    August 14, 2009 at 9:30 am

    that was really helpful,thanx alot,

  1. July 13, 2007 at 11:22 am
  2. February 20, 2008 at 12:45 pm
  3. October 27, 2008 at 5:02 pm
Comments are closed.
%d bloggers like this: