CODEWITHSUNDEEP
c#sql-order-byormlite-servicestack
Harris
Harris

Reputation: 1138

ServiceStack ormLite chaning OrderBy

I am trying to so the following:

var routines = con.Select<Table>(con.From<Table>().OrderBy(p => p.Field1).ThenBy(i => i.Field2));

The above works perfectly. But I want a rather more generic approach and parse a string like sort="field1,field2". I have the following code:

int sortFieldCount = 0;
var itemsq = con.From<Table>();
foreach (var name in orderByField.Split(',')) {
if(sortFieldCount == 0)
  itemsq = sortOrderAscending ? itemsq.OrderBy(name) : itemsq.OrderByDescending(name);
else
   itemsq = sortOrderAscending ? itemsq.ThenBy(name) : itemsq.ThenByDescending(name);
sortFieldCount++;
}

But the above code seems to overwrite the first OrderBy. Is there a solution to such a problem?

Thanks

Upvotes: 2

Views: 1298

Answers (2)

mythz
mythz

Reputation: 143349

Other ways you can perform multiple Order By's with ServiceStack.OrmLite include:

var orderByAnonType = db.Select(db.From<Track>().OrderBy(x => new { x.Album, x.Name }));

var orderByString = db.Select(db.From<Track>().OrderByFields("Album","Name"));

// Use `-` prefix to inverse sort order, e.g. Album Descending
var orderByString = db.Select(db.From<Track>().OrderByFields("-Album","Name"));

var orderByArray = db.Select(db.From<Track>().OrderBy(x => new[]{ "Album","Name" }));

So you could get a flexible OrderBy like AutoQuery's OrderBy with:

var fields = orderByString.Split(',', StringSplitOptions.RemoveEmptyEntries);
q.OrderByFields(fields);

Here's a live example of this you can play around with on gistlyn.com

Upvotes: 6

Jonathan Allen
Jonathan Allen

Reputation: 70327

There is a couple problems in the accepted answer I'd like to address.

First is the possibility of SQL injection attacks. ServiceStack does not fully validate what you pass in to the list of sort columns. While it will detect some of the more obvious attacks, you could still slip in things like calls to stored functions.

The second problem is descending sorts. It's not obvious from the API, but you can pass in "columnName DESC" rather than just "columnName". In fact, this is how it is able to support "Album,Name", it just passes it directly to SQL with the barest amount of validation.

public IList<Employee> SortBy(string lastName, string sortByColumnA, bool isDescendingA, string sortByColumnB, bool isDescendingB)
{
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnA))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnA), "Unknown column " + sortByColumnA);
    if (!Utilities.EmployeeColumnNames.Contains(sortByColumnB))
        throw new ArgumentOutOfRangeException(nameof(sortByColumnB), "Unknown column " + sortByColumnB);

    var sortDirectionA = isDescendingA ? " DESC " : "";
    var sortDirectionB = isDescendingB ? " DESC " : "";

    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        return db.Select(db.From<Employee>().Where(x => x.LastName == lastName)
            .OrderBy(sortByColumnA + sortDirectionA + "," + sortByColumnB + sortDirectionB)).ToList();
    }
}

Upvotes: -1

Related Questions