Difficult query in Linq To Sql or SQL

Question

I have been working on this for 2 days and I cannot figure it out. I hope someone smarter than me gives it.

Let's say I have the following tables:

Rating: Id | Name 1 | A 2 | B 3 | C 4 | D 5 | E Inspection: Id | Date (mm/dd/yyyy) 1 | 01/04/2012 2 | 04/04/2012 3 | 28/03/2012 4 | 04/04/2012 Observation: Id | InspectionId | RatingId 1 | 2 | 3 2 | 1 | 2 3 | 4 | 3 4 | 2 | 1 5 | 3 | 3 6 | 1 | 2 

I want the request to be returned:

  RatingName | Date(mm/dd/yyyy) | ObservationCount A | 01/04/2012 | 0 B | 01/04/2012 | 1 C | 01/04/2012 | 1 D | 01/04/2012 | 0 E | 01/04/2012 | 0 A | 04/04/2012 | 1 B | 04/04/2012 | 0 C | 04/04/2012 | 2 D | 04/04/2012 | 0 E | 04/04/2012 | 0 A | 28/03/2012 | 0 B | 28/03/2012 | 0 C | 28/03/2012 | 1 D | 28/03/2012 | 0 E | 28/03/2012 | 0 

Therefore, I need the number of observations for each rating for each date. And yes, I need to have records that return 0 observations, because I use this data in a stacked graph, and without them it gives an error. I managed to get the above table, but without records that return 0 Cases with the following Linq To Sql query, but from now on I'm stuck.

 MyDataContext DB = new MyDataContext(); var data = (from r in DB.Ratings join o in DB.Observations on r.Id equals o.RatingId into ro from observation in ro.DefaultIfEmpty() join i in DB.Inspections on observation.InspectionId equals i.Id into roi from q in roi.DefaultIfEmpty() group q by new { Name = r.Name, Date = q.Date } into grouped select new { RatingName = grouped.Key.Name, Date = grouped.Key.Date, ObservationCount = grouped.Count(x => x != null) }).OrderBy(x => x.Date); 

I would appreciate a response in Linq To Sql or just in old SQL, thanks!

Answer 1

You should try CROSS JOIN in two lookup tables, and then OUTER JOIN go back to your “data” table and then check if Observation null ... then a group and a sum!

 SELECT [Name], [Date], SUM([Exists]) FROM ( SELECT name, [Date], CASE WHEN o.Id IS NULL THEN 0 ELSE 1 END as [Exists] FROM Rating r CROSS JOIN Inspection i LEFT OUTER JOIN Observation o ON o.RatingId = r.Id AND o.InspectionId = i.Id ) as [source] GROUP BY [Name], [Date] ORDER BY [Date], name 

Translating to LINQ will be a similar two-step process — getting an internal set of results (checking if the observation is NULL) before grouping and summing.