ORM Lite problem with Take and Skip

ServiceStack.OrmLite
1

Hi,
im trying to implement take and skip with ORMLite but this error message.
If i have Skip = 0 it works perfectly

“ErrorCode”: “SqlException”,
“Message”: “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.”,

I have tried to skip using autoquery and just write linq query against DB but get the same error.

  // If no date interval supplied, go to default and return all bookings (paged)
        request.BookingStart = request.BookingStart ?? (DateTime)SqlDateTime.MinValue;
        request.BookingEnd = request.BookingEnd ?? (DateTime)SqlDateTime.MaxValue;

        // If no date interval supplied, go to default and return all bookings (paged)
        request.CreatedFrom = request.CreatedFrom ?? (DateTime)SqlDateTime.MinValue;
        request.CreatedTo = request.CreatedTo ?? (DateTime)SqlDateTime.MaxValue;

        // If no statuses are supplied, go with active statuses as default
        if (request.StatusIds == null || request.StatusIds.Count() == 0)
            request.StatusIds = BookingStatusSets.ActiveById;


        var bookingsQuery = 
        Db.From<Booking>().Where(b => 
        (b.CreatedDate >= request.CreatedFrom && b.CreatedDate <= request.CreatedTo) 
        && (b.From >= request.BookingStart && b.To <= request.BookingEnd)
        ).OrderBy(b => b.From);

        if(request.CompanyId != null)
            bookingsQuery = bookingsQuery.Where(b => b.CompanyId == request.CompanyId);

        Guid fakeCustomerId = new Guid("12345678-1234-1234-1234-123456789abc");
        if (request.CompanyBookings == false && UserSession.CustomerId == Guid.Empty)
            //Set fake Guid ID so you wont get any bookings  
            bookingsQuery = bookingsQuery.Where(b => b.CustomerId == fakeCustomerId);



        //// If we request all bookings
        //bookingsQuery = request.CompanyBookings
        //    ? // Filter by logged in user company or if CompanyId parameter is sent,use that (only allowed for superadmins)
        //    request.CompanyId == null ? bookingsQuery.Where(bookedEvent => bookedEvent.CompanyId == UserSession.CompanyId) : bookingsQuery.Where(bookedEvent => bookedEvent.CompanyId == request.CompanyId)
        //    : // Filter by logged in user id
        //    bookingsQuery.Where(bookedEvent => bookedEvent.CustomerId == UserSession.CustomerId);

        if (request.StatusIds.Count() > 0)
            bookingsQuery = bookingsQuery.Where(b => request.StatusIds.Contains(b.StatusId));


        List<Booking> result;
        bookingsQuery = bookingsQuery.Skip(request.Skip).Take(request.Take);



        result = Db.LoadSelect<Booking>(bookingsQuery).ToList();

Thx!

2

This looks like you’ve hit a limitation with the RDBMS you’re using. Are you using MySQL or something else?

If you’re using SQL Server then it’s likely an issue with the Windowing function needed to get Skip/Take to work. In which case you can use a more recent SqlServer2012Dialect.Provider to take advantage of SQL Servers built-in support for OFFSET/ROWS to avoid using the Windowing function, i.e:

var dbFactory = new OrmLiteConnectionFactory(
    connectionString,  
    SqlServer2012Dialect.Provider);
1 Like
3

Thx, that solved my problem.
I had the setting

new OrmLiteConnectionFactory(connectionString, SqlServerOrmLiteDialectProvider.Instance)

Changed to your setting and now it worked