Linq to SQL Foreign Keys

Question

DDL for Database Tables:

    Users:
    id - int - identity
    name - varchar - unique

    PCs:
    id - int - idnetity
    name - varchar - unique
    userid - FK to Users

    Apps:
    id - int - identity
    name - varchar
    pcid - FK to PCs

I created a DataContext using the Linq To SQL designer in Visual Studio 2008.

I want to perform this query:

select
  users.name,
  pcs.name,
  apps.name
from
  users u
  join pcs p on p.userid = u.id
  join apps a on a.pcid = p.id

I was told in another thread where I posted an answer that the following was incorrect and that it created a cross-join.

var query = from u in db.Users // gets all users
     from p in u.PCs // gets all pcs for user
     from a in p.Apps // gets all apps for pc
     select new
     {
      username = u.Name,
      pcname = p.Name,
      appname = a.Name
     };

When I execute this query I get the correct results. A cross-join with two records in each table should return 8 records but my query correctly returns the two records.

Am I lucky, or is the person telling me that I'm wrong confused?

Answer 1

That looks fine to me, I'm not sure why that would create a cross join

Answer 2

That will work. It's normal because the second "from" inquires in the PC's set of the users and the third "from", the Apps set of the pcs. And I guess that is not going generate a cross join as T-Sql, because the conditions are already defined in the foreign keys and associations.

But I guess this should be the syntactically you want by inner join;

var query = from u in db.Users
            join p in db.PCs on p.UserId == u.Id
            join a in db.Apps on a.PCId == p.Id
            select new
            {
                username = u.Name,
                pcname = p.Name,
                appname = a.Name
            };

Answer 3

Jason - I had missed the fact that you were using the built-in Linq to SQL relationships. Sorry for the misunderstanding; under normal circumstances what you have proposed here will work just fine.