Checking for numeric strings in LINQ to SQL

[Richard Everett]

I have a table in SQL server containing records that are uniquely
identified using a three character alphanumeric code.

Codes are either all letters (eg ABC) or all numeric (eg 123)

I need to obtain only the records whose three character code is all
letters.

In SQL I can achieve this using:

SELECT

-- and other fields
FROM [dbo].[TableName]
where [Code] > '999'

How can I do this using LINQ to SQL?

I've already tried writing a custom C# function that checks to see if
the code can be parsed as an integer, but that won't translater to SQL
(which is understandable).

 

[Jeroen Mostert]

I have a table in SQL server containing records that are uniquely
identified using a three character alphanumeric code.

Codes are either all letters (eg ABC) or all numeric (eg 123)

I need to obtain only the records whose three character code is all
letters.

In SQL I can achieve this using:

SELECT

-- and other fields
FROM [dbo].[TableName]
where [Code] > '999'
[/QUOTE]
This is a hack; knowing that the letters will sort lexically after the
numbers relies on the collation. Are you deliberately avoiding LIKE because
you want to take advantage of a range index? That is, a much less cryptic
query would use WHERE [Code] LIKE '[A-Z][A-Z][A-Z]' (three letters) or WHERE
[Code] NOT LIKE '[0-9][0-9][0-9]' (anything that's not three digits).

In fact, if you *know* codes are either all-numbers or all-letters, you can
express this in the table itself:

ALTER TABLE Codes ADD
IsNumericCode AS CAST(CASE WHEN [Code] LIKE '[0-9][0-9][0-9]' THEN 1 ELSE 0
END AS BIT),
IsLetterCode AS CAST(CASE WHEN [Code] LIKE '[A-Z][A-Z][A-Z]' THEN 1 ELSE 0
END AS BIT);

Now you can search on these computed fields to your heart's content.
[QUOTE]
How can I do this using LINQ to SQL?
[/QUOTE]
Well, actually, why can't you do it just as in SQL?

from code in Codes
where code > "999"
select code;

This should translate to the statement above.

I know of no way to write it in such a way that the appropriate LIKE query
is generated; patterned LIKE to my knowledge has no LINQ to SQL pendant.
[QUOTE]
I've already tried writing a custom C# function that checks to see if
the code can be parsed as an integer, but that won't translater to SQL
(which is understandable).[/QUOTE]

To avoid *that*, you can force query evaluation (and switch to LINQ to Objects):

from code in Codes.ToArray()
where myComplicatedCondition(code)
select code;

Obviously, this will retrieve all records in the table first, so if you're
just looking for a few records this is horribly inefficient.

 

[Richard Everett]

Thanks J.

The number of records that my "IsNumeric" C# condition will exclude is
small (I am typically pulling out ~15 records, and then excluding one
or two), so I am happy to take the efficiency hit using the approach
you suggested.

thanks for the post.

Richard