LINQ To SQL Grouping

Question

Hi,

Can some kind soul please lend me the Linq To SQL query for the following T-SQL Query

SELECT e.EmployeeName, MIN(a.SignInTime), MAX(a.SignOutTime)
FROM Employee e
LEFT OUTER JOIN Attendance a ON e.Id = a.EmployeeId AND CAST (CONVERT (varchar, a.SignInTime, 106) AS DATETIME) = '28 APR 2009'
GROUP BY e.EmployeeName

The database schema i have is as follows

Employee: {Id: int identity PK, EmployeeName: varchar(200) NOT NULL}
Attendance: {Id: int identity PK, EmployeeId: int FK(Employee(Id)) NOT NULL, SignInTime: DateTime, SignOutTime: DateTime}

NOTE: The Convert gimmick is only used to chop off the time portion in the SignInTime for comparision

Answer 1

This should work:

from e in db.Employee
join a in db.Attendance
      on new { e.Id, Column1 = (DateTime?)Convert.ToDateTime(Convert.ToString(a.SignInTime)) }
  equals new { Id = a.EmployeeId, Column1 = "28 APR 2009" } into a_join
from a in a_join.DefaultIfEmpty()
group new {e, a} by new {
  e.EmployeeName
} into g
select new {
  g.Key.EmployeeName,
  Column1 = g.Min(p => p.a.SignInTime),
  Column2 = g.Max(p => p.a.SignoutTime)
}

Answer 2

This is definitely a challenge. It's ugly, but It does the trick. It returns exactly what you are looking for. In the case of employees without attendance, you get back the name with null times.

var selectedDate = new DateTime(2009,4,28);
var query = from e in db.Employees
 join a in db.Attendances on e.Id equals a.EmployeeId into Temp   
 from t in Temp.DefaultIfEmpty()
    where t.SignInTime == null || (t.SignInTime >= selectedDate && t.SignInTime < selectedDate.AddDays(1))
 group t by e.EmployeeName into grouped   
 select new
 {
    Employee = grouped.Key,
    FirstSignInTime = grouped.Min(a => a.SignInTime),
    LastSignOutTime = grouped.Max(a => a.SignOutTime)
 };

This is the SQL that is emitted by that expression:

DECLARE @p0 DateTime = '2009-04-27 00:00:00.000'
DECLARE @p1 DateTime = '2009-04-28 00:00:00.000'

SELECT MIN([t1].[SignInTime]) AS [FirstSignInTime], MAX([t1].[SignOutTime]) AS [LastSignOutTime], [t0].[EmployeeName] AS [Employee]
FROM [Employee] AS [t0]
LEFT OUTER JOIN [Attendance] AS [t1] ON [t0].[Id] = [t1].[EmployeeId]
WHERE ([t1].[SignInTime] IS NULL) OR (([t1].[SignInTime] >= @p0) AND ([t1].[SignInTime] < @p1))
GROUP BY [t0].[EmployeeName]

This is very close to your original SQL. I added where "t.SignInTime == null" so that it returns employees with attendance, but you can remove that if that's not what you want.