Consulta Lambda

Asked

Viewed 1,153 times

11

I have the following consultation at 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 wouldn’t like to Linq, only if this is the last case.

How to ride? I have doubts as to the LEFT and to the HAVING:

this is my consultation 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();
        }
c# entity-framework linq lambda-expressions
  • 1

    You should order first and then give the take

  • 1

    I would strongly recommend that you keep this kind of consultation in the bank, through Stored Procedures to gain performance in the query instead of having q do the relations in the code like this.

  • lambda doesn’t have a good performance? of course many things are already stored in Process, but you can’t create everything.. until pq this is a page that runs only 1 time a day.

  • How is the result so far?

  • textData is not coming.. and other things must be wrong, for example where do I have it??? , there is as in debug I check which query he is riding on TSQL ?

  • Yes. Remove the ToList() and the Take(60). lambda will receive a query object.

Show 1 more comment

2 answers

8


To do the equivalent of having in SQL no , just put a Where() after the 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();
}

  • only to complete missed the () on Cout, after I put it worked Where(group => group.Count() > 1)

  • Ah, yes. Sorry. I wrote the solution right here and did not test. Thanks!

2

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

 TextData = b.Select(b1 => b1.TextData).ToString().Substring(0,20)

Browser other questions tagged c# entity-framework linq lambda-expressions

You are not signed in. Login or sign up in order to post.