LINQ LEFT OUTER JOIN, WHERE & MIN/MAX/Count difficulty

I'm new to LINQ and am finding it a bit difficult with understanding some of the flows of the data transformation as each LINQ statement is being strung together. I've got a fairly standard type SQL statement and I'm have a tough time getting a similar LINQ statement built.

Joining and Grouping seem to be fairly straight forward, however when I finally try to get the Select statement going and put all of the various fields from both tables, things seem to fall apart for me. I'm hoping that I may post some SQL code, then my attempt at cobbling a LINQ expression together and folks here can answer my questions on HOW this is basically supposed to work. Pretty sure the LINQ is AL开发者_高级运维L WHACKED up. :-/

I have the following SQL statement that provides me with a selection of data that I'd like to then use and pass to a grid control. (Using LINQ to Objects however)

Here's the SQL Statement that I'm trying to emulate in LINQ:

SELECT s.STRUCTURE_ID, s.TITLE, s.PHOTO, s.PHOTO_LINK, COUNT(s.STRUCTURE_ID)
   AS "How Many Activities", MIN(m.START_DATE) AS "START DATE", MAX(m.FINISH_DATE) AS "FINISH DATE"
FROM DB.STRUCTURES s
LEFT OUTER JOIN DB.ACTIVITIES m ON s.STRUCTURE_ID = m.STRUCTURE_ID
WHERE s.PARK = 'Elwood' AND m.CONTRACT_NO IS NOT NULL
GROUP BY  s.STRUCTURE_ID, s.TITLE, m.STATUS, s.PHOTO, s.PHOTO_LINK
ORDER BY s.STRUCTURE_ID

DB.Structures is the ONE file

DB.Activities is the MANY file

The goal of the selection is to select all structures that have Activities, match a specific park and DO have a contract. The additional little twist is to get the earliest Start & Finish dates from the associated Activities for each Structure.

The resulting data collections should be a flattened collection that I can simply hand off to my grid control in the following format:

Structure ID     Description           Photo      Start Date     Finish Date
HL-100           Activity Room 100     Yes        6/6/2011       8/26/2011
HS-400A          Small Ones Gym        No         5/2/2011       6/30/2011

So, the LINQ that I've put together is the following:

var query = from s in db.Structures
            join a in db.Activities on s.Structure_ID equals a.Structure_ID into sa
            from allStructs in sa.DefaultIfEmpty()       // I believe this is how to get the outer join and flatten the selection?
            where s.PARK == 'Elwood' && allStructs.CONTRACT_NO != ''
            orderby s.Structure_ID
            group s by s.Structure_ID into g
            select new
            {
                myID = g.Key,
                myDesc = //I don't know how to display the s.Title here ,
                myPhoto = // I don't know how to display the s.Photo here ,
                myStartDate = g.Min(c => c.START_DATE),
                myEndDate = g.Max(c => c.FINISH_DATE)
            };

So, now for the questions:

1. One thing that isn't clear to me in LINQ, it doesn't appear that when the join is done, that I get a singular collection that has ALL DECLARED columns like I would get with a standard SQL statement returning the selection. This is confusing to me. Could somone clue me in on whether that is true or not? Seems like I should be able to 'see' all columns in the 'allStructs' object...but that's obviously not the case.

2. The other major issue I'm struggling with is how to show the columns from the ONE file - I can't simply find a way to navigate to those fields. (Which kind of ties into #1 above.)

3. Lastly, are there some good 'tricks' or tools that you can step through each line in a LINQ expression like you can in the debugger for straight C# code? When that LINQ expression hits the regular debugger in VS, it just highlights the entired expression and you can't really SEE what is taking place as things get setup/performed. (I realize that this might be asking much here.) I'm currently just using the Console to see things after the expression is set in motion such as doing a query.Count() or something like that.

I would truly appreciate any help in getting a better understanding of how the data is transformed through this LINQ process. I've been reading here alot, MSDN & bought a couple of books, but I haven't gotten that 'Light bulb moment' that everyone seems to indicate will come.

Thanks to all... :-)

---

1. After the join you have access to s (the current structure) and sa, the group of activities for that Structure_ID. After DefaultIfEmpty() you have access to the same s and now allStructs which would correspond exactly with m in your original SQL example. You'll never get a single variable that represents all columns in both tables, but you don't get that in SQL either.

2. I believe LINQ to SQL is smart enough to translate grouping on a composite key, in which case this is what you probably want:

   var query = from s in db.Structures
               join a in db.Activities on s.Structure_ID equals a.Structure_ID into sa
               from m in sa.DefaultIfEmpty() // LEFT OUTER JOIN
               where s.PARK == 'Elwood' && m.CONTRACT_NO != ''
               orderby s.Structure_ID
               group m by new { s.Structure_ID, s.Title, s.Photo } into g
               select new
               {
                   myID = g.Key.Structure_ID,
                   myDesc = g.Key.Title,
                   myPhoto = g.Key.Photo,
                   myStartDate = g.Min(c => c.START_DATE),
                   myEndDate = g.Max(c => c.FINISH_DATE)
               };
   

   Note that I switched allStructs to m for consistency with your SQL, and switched group by to use m instead of s. The m values are what will be contained in g's groups, with Key representing the tuple of Structure_ID, Title and Photo.

3. For debugging, your best best is to turn on some logging (DataContext.Log property) to see what SQL is being generated. You can also use SQL profiler. It sometimes helps to work things out logically with in-memory collections and LINQ to Objects, with the caveat that you can achieve things in-memory that don't translate at all to SQL.

---

To answer your second question I think you can do the following:

myDesc = g.First().Title
myPhoto = g.First().Photo