Reputation: 8035

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

I've got the following SQL:

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

How do I write this in LINQ to SQL syntax?

Upvotes: 20

Answers (6)

Scott Marlowe

Reputation: 8035

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!

Upvotes: 0

MADMap

Reputation: 3192

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

Upvotes: 0

Mike Ceranski

Reputation:

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.

Upvotes: 0

Nick Craver

Reputation: 630627

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

Upvotes: 30

ChRoss

Reputation: 41

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

Upvotes: 1

user1228

Reputation:

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

Upvotes: 1

LINQ to SQL: How to write a &#39;Like&#39; select?

Answers (6)

Related Questions

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