What is the Linq equivalent to query my list which I query with Tsql?

i need count of first character of values in my list.  note: first 3 values start numeric char designate '1' (if isnumeric == true '1'), count of values start 'a', 'b', ...

here list of values (in table form tsql) and tsql use counts.  how query this list with linq query same result set tsql query? 

fld1
123
234abc
345
aaa
abb
abc
abd
baa
bbb
bcd
bee
bfg
bhf
bmd
caa
cba
ccb
cmh
daa
dba
dcb

select case  when isnumeric(substring(fld1, 1,1))  = 1 '1' else substring(fld1,1,1) end val, count(*) cnt mytbl
group case  when isnumeric(substring(fld1, 1,1))  = 1 '1' else substring(fld1,1,1) end

--result set

val   cnt
1       3
a       4
b       7
c       4
d       3

thanks

---

rich p

unfortunately, won't work in ef or linq sql, conversions character/etc won't available expressions can translated.  you're better off leaving in tsql.

you in linq objects, though, if pull list memory:

var results = list.groupby(i => char.isdigit(i,0) ? '1' : i[0])                   .select(g => new { val = g.key, count = g.count() });  foreach(var result in results)     console.writeline("{0}   {1}", result.val, result.count); 

---

reed copsey, jr. - http://reedcopsey.com - if post answers question, please click mark answer on post. if find post helpful, please click vote helpful.

Visual Studio Languages  ,  .NET Framework  >  Visual C#