What would this sql query (w count and group by) look like when translated to linq?

Question

How would the following sql query look when translated to linq?

SELECT
    myId, Count(myId) 
FROM MyTable 
GROUP BY myId

I've tried the following:

var q = from a in db.MyTable
group a by a.Id into g
let count = g.Count()
select new
{
  Count = Id,
  Key= g.Key
};

but it raises an exception on enumeration indicating that there is no db function with a mapping named 'Key'. I'm using LLBLGen on this particular app and I suspect that's where the problem is rooted. I want to verify that my linq syntax is correct before I start digging though. Anyone see anything wrong?

Answer 1

Try this:

var q = from a in db.MyTable
        group a by a.Id into g
        select new { Id=g.Key, Count=g.Count() };

That's nearly the same as yours, but your Count is obtained in a different way which looks wrong to me.

If LLBLGen doesn't understand IGrouping.Key though, it could be tricky...

To check whether your LINQ syntax is correct or not, I'd recommend building a very simple in-memory list to use as your table. Here's an example:

using System;
using System.Collections.Generic;
using System.Linq;

class Test
{
    static void Main()
    {
        var data = new[] {
            new { Id="X", Name="Jon" },
            new { Id="Y", Name="Marc" },
            new { Id="X", Name="Holly" },
        };

        var query = from a in data
                    group a by a.Id into g
                    select new { Id=g.Key, Count=g.Count() };

        foreach (var entry in query)
        {
            Console.WriteLine(entry);
        }
    }
}

This looks like it's giving the right results to me.