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 :)