Use having without group by

Question

Hello,

Is there any way to create a linq query that uses having but does not use group by? I haven't been able to find any examples on this.

Right now this is the code I have (which works):

double? averageBottom = (from p in db.Scores
                                     where p.Scale.Code == scaleCode && p.BottomLevelID == bottomLevel.ID
                                     select (double?)p.Value).Average();

This is the query generated by Linq:

SELECT AVG([t2].[value]) AS [value]
FROM (
    SELECT [t0].[fValue] AS [value], [t1].[fCode], [t0].[fBottomLevelID]
    FROM [dbo].[tbScore] AS [t0]
    INNER JOIN [dbo].[tbScale] AS [t1] ON [t1].[fScaleID] = [t0].[fScaleID]
    ) AS [t2]
WHERE ([t2].[fCode] = @p0) AND ([t2].[fBottomLevelID] = @p1)

But I also want to check for a minimum of records, how do I add the equivalent of "HAVING COUNT(*) > 10" to the Linq statement?

Thanks,
Patrick

Answer 1

Hi Trax72,

In SQL expression, 'HAVING' clause is used to filter records because the 'WHERE' keyword could not be used with aggregate functions. However, in LINQ, there's no such restrictions. That is to say, you can use 'where' to achieve the same results. For example, if you want to translate the following SQL:

SELECT AVG(VALUE) FROM SCORES GROUP BY NO HAVING COUNT(*)>10

you can write like this:

       var results = from s in Scores
                          group s by s.No
                              into cc
                              where cc.Count() > 10
                              select cc.Average(c => c.Value);

Feel free to ask if you have any further question.

---

Best regards,
Charlie Lee

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hello,

That wasn't quite the solution I was looking for, mainly because I don't want to group at all. If I do use grouping I won't get a single value but a list of values (even if there's only one). But I worked around it, by grouping on a constant and using SingleOrDefault() to get the value I need out of the result:

            double? averageTop2 = (from p in db.Scores
                                   where p.Scale.Code == scaleCode && p.BottomLevel.MidLevel.MonitorID == bottomLevel.MidLevel.MonitorID
                                   group p by 1 into pg
                                   where pg.Count() > minimum
                                   select pg.Average(a => (double?)a.Value)).SingleOrDefault();

The resulting sql query looks quite awkward:

SELECT [t6].[value2]
FROM (
    SELECT COUNT(*) AS [value], AVG([t5].[value]) AS [value2]
    FROM (
        SELECT [t4].[fValue] AS [value], [t4].[fCode], [t4].[fMonitorID], [t4].[value] AS [value2]
        FROM (
            SELECT @p0 AS [value], [t0].[fValue], [t1].[fCode], [t3].[fMonitorID]
            FROM [dbo].[tbScore] AS [t0]
            INNER JOIN [dbo].[tbScale] AS [t1] ON [t1].[fScaleID] = [t0].[fScaleID]
            INNER JOIN [dbo].[tbBottomLevel] AS [t2] ON [t2].[fBottomLevelID] = [t0].[fBottomLevelID]
            INNER JOIN [dbo].[tbMidLevel] AS [t3] ON [t3].[fMidLevelID] = [t2].[fMidLevelID]
            ) AS [t4]
        ) AS [t5]
    WHERE ([t5].[fCode] = @p1) AND ([t5].[fMonitorID] = @p2)
    GROUP BY [t5].[value2]
    ) AS [t6]
WHERE [t6].[value] > @p3

But according to the estimated execution plan it does get optimized to run as fast as the simpler query with HAVING COUNT(*) > x.

Regards,
Patrick

Answer 3

Hi Trax72,

You can try this to see if the SQL statement translated from the following LINQ expression seems elegant to you.

            var averageTop2 = from p in db.Scores
                                   where p.Scale.Code == scaleCode && p.BottomLevel.MidLevel.MonitorID == bottomLevel.MidLevel.MonitorID
                                   group p by new {Criterion =db.Scores.Count() > minimum}
                                   into pg
                                   select new {Criterion = pg.Key, average = pg.Avereage(a => a.Value);

---

Best regards,
Charlie Lee

Please remember to mark the replies as answers if they help and unmark them if they provide no help.