ServiceStack OrmLite wrong SQL?

Question

I have the following class:

    public class ProcessInstance
    {
        [AutoIncrement]
        public int Id { get; set; }

        [Reference]
        public ProcessDefinition ProcessDefinition { get; set; }
        public int ProcessDefinitionId { get; set; }

        // and more...
}

Then running the following, which looks fine to me:

var q = db.From<ProcessInstance>().Where(inst => inst.ProcessDefinition.Id == id
                && Sql.In(inst.Status, enProcessStatus.READY, enProcessStatus.ACTIVE));
return db.Exists(q);

When I inspect the last command text SQL from the "db" object, it's wrong:

SELECT 'exists' 
FROM "ProcessInstance"
WHERE (("Id" = @0) AND "Status" IN (@1,@2))
LIMIT 1

Note that it's filtering on Id instead of ProcessDefinition.Id, which of course is wrong. Don't know why it's doing that -- at least I'd appreciate getting an error instead of just a wrong result.

However, I've found how to fix it: Use ProcessDefinitionId: Where(inst => inst.ProcessDefinitionId == id gives the correct SLQ:

SELECT 'exists' 
FROM "ProcessInstance"
WHERE (("ProcessDefinitionId" = @0) AND "Status" IN (@1,@2))
LIMIT 1

Why didn't the first one work? Why is there no error?

mythz mythz · Accepted Answer · 2021-09-06T17:46:31

OrmLite is designed for providing a typed api around an SQL Expression so that it should be intuitive to determine the SQL generated from a typed Expression. It doesn’t support magic behavior such as querying any nested objects as attempted with the reference complex type property, I.e. you can only query direct column properties as done in your 2nd query.

ServiceStack OrmLite wrong SQL?

1 Answers