Multiple table Linq join query

Question

My table schema

The query I need is:

I want to list ALL the golf courses (GolfCourseInfo) along with the DatePlayed (GolfQuestTee).

Not all golf courses have been played, so then the DatePlayed will be empty, but I need to list ALL the golf courses no matter what.

I've struggled with a few queries in LinqPad but they always return just the courses that have corresponding DatePlayed values, and that's not what I need.

Any help is appreciated.

What you are looking for is performing multiple left outer joins. Check out [this answer](http://stackoverflow.com/questions/267488/linq-to-sql-multiple-left-outer-joins). — TylerOhlsen, Jul 10 '13 at 02:13
@TylerOhlsen - I looked at this answer and while the concept probably works, I couldn't get it to work, completely. It was returning duplicate GolfCourseInfo CourseNames. I couldn't figure out where to put the Distinct operator. — Kahanu, Jul 11 '13 at 18:03

score 2 · Accepted Answer · answered Jul 10 '13 at 07:55

2

Assuming that you just connect to the database in Linqpad (and, thus, use LINQ to SQL under the hood) it should be something like this:

from info in GolfCourseInfos
select new { 
               info,
               Dates = info.Tees.SelectMany(t => t.GolfQuestTees)
                                .Select(x => x.DatePlayed)
           }

It will give empty Dates collections where there are no dates.

answered Jul 10 '13 at 07:55

Gert Arnold

105,341
31
202
291

This worked! I didn't realize it could be so, concise and simple (looking). Thanks. – Kahanu Jul 11 '13 at 17:39
I'm trying to change the Dates collection to be a string for a viewmodel. The DatePlayed property would be empty or a formatted date string. I'm having a problem getting that to work. I get the error that a .ToString() or .ToShortDateString() is not recognized. Do you have any ideas how I would just return a string for the date? – Kahanu Jul 12 '13 at 01:22
Nevermind. I figured it out. I created two runs, the first one returned your query, and then I ran it again and did the type converting into my viewmodel. Thanks. – Kahanu Jul 12 '13 at 02:26

mnsr · Answer 2 · 2013-07-10T02:53:11.157

1

Guessing here, but something like this?

from c in GolfCourseInfo 
join t in Tee on c.Id equals t.CourseInfoId
join q in GolfQuestTee on t.Id equals q.TeeId
select new
{
   CourseID = c.Id,
   CourseName = c.CourseName,
   Location = c.Location,
   DatePlayed = q.DatePlayed
}

edited Jul 10 '13 at 02:53

answered Jul 10 '13 at 02:36

mnsr

12,337
4
53
79

1

This is almost identical to my attempts, but unfortunately doesn't return the correct results. It only returns those golf courses that have been played, and not also those that have not. – Kahanu Jul 11 '13 at 17:15

Multiple table Linq join query

2 Answers2