Much of the time, it's fairly straightforward to express an SQL query in Linq instead. However, there are some things that aren't quite so clear how to do. In this article, I'll take a look at some of those.

LIKE

There are a couple of ways to replace LIKE. If you just want to directly use LIKE, then you can do so using the SqlMethods class. To use this, you must add the following using declaration at the top of the file:

using System.Data.Linq.SqlClient;

Then we can use it like this:

var Result = from Book in DB.BookResources
             where SqlMethods.Like(Book.Title, "%java%")
             select Book;

This will create an SQL query that locates all books containing "java" (case-insensitively). Interestingly, you can also do the following:

var Result = from Book in DB.BookResources
             where Book.Title.Contains("java")
             select Book;

And it is equivalent. This is useful and results in the same SQL, but also a tad confusing: you get the case sensitivity (or default lack thereof) configured in the database, even though normally in C# code outside of a Linq query this method is case sensitive.

You can also use the .StartsWith and .EndsWith methods of a string, which can replace some LIKE queries a little more readably. They have the same case quirks, though (under the hood, they all compile down to a LIKE query).

IN

Our example table has a rating field that allows us to store a book's rating out of ten. We want to get those books scoring between 7 and 10 points (or some other arbitrary List of scores). We can use the List's Contains method inside the Linq query, which will cause it to generate an IN query in the SQL.

var GoodRatings = new List<short>() { 7, 8, 9, 10 };
var Result = from Book in DB.BookResources
             where GoodRatings.Contains(Book.Rating)
             select Book;

Nice and easy.

DATEDIFF

The SqlMethods class also contains various methods to allow us to express the SQL DATEDIFF built-in. In the following example, we select all books that were added to the database in the last 30 days.

var Result = from Book in DB.BookResources
             where SqlMethods.DateDiffDay(Book.EntryDate, DateTime.Now) <= 30
             select Book;

There are a variety of related methods available, including DateDiffYear, DateDiffHour and so forth, depending what unit of time you want to get the result back in. These compile to an SQL DATEDIFF query.

Note that methods on TimeSpan objects are not translated by Linq, therefore you can not write:

var Result = from Book in DB.BookResources
             where DateTime.Now.Subtract(Book.EntryDate.Value).Days < 30
             select Book;

Trying to do so will give you a NotSupportedException at runtime (unfortunately meaning that you won't find out that this won't work at compile time).

If All Else Fails

If you are really stuck trying to express a tricky SQL query in Linq, but using Linq generally, then you can still use the DataContext's ExecuteQuery method to send a query directly to the database in SQL.

var Result = DB.ExecuteQuery<BookResource>(
    "SELECT * FROM [BookResources] WHERE Title LIKE '%java%'");

We use a type parameter to specify the type of object that will contain the result of our query, then just specify the query to execute as a string. Note that we could have expressed this one in Linq, it's just an example.

If you want to just get one field, write the type of that field.

var Result = DB.ExecuteQuery<string>(
    "SELECT [Title] FROM [BookResources] WHERE Title LIKE '%java%'");

If you're getting a few fields back, then you will probably need to create a class with properties that have names matching those of the fields that you use in the query. However, there is another variant of ExecuteQuery that takes a Type object as a first parameter, so you can most probably instantiate an anonymous type using new() { ... } with some dummy values to get the member types correct, then call GetType and pass that as the parameter. I'll leave testing this beautiful bit of evil as an exercise for the reader.

Final Thoughts

Linq works to make working with databases feel close to working with other objects in your program. Thus IN feels very natural and, while they provide a direct way to use LIKE, some methods of the String class are usable too. However, in some cases you have to fall back to using some of the methods in the SqlMethods class, and in an extreme few further cases you might need to drop all the way back to using SQL.

Post Comment Return To Blog