I have the following query in T-SQL
:
SELECT TOP (60) COUNT(*) AS QTD, SUM(Duration) AS Total, AVG(Duration) AS Media, MIN(Duration) AS Menor, LEFT (TextData, 80) AS TextData
FROM Traces WITH (nolock)
WHERE (StartTime >= DATEADD(D, - 7, GETDATE())) AND (DataBaseName IN ('databaseName1', 'databaseName2'))
GROUP BY TextData, DataBaseName
HAVING (COUNT(*) > 1) ORDER BY Total DESC
I'm trying to write in Lambda, I would not like Linq
, only if it's the last case.
How to mount?
I have questions about LEFT
and HAVING
:
This is my query for now.
using (var ctx = new TracesEntity())
{
var lambda = ctx.TraceTabelas.Where(q => q.DataBaseName == "databaseName1" || q.DataBaseName == "databaseName2")
.GroupBy(a => new { a.TextData, a.DataBaseName })
.Select(b => new {
QTD = b.Count(),
Total = b.Sum(b1 => b1.Duration),
Media = b.Average(b1 => b1.Duration),
Menor = b.Min(b1 => b1.Duration),
TextData = b.Select(b1 => b1.TextData)
})
.Take(60)
.OrderByDescending(b =>b.Total)
.ToList();
GridView1.DataSource = lambda;
GridView1.DataBind();
}