Home > .net 3.5, c#, LINQ, Visual Studio 2008 > UPDATE: Table Joins in LINQ – Simplify it!

UPDATE: Table Joins in LINQ – Simplify it!

July 13, 2007

A few days ago, I posted up my experience joining tables using LINQ; however, my “join” the other day came up a bit complex due to the odd dot-notation used to access joined columns.  After coming across a blog post from Scott Guthrie, the end product is right, but I was taking an extra, unneeded step in the join.  The OR/M handles the joins using the graphic designer.  So, since that relationship already exists, the “child” column is EXACTLY what we want.

The original code, below, creates both instances and “joins” them.  Well, that’s a repeat of what we have already setup in the OR/M and is a mistake.

gvIncidents.DataSource =

from i in db.Incidents

       from it in db.IncidentTypes

       where i.IncidentTypeId == it.IncidentTypeId

       select i).ToList();

Since we assume that the designer has done a good job creating the joins, we can simply write:

var incidents = from i in db.Incidents

orderby i.IncidentId descending

select i;

If we were wanting to query based on the join, to say, look up by a specific reference name, we’d write:

var incidents = from i in db.Incidents

where i.IncidentTypes.IncidentTypeName == “Tutoring”

       orderby i.IncidentId descending

select i;

Where I referenced the GridView using the IncidentTypes.IncidentTypeName notation was exactly right—just sheer luck to stumble on it.  Also, with that understanding, as you see from the new code snippets, the ToList is no longer necessary.  As always, we can drop down to a breakpoint and look at the actual SQL query—just to make sure, and then tune our LINQ code from there.

Another tidbit is that, in Scott’s post, he makes another mention of the infamous LINQDataSource that will be coming out in Beta2 of Orcas—finally.  It’s functionality should allow us to do a lot more dynamic querying, similar to what we do with a SQL DataSource (basing parameters of the query on controls and such)… good times, good times!

 

Tags: ,

About these ads
  1. rcurrie
    April 29, 2009 at 11:38 am

    Sorry, I’m new to LINQ so bear with me if this is trivial.

    Doesn’t this only work with O/RM designer associations using a one-to-one cardinality? If it’s one-to-many you would be stuck having to use your first example of doing an explicit join in query syntax. Otherwise, if it’s one-to-many, you woul have to search through the “Child” properties collection. Hopefully that makes sense(?).

    Example: (forgive the cheese)
    FanClub Table, FanClubMembers Table, Person Table
    1 Person -> N FanClubMembers (1 Person can belong to Many FanClubs)

    How do we get all people given a FanClub id using the associations method?

    (won’t work since p.FanClubs.FanClubId is invalid. p.FanClubs is a collection)
    int requestedFanClubId = 5;
    var people = from p
    in db.Person
    where p.FanClubs.FanClubId == requestedFanClubId
    select p;

    Here is the join method:
    int requestedFanClubId = 5;

    var people = from p
    in db.Person
    from m
    in db.FanClubMembers
    where p.Id == m.personId && m.FanClubId == requestedFanClubId
    select p;

    Thanks for the clarification!

  2. May 4, 2009 at 7:35 am

    @rcurrie-

    Off the top of my head, I believe you’re right; however, you can get your FanClubMembers to return “persons”. Here’s how I’d set it up; however, keep in mind that I’m going off assumptions for your data schema and application.

    Here’s a link to the data schema: http://is.gd/wAYA

    From there, the code I used was to attach to the FanClub members and return an IQueryable since the relationship exists between FanClubMember and Person.

    using (var db = new FanClubsDataContext())
    {
    var requestedFanClubId = 1;
    var peopleInFanClub = from p in db.FanClubMembers
    where p.FanClubId == requestedFanClubId
    select p.Person;

    foreach (var person in peopleInFanClub)
    {
    Console.WriteLine(person.Name);
    }

    Console.ReadKey();
    }

  1. February 20, 2008 at 12:40 pm
Comments are closed.
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: