Sunday, June 15, 2008

Using 'LIKE' in LINQ to SQL under C#

I've been investigating LINQ to SQL over recent months and I opted to do a VS 2008 course which covers many .NET 3.5 features and language enhancements etc etc

One of the questions on this course was to change some code to return items that contained the word "Blend". So a wild card statement in other words. Now in SQL this is easy. Under LINQ to SQL I originally coded:
 var query = from c in db.Coffees
where c.CoffeeName == "%Blend%"
select c;
But this didn't work. This wasn't too surprising as LINQ is not about a specific type of data access, it's about building a generic querying interface that is common across many persistent storage mediums and not just SQL Server.

I found this post where I realised that LINQ uses the standard string methods such as Contains, StartsWith etc for these purposes. So I changed my LINQ query to use the Contains method and it worked!
  var query = from c in db.Coffees
where c.CoffeeName.Contains("Blend")
select c;
After executing this query we get the following from the SQL trace:
N'SELECT [t0].[CoffeeID], [t0].[RegionID], [t0].[SupplierID], [t0].[CoffeeName], [t0].[SizeDescription], [t0].[Description], [t0].[UnitPrice], [t0].[UnitsInStock],
[t0].[ReorderLevel]
FROM [dbo].[Coffee] AS [t0]
WHERE [t0].[CoffeeName] LIKE @p0',N'@p0 nvarchar(7)',@p0=N'%Blend%'

As you can see the Contains was translated into LIKE and % to give us the match, very neat.

4 comments:

smokeyhotpot said...

its not showing up in the intellisense thing, are you sure with this?

Simon Hart said...

@smokeyhotpot:

Ensure you have a:

using System.Linq;

Declaration in your class.

Simon.

Anonymous said...

Ok, but try change the last parameter following your way...
var query = from a in context.Fabricantes
where ((fabricante.FabrFantasia == null && a.FabrFantasia == null)
|| (a.FabrFantasia == fabricante.FabrFantasia))
select a;

Anonymous said...

how to write a equivalent linq for the below sql query?

SELECT * FROM Parties WHERE PartyName >= 'Chris' AND PartyName <= 'John'