LINQ group by question

Question

I started playing around with Linq today and ran into a problem I couldn't find an answer to. I was querying a simple SQL Server database that had some employee records. One of the fields is the full name (cn). I thought it would be interesting to group by the first name by splitting the full name at the first space. I tried

group by person.cn.Split(separators)[0]

but ran into a lengthy runtime exception (looked a lot like a C++ template instantiation error).

Then I tried grouping by a few letters of the first name:

group by person.cn.Substring(0,5)

and that worked fine but is not what I want.

I'm wondering about two things:

• Why does the first example not work when it looks so close to the second?
• Knowing that behind the scenes it's SQL stuff going on, what's a good way to do this kind of thing efficiently

Thanks,

Andrew

Answer 1

Split has no translation into SQL.

So, how to do this string manipulation without split? Cheat like hell (untested):

string oneSpace = " ";
string fiftySpace = "                                                  ";

var query = 
  from person in db.Persons
  let lastname = person.cn.Replace(oneSpace, fiftySpace).SubString(0, 50).Trim()
  group person by lastname into g
  select new { Key = g.Key, Count = g.Count };

Answer 2

The reason your first attempt didn't work is because LINQ to SQL uses Expression Trees to translate your query into SQL. As a result any code that isn't directly translatable into SQL is an exception - this includes the call to Split.

Answer 3

Thanks guys, I'll try the "Replace" trick to see if that runs. I'm very intrigued by LINQ but now it looks like there's some hidden mysteriousness where you have to know what your LINQ queries translate into before being able to use it effectively.

The core problem is of course that I don't know SQL very well so that's where I'll start.

Edit:

I finally tried the "Replace" today and it works. I even got to sort the grouped results by count so now I have a pareto of name in my company. It's horrendously slow, though. Much faster to select everything and do the bucketing in C# directly.

Thanks again,

Andrew