Select [with added functions] group by in Lambda

Question

Select [with added functions] group by in Lambda

Navigation

#1 by (2 votes)

3

How do I convert the following select

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 ('database_1', 'database_2'))
GROUP BY TextData, DataBaseName
HAVING      (COUNT(*) > 1)
ORDER BY Total DESC

I'm trying this way:

var lambda = ctx.Traces.Where(q => q.DataBaseName == "database_1" || q.DataBaseName == "database_2")
         .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(b2 => b2.TextData) //aqui o problema
                          })
          .OrderByDescending(b => b.Total)
          .Take(10)
          .ToList();

however I can not retrieve TextData

it returns something like:

System.Collections.Generic.List'1[System.String]

the other fields are ok.

c# entity-framework

asked by anonymous 20.06.2014 / 21:23

1 answer

Sort results by a relationship column in Laravel 4 Starting GIT by Terminal

score 2 · Accepted Answer

Put like this:

var lambda = ctx.Traces
         .Where(q => q.DataBaseName == "database_1" || q.DataBaseName == "database_2")
         .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 = EntityFunctions.Left(b.Key.TextData, 80)})
          .OrderByDescending(b => b.Total)
          .Take(10)
          .ToList();

or

var lambda = ctx.Traces
         .Where(q => q.DataBaseName == "database_1" || q.DataBaseName == "database_2")
         .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.Key.TextData.Substring(0,80)})
          .OrderByDescending(b => b.Total)
          .Take(10)
          .ToList();

Key = (a => new { a.TextData, a.DataBaseName }) , that is, it is the grouping that you set up to be b.Key.TextData and b.Key.DataBaseName