LINQ a SQL utilizzando GROUP BY e COUNT (DISTINCT)

Devo eseguire la seguente query SQL:

select answer_nbr, count(distinct user_nbr) from tpoll_answer where poll_nbr = 16 group by answer_nbr 

La query LINQ su SQL

 from a in tpoll_answer where a.poll_nbr = 16 select a.answer_nbr, a.user_nbr distinct 

esegue il mapping alla seguente query SQL:

 select distinct answer_nbr, distinct user_nbr from tpoll_answer where poll_nbr = 16 

Fin qui tutto bene. Tuttavia, il problema sorge quando provo a GROUP i risultati, dato che non riesco a trovare una query LINQ to SQL che mappa la prima query che ho scritto qui (grazie a LINQPad per rendere questo processo molto più semplice). Quello che segue è l’unico che ho trovato che mi dia il risultato desiderato:

 from answer in tpoll_answer where answer.poll_nbr = 16 _ group by a_id = answer.answer_nbr into votes = count(answer.user_nbr) 

Che a sua volta produce follwing brutto e non ottimizzato a tutte le query SQL:

 SELECT [t1].[answer_nbr] AS [a_id], ( SELECT COUNT(*) FROM ( SELECT CONVERT(Bit,[t2].[user_nbr]) AS [value], [t2].[answer_nbr], [t2].[poll_nbr] FROM [TPOLL_ANSWER] AS [t2] ) AS [t3] WHERE ([t3].[value] = 1) AND ([t1].[answer_nbr] = [t3].[answer_nbr]) AND ([t3].[poll_nbr] = @p0) ) AS [votes] FROM ( SELECT [t0].[answer_nbr] FROM [TPOLL_ANSWER] AS [t0] WHERE [t0].[poll_nbr] = @p0 GROUP BY [t0].[answer_nbr] ) AS [t1] -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [16] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 

Qualsiasi aiuto sarà più che apprezzato.

Non esiste un supporto diretto per COUNT(DISTINCT {x})) , ma è ansible simularlo da un IGrouping<,> (ovvero quale group by resi); Ho paura di “fare” solo C #, quindi dovrai tradurre in VB …

  select new { Foo= grp.Key, Bar= grp.Select(x => x.SomeField).Distinct().Count() }; 

Ecco un esempio Northwind:

  using(var ctx = new DataClasses1DataContext()) { ctx.Log = Console.Out; // log TSQL to console var qry = from cust in ctx.Customers where cust.CustomerID != "" group cust by cust.Country into grp select new { Country = grp.Key, Count = grp.Select(x => x.City).Distinct().Count() }; foreach(var row in qry.OrderBy(x=>x.Country)) { Console.WriteLine("{0}: {1}", row.Country, row.Count); } } 

Il TSQL non è esattamente quello che vorremmo, ma fa il lavoro:

 SELECT [t1].[Country], ( SELECT COUNT(*) FROM ( SELECT DISTINCT [t2].[City] FROM [dbo].[Customers] AS [t2] WHERE ((([t1].[Country] IS NULL) AND ([t2].[Country] IS NULL)) OR (([t1] .[Country] IS NOT NULL) AND ([t2].[Country] IS NOT NULL) AND ([t1].[Country] = [ t2].[Country]))) AND ([t2].[CustomerID] <> @p0) ) AS [t3] ) AS [Count] FROM ( SELECT [t0].[Country] FROM [dbo].[Customers] AS [t0] WHERE [t0].[CustomerID] <> @p0 GROUP BY [t0].[Country] ) AS [t1] -- @p0: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 

I risultati, tuttavia, sono verificabili correttamente eseguendoli manualmente:

  const string sql = @" SELECT c.Country, COUNT(DISTINCT c.City) AS [Count] FROM Customers c WHERE c.CustomerID != '' GROUP BY c.Country ORDER BY c.Country"; var qry2 = ctx.ExecuteQuery(sql); foreach(var row in qry2) { Console.WriteLine("{0}: {1}", row.Country, row.Count); } 

Con la definizione:

 class QueryResult { public string Country { get; set; } public int Count { get; set; } } 

L’esempio Northwind citato da Marc Gravell può essere riscritto con la colonna City selezionata direttamente dall’istruzione di gruppo:

 from cust in ctx.Customers where cust.CustomerID != "" group cust.City /*here*/ by cust.Country into grp select new { Country = grp.Key, Count = grp.Distinct().Count() }; 

Da Linq a sql non ha supporto per Count (Distinct …). È quindi necessario mappare un metodo .NET in codice su una funzione server Sql (quindi Count (distinto ..)) e utilizzarlo.

btw, non aiuta se si inserisce codice pseudo copiato da un toolkit in un formato che non è né VB.NET né C #.

Ecco come si fa una query di conteggio distinta. Si noti che è necessario filtrare i valori nulli.

 var useranswercount = (from a in tpoll_answer where user_nbr != null && answer_nbr != null select user_nbr).Distinct().Count(); 

Se combini questo con il tuo attuale codice di raggruppamento, penso che avrai la soluzione.

esempio semplice e pulito di come funziona il gruppo in LINQ

http://www.a2zmenu.com/LINQ/LINQ-to-SQL-Group-By-Operator.aspx

Non mi preoccuperei di farlo in Linq2SQL. Creare una stored procedure per la query che si desidera e comprendere e quindi creare l’object per la stored procedure nel framework o semplicemente collegarsi direttamente ad esso.