LINQ to SQL GROUP BY HAVING is generated only as a subquery

Question

according to all samples, converting SQL to LINQ clause for HAVING, as in this example:

SELECT NAME
FROM TABLES
GROUP BY NAME
HAVING COUNT(*) > 1

: (vb.net)

from t in tables 
group t by t.NAME into g = Group 
where g.count > 1 
select g

---

BUT, the LINQ statement above translates to the following SQL:

---

SELECT [t1].[NAME] AS [Key]
FROM (
    SELECT COUNT(*) AS [value], [t0].[NAME]
    FROM [tables] AS [t0]
    WHERE [t0].[NAME] <> @p0
    GROUP BY [t0].[NAME]
    ) AS [t1]
WHERE [t1].[value] > @p1

---

I never manage to create a HAVING LINQ clause form. Let them say that the HAVING group and the subquery with WHERE are equivalent in the results, but is there a difference in performance? how about making my original SQL queries at least similar to the ones that LINQ generates base ones?

Answer 1

, SQL Server, . ( SQL Server 2008):

CREATE TABLE #TABLES ([ID] INT IDENTITY, [Name] VARCHAR(30))
INSERT INTO #TABLES VALUES('A')
INSERT INTO #TABLES VALUES('A')
INSERT INTO #TABLES VALUES('B')
INSERT INTO #TABLES VALUES('C')
INSERT INTO #TABLES VALUES('D')

SELECT NAME
FROM #TABLES
WHERE [Name] <> 'D'
GROUP BY NAME
HAVING COUNT(*) > 1

SELECT [t1].[NAME]
FROM (
    SELECT COUNT(*) AS [value], [t0].[NAME]
    FROM [#TABLES] AS [t0]
    WHERE [t0].[NAME] <> 'D'
    GROUP BY [t0].[NAME]
    ) AS [t1]
WHERE [t1].[value] > 1

DROP TABLE #TABLES

SQL Query Analyzer " ", "", :

, , , SQL SQL, LINQ.

, , , LinqToSql HAVING, HAVING . , , SQL Server , , . , , , - , SQL LinqToSql. , . SQL- , LinqToSql.