OrmLite LoadSelect with nullable references

ServiceStack.OrmLite
1

Hello!

I have a model with nullable references to UserAuth. I would like to load this model with LoadSelect (Db.LoadSelect<DepartmentEntity>()) and I noticed it’s generate unnecessary SQL select statements.

This is my model.

    [Alias("Department")]
    public class DepartmentEntity
    {
        [StringLength(250)]
        public string Name { get; set; }

        public string Description { get; set; }

            [StringLength(254)]
            public string Email { get; set; }

            [References(typeof(UserAuth))]
            public int? ManagerId { get; set; }

            [Reference]
            public UserAuth Manager { get; set; }
        }

And here is the strange SQL statement if the ManagerId is null.

SELECT "Id", "UserName", "Email", "PrimaryEmail", "PhoneNumber", "FirstName", "LastName", "DisplayName", "Company", "BirthDate", "BirthDateRaw", "Address", "Address2", "City", "State", "Country", "Culture", "FullName", "Gender", "Language", "MailAddress", "Nickname", "PostalCode", "TimeZone", "Salt", "PasswordHash", "DigestHa1Hash", "Roles", "Permissions", "CreatedDate", "ModifiedDate", "InvalidLoginAttempts", "LastLoginAttempt", "LockedDate", "RecoveryToken", "RefId", "RefIdStr", "Meta" FROM "UserAuth" WHERE "Id"=NULL

Thanks,
Tom

2

Can you provide the exact OrmLite code you’re using that generates this SQL, i.e. are you using db.LoadSingleById<T> or db.LoadSelect<T> ? this doesn’t look like a query db.LoadSelect<T> would generate.

3

I’ll assume you called db.LoadSingleById<T> as it used to generate this query but won’t anymore from this commit.

This change is available from v4.5.1 that’s now available on MyGet.

Thx for reporting.

P.S. your DepartmentEntity doesn’t have a PrimaryKey defined and since there’s no Id field it falls back to using the first property which in this case is Name. If that’s what you want you should make it explicit by annotating it with the [PrimaryKey] attribute otherwise you should consider adding an auto incrementing Id field so your table has a proper Primary Key.

4

Sorry I post wrong code from my test.

This is the good one.

Db.LoadSelect<DepartmentEntity>();

And the SQL statements.

SELECT "Name", "Description", "Email", "ManagerId" FROM "Department"

I have only one Department record in the table and the ManagerId is null. I don’t understand why executing this second SQL statement.

SELECT "Id", "UserName", "Email", "PrimaryEmail", "PhoneNumber", "FirstName", "LastName", "DisplayName", "Company", "BirthDate", "BirthDateRaw", "Address", "Address2", "City", "State", "Country", "Culture", "FullName", "Gender", "Language", "MailAddress", "Nickname", "PostalCode", "TimeZone", "Salt", "PasswordHash", "DigestHa1Hash", "Roles", "Permissions", "CreatedDate", "ModifiedDate", "InvalidLoginAttempts", "LastLoginAttempt", "LockedDate", "RecoveryToken", "RefId", "RefIdStr", "Meta" FROM "UserAuth" WHERE "Id" IN (SELECT "Department"."ManagerId" FROM "Department")
5

If you’re doing a LoadSelect it needs to do this query to fetch the related records of all the tables which are then merged with any matching fields on the primary table. It doesn’t matter if you have only 1 row or 1000 rows it will always perform the 2nd related query.

As per the above fix, if you use LoadSingleById it will no longer perform the 2nd query when the RefId is null.

6

I see. Thank you for your help.

1 Like