CODEWITHSUNDEEP
c#sqllinq
ChinnaR
ChinnaR

Reputation: 837

linq is taking long to order by datediff

I am trying to convert the following sql query to linq. The sql query is taking one second to run, but the linq is taking about 10 seconds to return the results. Could anyone please let me know how can I reduce the time on linq query

select datediff(mm, min(QueuedTime), max(QueuedTime)), SequencingQueue 
from tbl_AS_MessageQueue with (nolock)
group by SequencingQueue 
order by datediff(mm, min(QueuedTime), max(QueuedTime)) desc

var longRunningQueries = context.TblMessageQueues.GroupBy(x => x.SequencingQueue).Select(g => new TblMessageQueueDto
{
    DateDiff = DbFunctions.DiffMonths(g.Min(x => x.QueuedTime), 
    g.Max(x => x.QueuedTime)),
    SequencingQueue = g.Key
}).OrderByDescending(a => a.DateDiff).ToList();

Upvotes: 2

Views: 94

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726699

Rewrite your query to select DTO after ordering:

var longRunningQueries = context.TblMessageQueues
    .GroupBy(x => x.SequencingQueue)
    .Select(g => new {
        DateDiff = DbFunctions.DiffMonths(g.Min(x => x.QueuedTime), g.Max(x => x.QueuedTime)),
        SequencingQueue = g.Key
    }).OrderByDescending(a => a.DateDiff)
    .Select(t => new TblMessageQueueDto {
        DateDiff = t.DateDiff,
        SequencingQueue = t.SequencingQueue
    }).ToList();

Upvotes: 2

Related Questions