Apr

5

LINQ – SQL Wildcard Equivalents

Posted by Leave a Comment

LINQ has several ways to mimic SQL wildcards in both query and method syntax. Below are a few examples of each:


First we’ll use an Employee table to run our statements on:

Employeeid Lastname Firstname Birthdate
1 Davolio Nancy 12/8/1948 12:00:00 AM
2 Fuller Andrew 2/19/1952 12:00:00 AM
3 Leverling Janet 8/30/1963 12:00:00 AM
4 Peacock Margaret 9/19/1937 12:00:00 AM
5 Buchanan Robert 3/4/1955 12:00:00 AM
6 Suyama Michael 7/2/1963 12:00:00 AM
7 King Robert 5/29/1960 12:00:00 AM
8 Callahan Laura 1/9/1958 12:00:00 AM
9 Dodsworth Anne 1/27/1966 12:00:00 AM

Method Syntax

Linq Method SQL Wildcard Equivalent
StartsWith(“R”) R%
EndsWith(“t”) %t
Contains(“et”) %et%

StartsWith()

Employee.Where(e => e.Firstname.StartsWith("R"))

Result:

Employeeid Lastname Firstname Birthdate
5 Buchanan Robert 3/4/1955 12:00:00 AM
7 King Robert 5/29/1960 12:00:00 AM

EndsWith()

Employee.Where(e => e.Firstname.EndsWith("t"))

Result:

Employeeid Lastname Firstname Birthdate
3 Leverling Janet 8/30/1963 12:00:00 AM
4 Peacock Margaret 9/19/1937 12:00:00 AM
5 Buchanan Robert 3/4/1955 12:00:00 AM
7 King Robert 5/29/1960 12:00:00 AM

Contains()

Employee.Where(e => e.Firstname.Contains("et"))

Result:

Employeeid Lastname Firstname Birthdate
3 Leverling Janet 8/30/1963 12:00:00 AM
4 Peacock Margaret 9/19/1937 12:00:00 AM

Query Syntax

Query syntax is pretty self-explanatory using SqlMethods.Like():

SqlMethods.Like()

from e in Employee 
where SqlMethods.Like(e.Firstname, "%et%") 
select e

Result:

Employeeid Lastname Firstname Birthdate
3 Leverling Janet 8/30/1963 12:00:00 AM
4 Peacock Margaret 9/19/1937 12:00:00 AM

Category: .NET, Development, Linq

Leave a Reply