Any help with a Ormlite Linq expression error in select (mysql db)?
In OrmLite 3 I could do this expression:
conn.Select<Contact>(C => C.Roles.Contains(ContactRoles.owner))
In Ormlite 4 this code gives the error:
variable ‘C’ of type ‘pM.Models.Entity.Contact’ referenced from scope ‘’, but it is not defined
I can’t repro this, can you add a stand-alone repro on https://github.com/ServiceStack/Issues so I can run + test locally?
Aaron Langley:
Will do soon. Work around was to change i to conn.Select<Contact>().Where(C => C.Roles.Contains(ContactRoles.owner)). Still trying to get the general upgrade to 4 working, one last issue hosting.
Aaron Langley:
Made a fork of EmailContacts that reproduces the issue. This is the commit.
https://github.com/AaronLangley/EmailContacts/commit/a31822265975824da647a4a9b5629e03632c25dd
Yeah that’s never going to work. Storing a collection of enums is a complex type that ends up being blobbed, e.g: “[glam,funk]” and you can’t perform any native SQL queries like “IN” on blobbed data.
The collection works the other way around where if you can query with IN with a collection of enums against a single enum field, see quick test I just added:
https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/src/ServiceStack.OrmLite.MySql.Tests/Issues/SelectExpressionIssues.cs
An option for storing multiple enums is to use an [Flags] Enum which can store multiple enum values in a single int field. You can then use bit-wise operations on the server to query if it’s in the collection. Unfortunately there’s no support for bitwise enums in the LINQ expressions yet, but you can use Custom bitwise SQL with SelectFmt to do this, e.g:
db.SelectFmt<Contact>(“Flags & {0} = {0}”, (int)AvailableFlags.glam)
Aaron Langley:
I guess with mysql a regex on the raw json string is possible too. Strange that it was working before in v3.
Do you have the SQL v3 generates?
Aaron Langley:
yes from the profiler (which is awesome):
SELECT Id , CustomerId , Reference , Email , SpecialType , Roles , Persons , AccountDetails , Notes , NameText , PostalAddressText , PhysicalAddressText , HomePhone , WorkPhone , CellPhone , ABN , TradeName , Website
FROM Contact
WHERE (upper(Roles) like ‘%OWNER%’ AND (CustomerId = ‘7fc4d2df93c04287b44ba2eb00af98ed’))
ok looks like it’s doing a string contains instead of an IN array contains.
Is Roles an Enum Collection?
Aaron Langley:
Yes. So it should be safe not to escape it etc.