Random thoughts of wisdom - the personal blog of Michael Holm Andersen

LINQ - SqlMethods.Like method

Using LINQ we can generate SQL which uses the LIKE behaviour using .Contains, like this:

var qMails = from m in db.Mails
             where m.Subject.Contains("M")
             select new { m.MailID, m.ToEmail, m.Subject };

However in some situations the auto-generated SQL created by LINQ will not perform very good. E.g. the query:

var qMails = from m in db.Mails
             where m.Subject.StartsWith("M") && m.Subject.Contains("o")
             select m;

..will cause the generated SQL to include:

WHERE  Subject LIKE [M%]
AND    Subject LIKE [%o%] 

And that's not exactly what we want. So in order to force LINQ to generate proper SQL we can simply use the SqlMethods (located in the System.Data.Linq.SqlClient namespace) helper like this:

var qMails = from m in db.Mails
             where SqlMethods.Like(m.Subject, "M_o%")
             select m;

Now, the generated SQL will contain: WHERE Subject LIKE [M_o%] .. which is exactly what we want :)


Need a piece of software and you know exactly which one you would like, but cannot afford it or just don't to spend money!? - Then you need to know about which takes an alternative (sorry, could'nt help it!) approach to finding software in the form: "Enter the application you want to replace and a list of suggestions on great alternatives is shown". (Thanks to René for sharing this!)