ToJson Comparison in OrmLite Linq Statement

ServiceStack
1

Trying to write an OrmLite query that will return the latest entry into a request log table where a complex type property in the DTO in the DB is identical to the one currently being submitted. I wanted to use ToJson from ServiceStack.Text to do the comparison so I would get a by value comparison of the 2 Complex Types to test for equality. Unfortunately, in doing so, I am getting an interesting error. When I remove the ToJson from the statement, it executes fine (but of course, the comparison then is a reference comparison which will never be true).

   MyDTO result = await Db
        .SingleAsync(Db.From<MyDTO>().Where(x =>
                x.ComplexType.ToJson() == newDTO.ComplexType.ToJson())
            .OrderByDescending(y => y.TimeCreated).Take(1)).ConfigureAwait(false);

and I am getting:

System.InvalidOperationException: variable ‘x’ of type ‘MyProject.MyDTO’ referenced from scope ‘’, but it is not defined

I also wrote a custom ObjectComparison method using ToJson and tried MyObjectComparer.AreEqual(x.ComplexType, newDTO.ComplexType) but that gave the same exception.

2

You can’t execute custom C# logic in a LINQ statement since it needs to be converted to SQL and executed on the server, only a small subset of methods on .NET primitive types are supported within an OrmLite LINQ expression query. You could only perform this check with LINQ on the returned resultset on the client, e.g. after .SingleAsync() is executed.

It’s also fragile trying to compare serialized blobbed fields since the order of properties returned from .NET Reflection APIs are not guaranteed to be deterministic so I wouldn’t be depending on this logic. In general blobbed fields should be considered opaque and shouldn’t try to perform RDBMS queries on them, i.e. when needed it should be extrapolated into RDBMS data types.

But if this isn’t critical that it’s 100% deterministic you could execute with custom SQL like:

.Where("ContentType = @json", new { json = newDTO.ComplexType.ToJson() })