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.

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

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