Error in generated SQL for UserAuth query

ServiceStack.OrmLite
1

I am doing a query on the userauth and userauthrole table to find users who have a certain role. The result is paginated, so Skip and Take are also used:

var query = Db.From<UserAuth>();
query = query.LeftJoin<UserAuthRole>((a, b) => a.Id == b.UserAuthId)
    .Where<UserAuthRole>(r => "role" == r.Role);
    .Skip(2);
    .Take(2);
    .OrderBy("displayName")
    .SelectDistinct();
    
var list = Db.Select<UserAuth>(query);

This creates the following SQL (SQL Server):

SELECT "UserAuth"."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 (SELECT ROW_NUMBER() OVER (
ORDER BY displayName) As RowNum,  "UserAuth"."Id", "UserAuth"."UserName", "UserAuth"."Email", "UserAuth"."PrimaryEmail", "UserAuth"."PhoneNumber", "UserAuth"."FirstName", "UserAuth"."LastName", "UserAuth"."DisplayName", "UserAuth"."Company", "UserAuth"."BirthDate", "UserAuth"."BirthDateRaw", "UserAuth"."Address", "UserAuth"."Address2", "UserAuth"."City", "UserAuth"."State", "UserAuth"."Country", "UserAuth"."Culture", "UserAuth"."FullName", "UserAuth"."Gender", "UserAuth"."Language", "UserAuth"."MailAddress", "UserAuth"."Nickname", "UserAuth"."PostalCode", "UserAuth"."TimeZone", "UserAuth"."Salt", "UserAuth"."PasswordHash", "UserAuth"."DigestHa1Hash", "UserAuth"."Roles", "UserAuth"."Permissions", "UserAuth"."CreatedDate", "UserAuth"."ModifiedDate", "UserAuth"."InvalidLoginAttempts", "UserAuth"."LastLoginAttempt", "UserAuth"."LockedDate", "UserAuth"."RecoveryToken", "UserAuth"."RefId", "UserAuth"."RefIdStr", "UserAuth"."Meta"  
FROM "UserAuth" LEFT JOIN "UserAuthRole" ON ("UserAuth"."Id" = "UserAuthRole"."UserAuthId")
WHERE (N'my role' = "UserAuthRole"."Role")) AS RowConstrainedResult WHERE RowNum > 2 AND RowNum <= 4

which creates an SqlException

"The multi-part identifier "UserAuth.Id" could not be bound."

It seems that adding the Skip (or Take) is causing the problem as it works without them.

Is there a bug in the SQL generation?

2

This looks like an issue with the windowing function hack that’s needed to implement the missing LIMIT/OFFSET support in legacy versions of SQL Server, it should work if you only used a Take() without a Skip() as it can use the more optimized query using SQL Server’s SELECT TOP 2….

I’ll investigate for a fix.

3

I didn’t know it was a legacy problem, we’re still using SQL Server 2008. Do you know which version it starts to work with?

4

SQL Server only recently added support for OFFSET/FETCH in SQL Server 2012, but of course they didn’t just use the same LIMIT/OFFSET syntax everyone else is using, which forces adding further specialization for specific versions of SQL Server.

5

Hi Colin,

I’m unable to reproduce this issue, are you using the latest version of OrmLite?

I notice that the sample code doesn’t compile or looks like it matches the generated SQL, this is the generated SQL I’m seeing:

SELECT * FROM (SELECT  "UserAuth"."Id", "UserAuth"."UserName", "UserAuth"."Email", "UserAuth"."PrimaryEmail", "UserAuth"."PhoneNumber", "UserAuth"."FirstName", "UserAuth"."LastName", "UserAuth"."DisplayName", "UserAuth"."Company", "UserAuth"."BirthDate", "UserAuth"."BirthDateRaw", "UserAuth"."Address", "UserAuth"."Address2", "UserAuth"."City", "UserAuth"."State", "UserAuth"."Country", "UserAuth"."Culture", "UserAuth"."FullName", "UserAuth"."Gender", "UserAuth"."Language", "UserAuth"."MailAddress", "UserAuth"."Nickname", "UserAuth"."PostalCode", "UserAuth"."TimeZone", "UserAuth"."Salt", "UserAuth"."PasswordHash", "UserAuth"."DigestHa1Hash", "UserAuth"."Roles", "UserAuth"."Permissions", "UserAuth"."CreatedDate", "UserAuth"."ModifiedDate", "UserAuth"."InvalidLoginAttempts", "UserAuth"."LastLoginAttempt", "UserAuth"."LockedDate", "UserAuth"."RecoveryToken", "UserAuth"."RefId", "UserAuth"."RefIdStr", "UserAuth"."Meta", ROW_NUMBER() OVER (
ORDER BY "displayName") As RowNum  
FROM "UserAuth" LEFT JOIN "UserAuthRole" ON ("UserAuth"."Id" = "UserAuthRole"."UserAuthId")
WHERE ('role' = "UserAuthRole"."Role")) AS RowConstrainedResult WHERE RowNum > 2 AND RowNum <= 4

Can you try upgrading to latest version (if not already), if it’s still an issue can you please provide sample code that does reproduce the error.

6

FYI, an SQL Server 2012 Dialect Provider that uses the more optimal SQL Server 2012 new support for OFFSET/FETCH was added in this commit which is available from v4.0.43+ that’s now on MyGet.

It can be registered with:

container.Register<IDbConnectionFactory>(c => 
    new OrmLiteConnectionFactory(connString, SqlServer2012Dialect.Provider));
7

Thank for the change. We’re still on 2008, so I’ll put a TODO in to change it if we upgrade.

I’ve created a project that replicates the issue.

Just needs a blank database in SQL Server 2008 called “userissue”

8

Hi, this works as expected when using the latest v4.0.43 NuGet packages on MyGet.

I’ve also cleaned up your example, to use Dependency injection and the existing base.Db property (not sure if you knew you could do this instead).

I also changed it to create 5 users so the query would return results:

public class MainService : Service
{
    public IUserAuthRepository Users { get; set; }

    public object Get(TestRequest request)
    {
        // create test users
        5.Times(i =>
        {
            var user = Users.CreateUserAuth(new UserAuth { 
                DisplayName = "Test" + i, Email = "test{0}@test.com".Fmt(i), 
                UserName = "test" + i }, "test");
            Users.AssignRoles(user, new[] { "role1", "role2" });
        });

        var query = Db.From<UserAuth>()
            .LeftJoin<UserAuthRole>((a, b) => a.Id == b.UserAuthId)
            .Where<UserAuthRole>(r => r.Role == "role1")
            .Skip(2)
            .Take(2)
            .OrderBy("displayName")
            .SelectDistinct();

        return Db.Select(query);
    }
}