I'm trying to translate a sql query into LINQ to SQL. I keep getting an error "sequence operators not supported for type 'system.string'" If I take out the distinct count part, it works. Is it not because I'm using the GROUP BY?
SELECT COUNT(EpaValue) AS [Leak Count], Location, EpaValue AS [Leak Desc.]
FROM ChartMes.dbo.RecourceActualEPA_Report
WHERE (EpaName = N'LEAK1'开发者_C百科) AND (Timestamp) > '20100429030000'
GROUP BY EpaValue, Location
ORDER BY Location, [Leak Count] DESC
Dim temp = (From p In db2.RecourceActualEPA_Reports _
Where (p.Timestamp >= str1stShiftStart) And (p.Timestamp < str2ndShiftCutoff) _
And (p.EpaName = "Leak1") _
Select p.EpaName.Distinct.Count(), p.Location, p.EpaValue)
p.EpaName
seems to be a string, not a collection so you can't apply Count()
there.
Here is the query you're trying to build (according to your SQL query) using LINQ (I'm not familiar with VB, so the query is written in C#):
var temp =
db2.RecourceActualEPA_Reports
.Where(p =>
p.Timestamp >= str1stShiftStart &&
p.Timestamp < str2ndShiftCutoff &&
p.EpaName == "Leak1"
).GroupBy(p => new { Key1 = p.EpaValue, Key2 = p.Location })
.Select(g => new
{
Count = g.Count(),
Value = g.Key.Key1,
Location = g.Key.Key2
}).OrderBy(i => new { i.Location, i.Count });
And please, in the future format and highlight your code using this, not (or not only) using VS/Management Studio.
Here is how it is formatted in SQL and in Visual Studio SQL
SELECT COUNT(EpaValue) AS [Leak Count], Location, EpaValue AS [Leak Desc.] FROM ChartMes.dbo.RecourceActualEPA_Report WHERE (EpaName = N'LEAK1') AND (Timestamp) > '20100429030000' GROUP BY EpaValue, Location ORDER BY Location, [Leak Count] DESC
VB
Dim temp = (From p In db2.RecourceActualEPA_Reports _ Where (p.Timestamp >= str1stShiftStart) And (p.Timestamp < str2ndShiftCutoff) _ And (p.EpaName = "Leak1") _ Select p.EpaName.Distinct.Count(), p.Location, p.EpaValue)
精彩评论