A better way of constructing a LINQ query

[Dylan Parry]

Hi folks,

I’ve got a LINQ query that filters out items in a database that begin
with a number. It looks like this:

char[] numbers = new char[] { '0', '1', '2', '3', '4', '5', '6', '7',
'8', '9' };
var result = (from i in Items
where numbers.Contains(i.Name[0])
orderby i.Name
select i).ToList();

Now this works, but I’m sure there must be a more efficient way of doing
this. Can anyone suggest a way that I can do it in a more efficient manner?

Cheers,

 

[David McCallum]

What about:

var result = (from i in Items
where char.IsNumber(name[0])
orderby i.Name
select i).ToList();

David McCallum

 

[Dylan Parry]

What about:

var result = (from i in Items
where char.IsNumber(name[0])
orderby i.Name
select i).ToList();

Sounded like a good idea, and passed my NUnit test on a Moq’ed database,
but then failed when it came to running it in the browser with a real
database:

“Method 'Boolean IsNumber(Char)' has no supported translation to SQL”

D’oh Thanks for trying though!

 

[Michael C]

Sounded like a good idea, and passed my NUnit test on a Moq’ed database,
but then failed when it came to running it in the browser with a real
database:

“Method 'Boolean IsNumber(Char)' has no supported translation to SQL”

D’oh Thanks for trying though!

Maybe this is one technology that I don't understand by why the heck would
you use linq-to-sql? It just seems to me that it would be more difficult to
write stuff in linq and deal with all the translation problems that would
arise. I mean, wouldn't it just be easier to write

Select * from MyTable WHERE SomeCol LIKE [0-9]%'

Michael

 

[Andy O'Neill]

What about:

var result = (from i in Items
where char.IsNumber(name[0])
orderby i.Name
select i).ToList();

Sounded like a good idea, and passed my NUnit test on a Moq’ed database,
but then failed when it came to running it in the browser with a real
database:

“Method 'Boolean IsNumber(Char)' has no supported translation to SQL”

D’oh Thanks for trying though!

You can use regex.
Matchcollection and regex.
Google on matchcollection linq for examples.

The rather weird thing to remember about linq is that it doesn't generate
your sql until you try and iterate the collection.
It's a very powerful feature but one I found completely counter-intuitive..

The end result will still be harder to understand than that one line of sql
though.
Probably not really an issue as the query isn't so hard, but you want to
think twice about hard linq.
Hard coding is bad coding.