LINQ query to retunr MAX date, where all days of that week meet a certain criteria
Not sure how best to explain this, so  bear with me...

I'm writing an application to auto generate a staff rota.

I have a table (tl_RotaDays) which is essentially a calendar which records which staff member was on call that particular day - eg.

+-----------------+----------+  
+Date..............+Login...+  
+-----------------+----------+  
+02-01-2012...+Bob......+  
+03-01-2012...+Bob......+  
+04-01-2012...+Bob......+  
+05-01-2012...+Bob......+  
+06-01-2012...+Bob......+  
  
+09-01-2012...+Bob......+  
+10-01-2012...+Bob......+  
+11-01-2012...+Bob......+   
+12-01-2012...+Sue......+   
+13-01-2012...+Sue......+  
+-----------------+----------+  

I want to return the date of the Friday in the last "full" working (Mon-Fri) week each person worked, based on a "full" week being defined as a week in which they worked >=3 days. 

As such in the above example, the query for "Bob" should return "13-01-2012", as he worked the majority of that week, even though Sue worked the end of the week.

I have a query which gets the last date each person worked:

    
    lastEveningOOH = Convert.ToDateTime((from rd in db.tl_rotaDays
                                    where rd.ooh == tc.login && ((int)Convert.ToDateTime(rd.date).DayOfWeek >= 1 &&
                                                (int)Convert.ToDateTime(rd.date).DayOfWeek <= 5)
                                    select rd.date).Max()),

But the only way I can think to check if the person worked a "full" week is to query the original table against each line, which seems frightfully inefficient.


LINQ query to retunr MAX date, where all days of that week meet a certain criteria
===

Not sure how best to explain this, so  bear with me...

I'm writing an application to auto generate a staff rota.

I have a table (tl_RotaDays) which is essentially a calendar which records which staff member was on call that particular day - eg.

+-----------------+----------+  
+Date..............+Login...+  
+-----------------+----------+  
+02-01-2012...+Bob......+  
+03-01-2012...+Bob......+  
+04-01-2012...+Bob......+  
+05-01-2012...+Bob......+  
+06-01-2012...+Bob......+  
  
+09-01-2012...+Bob......+  
+10-01-2012...+Bob......+  
+11-01-2012...+Bob......+   
+12-01-2012...+Sue......+   
+13-01-2012...+Sue......+  
+-----------------+----------+  

I want to return the date of the Friday in the last "full" working (Mon-Fri) week each person worked, based on a "full" week being defined as a week in which they worked >=3 days. 

As such in the above example, the query for "Bob" should return "13-01-2012", as he worked the majority of that week, even though Sue worked the end of the week.

I have a query which gets the last date each person worked:

    
    lastEveningOOH = Convert.ToDateTime((from rd in db.tl_rotaDays
                                    where rd.ooh == tc.login && ((int)Convert.ToDateTime(rd.date).DayOfWeek >= 1 &&
                                                (int)Convert.ToDateTime(rd.date).DayOfWeek <= 5)
                                    select rd.date).Max()),

But the only way I can think to check if the person worked a "full" week is to query the original table against each line, which seems frightfully inefficient.

