A better way of constructing a LINQ query

  • Thread starter Thread starter Dylan Parry
  • Start date Start date
D

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,
 
What about:

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

David McCallum
 
What about:

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

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!
 
Dylan Parry said:
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!
Click to expand...

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
 
Dylan Parry said:
What about:

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

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!
Click to expand...

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.
 
Back
Top