LINQ to SQL: How to write a 'Like' select?

Question

I've got the following SQL:

select * from transaction_log where stoptime like '%2008%'

How do I write this in LINQ to SQL syntax?

Answer 1

If you want to use the literal method, it's like this:

var query = from l in transaction_log
            where SqlMethods.Like(l.stoptime, "%2008%")
            select l;

Another option is:

var query = from l in transaction_log
        where l.stoptime.Contains("2008")
        select l;

If it's a DateTime:

var query = from l in transaction_log
        where l.stoptime.Year = 2008
        select l;

That method is in the System.Data.Linq.SqlClient namespace

Answer 2

from x in context.Table where x.Contains("2008") select x

Answer 3

If stoptime data type is string, you can use .Contains() function, and also .StartsWith() and .EndsWith().

Answer 4

If you use the contains to method then you are doing a LIKE '%somestring%'. If you use a startswith method then it is the same as 'somestring%'. Finally, endswith is the same as using '%somestring'.

To summarize, contains will find any pattern in the string but startswith and endswith will help you find matches at the beginning and end of the word.

Answer 5

The really interesting point is, that .NET creates queries like "Select * from table where name like '%test%'" when you use "from x in context.Table where x.Contains("test") select x" which is quite impressing

Answer 6

Thanks--good answers.

This is, in fact, a DateTime type; I had to typecast "stoptime" as:

var query = from p in dbTransSummary.Transaction_Logs
    where ( (DateTime) p.StopTime).Year == dtRollUpDate.Year
    select

Minor point. It works great!