views:

143

answers:

1

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?

+3  A: 

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.

Jon Skeet
Haha, indeed, my way was odd. That works like a charm. Thanks Jon
TheDeeno