Sql in ormlite servicestack

Question

I use ormlite with servicestack and I have got this problem. I have saved a list of string in a column of my db so I want to do a select sql like this: Select top 1 * From MyTable Where MyVariable In (MyListSavedInDb)

var orders = db.Select<Order>(o => Sql.In(o.Ldv, o.Waybills));

Where o.Ldv is a string and o.Waybills is a list of string saved on db

Any solutions ?

mythz mythz · Accepted Answer · 2021-03-04T16:43:52

You can't query a blobbed field with server-side SQL, best you can do is a fuzzy string index search like:

var q = db.From<Order>();
q.Where($"CHARINDEX({q.Column<Order>(x=>x.Ldv)},{q.Column<Order>(x=>x.Waybills)}) > 0")
 .Take(1);
var order = db.Single(q);

But essentially you shouldn't be blobbing any fields that you want to perform server-side SQL queries on.

A more typed and robust approach would be to perform the final query on a blobbed collection on the client after you've filtered the resultset, e.g:

var orders = db.Select(q);
var order = orders.FirstOrDefault(x => x.Waybills.Contains(term));

But as this query is done on the client you'll want to ensure it's being done on a limited filtered resultset.

Sql in ormlite servicestack

1 Answers