UPDATE: Table Joins in LINQ – Simplify it!
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!
Ramblings of the Sleepy... 
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!
@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();
}