Creating LINQ to SQL for counting a parameter

Question

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') 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)

Answer 1

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)

Answer 2

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.