LINQ to SQL – using Linq for SQL ‘Like’ statement

I’m really enjoying using LING to SQL, mostly because of the new .NET framework 3.5 LINQ features.

My latest find was ‘how do I make a ‘LIKE’ query using LINQ to SQL’ ?

Well, it was almost too intuitive 🙂

FSDALDataContext dc = new FSDALDataContext(connectionString);

            var pilotStats = from p in dc.CurrPilotStats
                             where p.PilotName.Contains(pilotName)
                             orderby p.Missions descending
                             select p;
            return pilotStats.ToList();
In the example above the equivalent SQL is
FROM tblCurrPilotStats WHERE (PilotName LIKE @ParamPilotName) ORDER BY Missions DESC"

If you are wanting a startswith or endswith, it’s the same syntax p.PilotName.Startswith(…)

A bit off subject, but I can approach queries with the syntax above, or use lamba:

FSDALDataContext dc = new FSDALDataContext(connectionString);
            var g = dc.MisAirSummaries.Where(air => air.MissionNum == decimal.Parse(missionID));
            return g.ToList();

Notice both statements have the ‘ToList()’ – the actual query is executed at that point.

Advertisements

6 thoughts on “LINQ to SQL – using Linq for SQL ‘Like’ statement

  1. SQL’s LIKE has more powerful capability to do search than Linq TO Sql’s Contain().
    LIKE can use wild chars: ‘%’ for any zero or more characters, ‘_’ for any single character.

    For example:

    SELECT * FROM CurrPilotStats AS p
    WHERE p.PilotName LIKE ‘Ab%de_g’

    Whereas Linq To Sql’s Contain() is unable to do wild chars search
    The variable pilotName can not have wild chars embeded.

    FSDALDataContext dc = new FSDALDataContext(connectionString);

    var pilotStats = from p in dc.CurrPilotStats
    where p.PilotName.Contains(pilotName)
    orderby p.Missions descending
    select p;
    return pilotStats.ToList();

  2. This solves your problem with wild chars.

    2. Using SqlMethods.Like method
    Digging into System.Data.Linq.SqlClient namespace, I found a little helper class called SqlMethods, which can be very usefull in such scenarios. SqlMethods has a method called Like, that can be used in a Linq to SQL query:

    var query = from c in ctx.Customers

    where SqlMethods.Like(c.City, “L_n%”)

    select c;

    This method gets the string expression to check (the customer’s city in this example) and the patterns to test against which is provided in the same way you’d write a LIKE clause in SQL.

    Using the above query generated the required SQL statement:

    SELECT CustomerID, CompanyName, …
    FROM dbo.Customers
    WHERE City LIKE [L_n%]

    I found that here http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/10/16/linq-to-sql-like-operator.aspx

    1. thanks for that, SqlMethods.Like works pretty good.
      here’s another example below for LinQ, where ModelRelationship is the table name, and Description is a column name of ModelRelationship table, and db is the LinQ DataContext instant name

      ModelRelationship[] GetModels = db.ModelRelationships.Where(p => System.Data.Linq.SqlClient.SqlMethods.Like(p.Description, string.Format(“%{0}%”, prefix))).ToArray();

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s