Linq Join Question

Question

Hi guys,

I have a problem trying to do a couple of things with linq joins... currently I have a group in linq that gives two columns, basically a count of tickets by location. Well now I'm trying to add a join that will join on the ticketID columns of two different tables Tickets and Comments.

I'm having a hell of a time trying to convert the sql join into Linq, less alone merging that into my original total count linq statement...somebody please help!

Original Linq statement:

From p In NewTickets _
Where p.TDate > "06/01/2009 12:00:00AM" And p.TDate < "07/01/2009 12:00:00PM" _
    Group p By p.LocationID _
    Into Count() _
    Select _
    LocationID, _
    NoOfTickets = Count _
    Order By NoOfTickets Descending

Join I need merged into Linq statement:

SELECT *
FROM NewTickets as p 
LEFT OUTER JOIN NewComments AS c ON p.TicketID = c.TicketID 
WHERE (p.TDate > '06/01/2009 12:00:00AM') And (p.TDate < '07/01/2009 12:00:00PM')
AND  c.Comment Like '%ali%'

THANK YOU!

Answer 1

If you add a relationship in the linq to sql designer between NewTickets and NewComments, properties will be created on those classes to navigate.

Queries that use those properties will automatically translate into the join. For example:

from t in db.NewTickets
where t.NewComments.Any(nc => nc.Comment.Contains("ali"))
group t by t.LocationID into g
select new {LocationID = g.Key, NoOfTickets = g.Count()} into x
order x by x.NoOfTickets descending
select x;

Apologies for the C# code examples.

Also, I'd like to point out that the left join in your sql is moot - tickets that have no comments will be removed by the ali criteria. An inner join will do fine.

Answer 2

That was it...for the most part...I'm going to have to attack this a different way as now I'm getting a count that apparently from comments as my total has ballooned from under 200 to almost 1300...each ticket will have on average around 5 or so comments so that's why I assuming this just shooting from the hip...

Thank you David and no problem with the C# (as much that I have translated, you think I'd be using it by now).

For anyone using VB that would like to see the same in VB, here you go:

Dim q = From e In db.NewTickets _
                Where e.NewComments.Any(Function(nc) nc.Comment.Contains("ali")) _
                Group e By e.LocationID _
                    Into Count() _
                    Select _
                    LocationID, _
                    NoOfTickets = Count _
                    Order By NoOfTickets Descending