group by join and aggregate (linq)

I have a problem with a LINQ query and don't know how to solve it :/

I've got two tables. One is with attributes and the second are rows from attributes, but I need to sum hours from rows and get it to attributes; can't explain it very well ;) Here is my query that I tried to create:

var query = (from ta in session.db.TimesheetAttributes
             join tr in session.db.TimesheetRows on ta.Id equals tr.Ti开发者_如何学JAVAmesheetAttributesId into tempTR
             from ttr in tempTR.DefaultIfEmpty()
             group ttr by new { ta.TimesheetId, ta.Date, ta.SickNote, ta.Vacation, ta.OccasionVacation } into g
             select new
             {
                 Id = g.Key.TimesheetId,
                 Date = g.Key.Date,
                 WeekDay = g.Key.Date.ToString("dddd", new CultureInfo("pl-PL")),
                 Description = String.Format("{0:dd/MM/yyyy}", g.Key.Date),
                 SickNote = g.Key.SickNote,
                 Vacation = g.Key.Vacation,
                 OccasionVacation = g.Key.OccasionVacation,
                 Hours = String.Format("{0:HH:mm}", (new DateTime(g.Aggregate(TimeSpan.Zero, (subtotal, t) => subtotal + (t.DateTo - t.DateFrom)).Ticks)))
             }).OrderBy(c=>c.Date).ToList();

The problem is that not every attribute's got rows, that's why I tried to use DefaultIfEmpty(), however this query is not working :(

My old query is working, but my ORM makes a new query for each row, so if I have 1000 TimesheetAttributes it makes 1000 selects :/

Here is my old query:

var query = (from c in session.db.TimesheetAttributes
             where ((c.Active == true)
                    && (c.Timesheet.Active == true)
                    && (c.ValidFrom <= validDate)
                    && (c.ValidTo > validDate)
                    && (c.Timesheet.ContactPersonId == session.ContactPersonAttributes.ContactPersonId))
             select new
             {
                 Id = c.TimesheetId,
                 c.Date,
                 WeekDay = c.Date.ToString("dddd", new CultureInfo("pl-PL")),
                 Description = String.Format("{0:dd/MM/yyyy}", c.Date),
                 Hours = String.Format("{0:HH:mm}", (new DateTime((c.TimesheetRows.Aggregate(TimeSpan.Zero, (subtotal, t) => subtotal + (t.DateTo - t.DateFrom)).Ticks)))),
                 c.SickNote,
                 c.Vacation,
                 c.OccasionVacation
              }).OrderBy(c => c.Date).ToList();

EDIT:

This is what I get as exception: "Exception has been thrown by the target of an invocation" InnerException: "Object reference not set to an instance of an object"

---

The problem you were having with you're old query is often referred to as the n+1 problem.

You should be able to adapt your original query as follows:

var query = (from c in session.db.TimesheetAttributes 
             from cr in c.TimesheetRows
             where ((c.Active == true) 
                    && (c.Timesheet.Active == true) 
                    && (c.ValidFrom <= validDate) 
                    && (c.ValidTo > validDate) 
                    && (c.Timesheet.ContactPersonId == session.ContactPersonAttributes.ContactPersonId)) 
             select new 
             { 
                 Id = c.TimesheetId, 
                 c.Date, 
                 WeekDay = c.Date.ToString("dddd", new CultureInfo("pl-PL")), 
                 Description = String.Format("{0:dd/MM/yyyy}", c.Date), 
                 Hours = String.Format("{0:HH:mm}", (new DateTime((cr.Aggregate(TimeSpan.Zero, (subtotal, t) => subtotal + (t.DateTo - t.DateFrom)).Ticks)))), 
                 c.SickNote, 
                 c.Vacation, 
                 c.OccasionVacation 
              }).OrderBy(c => c.Date).ToList(); 

If that still whinges, you can try adding a tenary operator to the Hours property...

Hours = String.Format("{0:HH:mm}", !(cr == null) && cr.Any() ? (new DateTime((cr.Aggregate(TimeSpan.Zero, (subtotal, t) => subtotal + (t.DateTo - t.DateFrom)).Ticks))) : DateTime.MinValue

EDIT:

I see what you mean now, my brain hasn't quite woken up yet. The problem you have is that the underlying query provider isn't noticing that it needs to fetch TimesheetAttributes AND the associated/related TimesheetRows. This is probably because the TimesheetRows are buried quite deeply within the expression that generates the anonymous types you're selecting.

My query syntax isn't brilliant (I prefer to do it in lambda - I feel it's more future proof but that's just a feeling), but I'll give it a shot...

var query = (from res in 
                 (from c in session.db.TimesheetAttributes           
                 where ((c.Active == true)           
                        && (c.Timesheet.Active == true)           
                        && (c.ValidFrom <= validDate)           
                        && (c.ValidTo > validDate)           
                        && (c.Timesheet.ContactPersonId == session.ContactPersonAttributes.ContactPersonId))           
                 select new           
                 {   
                     c = c,
                     cr = c.TimesheetRows
                 })
             select new
             {
                 Id = c.TimesheetId,           
                 c.Date,           
                 WeekDay = c.Date.ToString("dddd", new CultureInfo("pl-PL")),           
                 Description = String.Format("{0:dd/MM/yyyy}", c.Date),           
                 Hours = String.Format("{0:HH:mm}", (new DateTime((cr.Aggregate(TimeSpan.Zero, (subtotal, t) => subtotal + (t.DateTo - t.DateFrom)).Ticks)))),           
                 c.SickNote,           
                 c.Vacation,           
                 c.OccasionVacation           
              }).OrderBy(c => c.Date).ToList();  

It looks uglier than the original, and I'm sure there's a better way of doing it, but it should make the expression clear enough for your query provider (L2S or whatever) to realise it needs to return both entities, and avoid the n+1 issue.