How to access associations in a LINQ query?

Question

I'm having problems quering this (I am new to LINQ, please forgive me) and I have spent hours trawling the web. In SQL I just want to do this

SELECT c.Forname, c.Surname cg.Title, g.GroupName
FROM Contact c
    inner join ContactGroup cg on cg.ContactID = c.ID
    inner join Group g on cg.GroupNameID = g.ID
WHERE g.ID=1;

I have attempted it but failed miserably as :

var result = 
    from c in cc.Contacts
    from cg in c.ContactGroups
    from g in cg.Group 
    where g.ID==1
    select new
    {
        c.Forename,
        c.Surname,
        cg.Title,
        g.GroupName
    };

Can someone please show me what I am doing wrong or direct me to somewhere with further information?

Much thanks.

Ludivinaludlew

What error are you getting? IntelliSense should be able to tell you the type of your variables and you should be able to figure it out from there – Queenie

gc.Title should be cg.Title. I changed it, assumed it was a typo. – Testee

In what way are the results of your query unsatisfactory? Does it not compile? Does it give an error message? At a glance, it looks right to me. – Abut

The opening ( and closing ) are not necessary in this case, but other than that it looks good. Of course it won't execute an actual query against the database until you iterate over the result (perhaps with something as simple as a call to ToList(). You'll need to give a bit more detail on what's not working. – Codd

Answer 1

I would switch things around a bit and start your query using the ContactGroup table. That way you have 'access' to all 3 tables.

from c in cc.ContactGroups
where c.GroupNameID == 1
select new {
    c.Contact.Forename,
    c.Contact.Surname, 
    c.Title,
    c.Group.GroupName
}

Here's the SQL produced in Linqpad (using Linq to Sql but EF wouldn't be too different) - it's almost exactly like your query.

-- Region Parameters
DECLARE @p0 Int = 1
-- EndRegion
SELECT [t1].[Forename], [t1].[Surname], [t0].[Title], [t2].[GroupName]
FROM [ContactGroups] AS [t0]
INNER JOIN [Contacts] AS [t1] ON [t1].[ID] = [t0].[ContactID]
INNER JOIN [Groups] AS [t2] ON [t2].[ID] = [t0].[GroupNameID]
WHERE [t0].[GroupNameID] = @p0

Mckoy

Thank you! Is this the "proper" way of doing it or a sneaky way? :) – Ludivinaludlew

@Ichirichi - I think this is the proper way. It certainly produces the 'proper' sql. I have not used EF much but I believe EF4 has built in support for many-to-many relationships (not sure if it was in earlier versions) where you can do away with the joining table - but only if the joining table doesn't have any other columns besides the foreign keys. Linq to Sql doesn't have this support and so this solution is the simplest way to go. – Mckoy

Answer 2

in linq your join looks like this

from c in cc.Contacts
join cg in c.ContactGroups on cg.Id equals c.Id

look here: http://www.dotnetperls.com/join

Priestridden

Not quite right, because he's not joining on the ContactGroups directly from the context. c.ContactGroups already does a join automatically. – Abut

You shouldn't have to join it though because the relation should already be defined in the model. – Testee

thats true. depending on lazy loading / eager loading .. he could also do cc.Contacts.Include("")... right? – Priestridden

Lazy/Eager loading only applies to entities not selected in the select statement. If you traverse the navigation properties as part of your LINQ statement, and select all you need to select, EF will load what you asked for. Lazy/eager loading only applies if one selected a full entity in the select statement and it had other navigation properties on it that you also wanted but didn't select explicitly. – Acetanilide

Answer 3

I personally found LINQPad to be a good resource on learning both LINQ in general and LINQ-to-SQL. Maybe it will help you.

Taranto

Answer 4

Start here: http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx.

Zins