Linq-to-SQL expression to get the max numeric value from a text column

Question

I have an nvarchar SQL column which contains mostly numeric values. I'm trying to come up with an L2S expression that gets me the maximum numeric value while ignoring any non-numeric values.

The SQL that does the job is:

select top 1 value from identifier where and patindex('%[^0-9]%', value) = 0 order by CAST(value AS INT) desc

What LINQ expression can I use to achieve the same thing?

Answer 1

You can do SQL Like queries using the SqlMethods in System.Data.Linq.SqlClient.

(from a in identifier
where !SqlMethods.Like(a.value, "%[^0-9]%")
select a).Max(x => Convert.ToInt64(x.value))

This Linq statement becomes this query according to LinqPad:

-- Region Parameters
DECLARE @p0 VarChar(8) = '%[^0-9]%'
-- EndRegion
SELECT MAX([t1].[value]) AS [value]
FROM (
    SELECT CONVERT(BigInt,[t0].[value]) AS [value], [t0].[value]
    FROM [Identifier] AS [t0]
    ) AS [t1]
WHERE NOT ([t1].[value] LIKE @p0)

LinqPad is a great way to play around with queries to see if you can get what you're looking for. I've found that just about the only SQL statements that don't have a good L2S equivalent are ones with the PIVOT keyword. Other than that, there's usually a way to get what you want.

If you want the whole record and not just the MAX() value, you could do the query this way:

(from a in Accounts
orderby (!SqlMethods.Like(a.AccountNumber, "%[^0-9]%") ?
                      Convert.ToInt64(a.AccountNumber) : 0)
descending
select a).Take(1)