CODEWITHSUNDEEP
sql-serverservicestackormlite-servicestack
scoob
scoob

Reputation: 1379

ServiceStack ormlite with sql server: how to get a where clause with wildcards

How do we get servicestack's ormlite to generate SQL that does a WHERE clause like this :

WHERE FirstName like '%joe%' OR lastname like '%joe%'

Currently if I do this :

db.Select<Person>.Where(x=> x.LastName.Contains(searchstring) || x.FirstName.Contains(searchstring));

it will generate a WHERE clause like the following without the needed wildcards:

WHERE FirstName like 'joe' OR lastname like 'joe'

Upvotes: 2

Views: 856

Answers (1)

mythz
mythz

Reputation: 143374

String Contains expressions in OrmLite works as expected as seen in this Live example on Gistlyn which uses the latest version of OrmLite:

LogManager.LogFactory = new ConsoleLogFactory();

public class Person
{
    [AutoIncrement]
    public long Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

db.CreateTable<Person>();

db.Insert(new Person { FirstName = "Joeseph", LastName = "Smith" });

var rows = db.Select<Person>(x => 
    x.FirstName.Contains("joe") || x.LastName.Contains("joe"));

Which returns the valid match as expected and logs the query executed to the console:

DEBUG: SQL: CREATE TABLE "Person" 
(
  "Id" INTEGER PRIMARY KEY AUTOINCREMENT, 
  "FirstName" VARCHAR(8000) NULL, 
  "LastName" VARCHAR(8000) NULL 
); 

DEBUG: SQL: INSERT INTO "Person" ("FirstName","LastName") VALUES (@FirstName,@LastName)
PARAMS: @FirstName=Joeseph, @LastName=Smith
DEBUG: SQL: SELECT "Id", "FirstName", "LastName" 
FROM "Person"
WHERE (upper("FirstName") like @0 OR upper("LastName") like @1)
PARAMS: @0=%JOE%, @1=%JOE%

OrmLite has been using parameterized queries for a while so you must be using an old version of OrmLite, if you upgrade to the latest v4.5.0 of OrmLite it should work as expected.

Upvotes: 3

Related Questions