hat is the LINQ equivalent for the following SQL query?

Question

T-SQL query:

 SELECT  T1.ID, 
        T1.UserId,
        T1.ServerId,
        T1.DiskId, 
        T1.Date_ PreviousDate_, 
        T1.Row,
        MIN(T2.Date_) AS Date_, 
        DATEDIFF(MINUTE, T1.Date_, MIN(T2.Date_)) AS MinutesDiff
FROM    IesLogs T1
        LEFT JOIN IesLogs T2
            ON T1.DiskId = T2.DiskId
            where T1.DiskId = 2 AND T2.Date_ > T1.Date_ AND T1.Row = T2.Row
GROUP BY T1.ID,T1.UserId, T1.ServerId, T1.DiskId, T1.Date_, T1.[Row]
ORDER BY T1.DiskId, T1.[Row], T1.Id

I am getting more data than I expected.

  var result = (
                    from i in context.IesLogs
                    join i2 in context.IesLogs 
                        on i.DiskId equals diskId into i2left
                    from i3 in i2left.DefaultIfEmpty()
                    where 
                        i.UserId == userId
                        && i3.Date > i.Date
                        && i.Row == i3.Row
                    group i3 by new {i.Id, i.ServerId,i.DiskId, i.Row, PreviousDate = i.Date, i3.Date} into logs
                    orderby logs.Key.DiskId, logs.Key.Row,logs.Key.Id  ascending
                    select new IesLogStatisticsDto
                    {
                        Id = logs.Key.Id,
                        ServerId = logs.Key.ServerId,
                        DiskId = logs.Key.DiskId,
                        PreviousDate = logs.Key.PreviousDate,
                        Date = logs.Min(x => x.Date),
                        Row = logs.Key.Row,
                        DateDiff = Convert.ToInt32((logs.Min(x => x.Date) - logs.Key.PreviousDate).TotalMinutes)
                    }).ToList();

I'm getting 12 when I should be getting 6 data. How Can I solve this ?

I think the reason why I'm getting a lot of data is the(i3.Date > i.Date) in the code blog above but I have to implement this line.

Answer 1

Your LINQ query has wrong grouping. You have to remove i3.Date from grouping key.

A little bit refactored:

var query =
    from i in context.IesLogs
    join i2 in context.IesLogs 
        on i.DiskId equals diskId into i2left
    from i2 in i2left.DefaultIfEmpty()
    where 
        i.UserId == userId
        && i2.Date > i.Date
        && i.Row == i2.Row
    group i2 by new {i.Id, i.ServerId, i.DiskId, i.Row, PreviousDate = i.Date} into logs
    orderby logs.Key.DiskId, logs.Key.Row, logs.Key.Id  ascending
    select new IesLogStatisticsDto
    {
        Id = logs.Key.Id,
        ServerId = logs.Key.ServerId,
        DiskId = logs.Key.DiskId,
        PreviousDate = logs.Key.PreviousDate,
        Date = logs.Min(x => x.Date),
        Row = logs.Key.Row,
        DateDiff = Convert.ToInt32((logs.Min(x => x.Date) - logs.Key.PreviousDate).TotalMinutes)
    };