Inquiry Lambda

11

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();
        }
    
asked by anonymous 20.03.2014 / 14:17

2 answers

8

To make the equivalent of having in SQL in , just put Where() after GroupBy() :

using (var ctx = new TracesEntity())
{
    var lambda = ctx.TraceTabelas.Where(q => q.DataBaseName == "megacorretor" || q.DataBaseName == "stats_mega")
        .GroupBy(a => new { a.TextData, a.DataBaseName })
        .Where(group => group.Count() > 1)
        .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).ToString().Substring(0,20)
                        })
        .Take(60)
        .OrderByDescending(b =>b.Total)
        .ToList();

    GridView1.DataSource = lambda;
    GridView1.DataBind();
}
    
20.03.2014 / 19:25
2

There was only one last answer to my question that was the LEFT that I solved with Substring

 TextData = b.Select(b1 => b1.TextData).ToString().Substring(0,20)
    
21.03.2014 / 15:24