Reputation: 63
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 ?
Upvotes: 1
Views: 510
Reputation: 143389
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.
Upvotes: 1