Tuesday, September 27, 2011

Using Linq to SQL to fetch the records that non alphabetic

I came across an issue where I had to fetch all the email addresses that do not begin with an alphabet. I learnt something new and interesting today.

On the sql server side, query the database table with something like this:
select * from [yourtable] where [columnname] like '[^a-zA-Z]%'
This interestingly fetches all the desired email address that either begin with a number, or have any other special character.

How to achieve this in Linq? Well that's a little tricky. This was my initial query:
DataClassesDataContext db = new DataClassesDataContext();
     var therecords =    from dh in db.customers
                         where dh.email.StartsWith("[^a-zA-Z]%")
                         select dh)

When this query was executed, it yielded zero results. This was how sql was interpreting the LINQ to Sql query:
exec sp_executesql N'SELECT *
FROM [dbo].[customers] AS [t0]
WHERE [t0].[email] LIKE @p0 ESCAPE ''~''',N'@p0 varchar(12)',@p0='~[~^a-zA-Z]%'

I had to get rid of the Escape characters. Modifying the Linq query as below gave me the desired result:
DataClassesDataContext db = new DataClassesDataContext();
     var therecords =    from dh in db.customers
                         where System.Data.Linq.SqlClient.SqlMethods.Like(dh.email, "[^a-zA-Z]%")                      
                         select dh)

Now I know there is one more way of Linq to get close to Sql!