Basic Sorting

These scenarios demonstrate how to perform basic sorts.

Note: Sorting by dynamically chosen columns or by expressions will be handled in a separate scenarios.

Scenario Prototype

public interface ISortingScenario<TEmployeeSimple>
   where TEmployeeSimple : class, IEmployeeSimple, new()
{
    /// <summary>
    /// Insert a collection of Employee rows.
    /// </summary>
    void InsertBatch(IList<TEmployeeSimple> employees);

    /// <summary>
    /// Sorts by the first name
    /// </summary>
    IList<TEmployeeSimple> SortByFirstName(string lastName);

    /// <summary>
    /// Sorts by the middle name in reverse order, then the first name.
    /// </summary>
    IList<TEmployeeSimple> SortByMiddleNameDescFirstName(string lastName);

    /// <summary>
    /// Sorts by the middle name, then the first name.
    /// </summary>
    IList<TEmployeeSimple> SortByMiddleNameFirstName(string lastName);
}

ADO.NET

public IList<EmployeeSimple> SortByFirstName(string lastName)
{
    const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName ORDER BY e.FirstName";

    using (var con = OpenConnection())
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@LastName", lastName);

        var results = new List<EmployeeSimple>();

        using (var reader = cmd.ExecuteReader())
            while (reader.Read())
                results.Add(new EmployeeSimple(reader));

        return results;
    }
}

public IList<EmployeeSimple> SortByMiddleNameDescFirstName(string lastName)
{
    const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName " +
        "ORDER BY e.MiddleName DESC, e.FirstName";

    using (var con = OpenConnection())
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@LastName", lastName);

        var results = new List<EmployeeSimple>();

        using (var reader = cmd.ExecuteReader())
            while (reader.Read())
                results.Add(new EmployeeSimple(reader));

        return results;
    }
}

public IList<EmployeeSimple> SortByMiddleNameFirstName(string lastName)
{
    const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName " +
        "ORDER BY e.MiddleName, e.FirstName";

    using (var con = OpenConnection())
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@LastName", lastName);

        var results = new List<EmployeeSimple>();

        using (var reader = cmd.ExecuteReader())
            while (reader.Read())
                results.Add(new EmployeeSimple(reader));

        return results;
    }
}

Chain

Columns to be sorted by are passed in as strings, but checked against the list of columns at runtime to prevent SQL injection attacks. A SortExpression object is needed for reverse sorting.

public IList<EmployeeSimple> SortByFirstName(string lastName)
{
    return m_DataSource.From<EmployeeSimple>(new { lastName })
        .WithSorting("FirstName").ToCollection().Execute();
}

public IList<EmployeeSimple> SortByMiddleNameDescFirstName(string lastName)
{
    return m_DataSource.From<EmployeeSimple>(new { lastName })
        .WithSorting(new SortExpression("MiddleName", SortDirection.Descending), "FirstName")
        .ToCollection<EmployeeSimple>().Execute();
}

public IList<EmployeeSimple> SortByMiddleNameFirstName(string lastName)
{
    return m_DataSource.From<EmployeeSimple>(new { lastName })
        .WithSorting("MiddleName", "FirstName").ToCollection<EmployeeSimple>().Execute();
}

Dapper

public IList<EmployeeSimple> SortByFirstName(string lastName)
{
    const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName " +
        "ORDER BY e.FirstName";

    using (var con = OpenConnection())
        return con.Query<EmployeeSimple>(sql, new { lastName }).ToList();
}

public IList<EmployeeSimple> SortByMiddleNameDescFirstName(string lastName)
{
    const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName " +
        "ORDER BY e.MiddleName DESC, e.FirstName";

    using (var con = OpenConnection())
        return con.Query<EmployeeSimple>(sql, new { lastName }).ToList();
}

public IList<EmployeeSimple> SortByMiddleNameFirstName(string lastName)
{
    const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, " +
        "e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @LastName " +
        "ORDER BY e.MiddleName, e.FirstName";

    using (var con = OpenConnection())
        return con.Query<EmployeeSimple>(sql, new { lastName }).ToList();
}

DbConnector

public IList<EmployeeSimple> SortByFirstName(string lastName)
{
    const string sql = @"SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone,
        e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @lastName
        ORDER BY e.FirstName;";


    return DbConnector.ReadToList<EmployeeSimple>(sql, new { lastName }).Execute();
}

public IList<EmployeeSimple> SortByMiddleNameDescFirstName(string lastName)
{
    const string sql = @"SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone,
        e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @lastName
        ORDER BY e.MiddleName DESC, e.FirstName;";

    return DbConnector.ReadToList<EmployeeSimple>(sql, new { lastName }).Execute();
}

public IList<EmployeeSimple> SortByMiddleNameFirstName(string lastName)
{
    const string sql = @"
        SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone,
        e.CellPhone, e.EmployeeClassificationKey FROM HR.Employee e WHERE e.LastName = @lastName
        ORDER BY e.MiddleName, e.FirstName;";

    return DbConnector.ReadToList<EmployeeSimple>(sql, new { lastName }).Execute();
}

Entity Framework 6

public IList<Employee> SortByFirstName(string lastName)
{
    using (var context = CreateDbContext())
        return context.Employee.Where(x => x.LastName == lastName)
            .OrderBy(x => x.FirstName).ToList();
}

public IList<Employee> SortByMiddleNameDescFirstName(string lastName)
{
    using (var context = CreateDbContext())
        return context.Employee.Where(x => x.LastName == lastName)
            .OrderByDescending(x => x.MiddleName).ThenBy(x => x.FirstName).ToList();
}

public IList<Employee> SortByMiddleNameFirstName(string lastName)
{
    using (var context = CreateDbContext())
        return context.Employee.Where(x => x.LastName == lastName)
            .OrderBy(x => x.MiddleName).ThenBy(x => x.FirstName).ToList();
}

Entity Framework Core

public IList<Employee> SortByFirstName(string lastName)
{
    using (var context = CreateDbContext())
        return context.Employees.Where(x => x.LastName == lastName)
            .OrderBy(x => x.FirstName).ToList();
}

public IList<Employee> SortByMiddleNameDescFirstName(string lastName)
{
    using (var context = CreateDbContext())
        return context.Employees.Where(x => x.LastName == lastName)
            .OrderByDescending(x => x.MiddleName).ThenBy(x => x.FirstName).ToList();
}

public IList<Employee> SortByMiddleNameFirstName(string lastName)
{
    using (var context = CreateDbContext())
        return context.Employees.Where(x => x.LastName == lastName)
            .OrderBy(x => x.MiddleName).ThenBy(x => x.FirstName).ToList();
}

LINQ to DB

public IList<Employee> SortByFirstName(string lastName)
{
    using (var db = new OrmCookbook())
        return db.Employee.Where(x => x.LastName == lastName)
            .OrderBy(x => x.FirstName).ToList();
}

public IList<Employee> SortByMiddleNameDescFirstName(string lastName)
{
    using (var db = new OrmCookbook())
        return db.Employee.Where(x => x.LastName == lastName)
            .OrderByDescending(x => x.MiddleName).ThenBy(x => x.FirstName).ToList();
}

public IList<Employee> SortByMiddleNameFirstName(string lastName)
{
    using (var db = new OrmCookbook())
        return db.Employee.Where(x => x.LastName == lastName)
            .OrderBy(x => x.MiddleName).ThenBy(x => x.FirstName).ToList();
}

LLBLGen Pro

public IList<EmployeeEntity> SortByFirstName(string lastName)
{
    using(var adapter = new DataAccessAdapter())
    {
        return new LinqMetaData(adapter).Employee.Where(x => x.LastName == lastName)
                                        .OrderBy(x => x.FirstName)
                                        .ToList();
    }
}

public IList<EmployeeEntity> SortByMiddleNameDescFirstName(string lastName)

{
    using(var adapter = new DataAccessAdapter())
    {
        return new LinqMetaData(adapter).Employee.Where(x => x.LastName == lastName)
                                        .OrderByDescending(x => x.MiddleName)
                                        .ThenBy(x => x.FirstName)
                                        .ToList();
    }
}

public IList<EmployeeEntity> SortByMiddleNameFirstName(string lastName)
{
    using(var adapter = new DataAccessAdapter())
    {
        return new LinqMetaData(adapter).Employee
                                        .Where(x => x.LastName == lastName)
                                        .OrderBy(x => x.MiddleName).ThenBy(x => x.FirstName)
                                        .ToList();
    }
}

NHibernate

public IList<Employee> SortByFirstName(string lastName)
{
    using (var session = m_SessionFactory.OpenStatelessSession())
    {
        return session.QueryOver<Employee>().Where(x => x.LastName == lastName)
            .OrderBy(x => x.FirstName).Asc
            .List();
    }
}

public IList<Employee> SortByMiddleNameDescFirstName(string lastName)
{
    using (var session = m_SessionFactory.OpenStatelessSession())
    {
        return session.QueryOver<Employee>().Where(x => x.LastName == lastName)
            .OrderBy(x => x.MiddleName).Desc.ThenBy(x => x.FirstName).Asc
            .List();
    }
}

public IList<Employee> SortByMiddleNameFirstName(string lastName)
{
    using (var session = m_SessionFactory.OpenStatelessSession())
    {
        return session.QueryOver<Employee>().Where(x => x.LastName == lastName)
            .OrderBy(x => x.MiddleName).Asc.ThenBy(x => x.FirstName).Asc
            .List();
    }
}

RepoDb

public IList<EmployeeSimple> SortByFirstName(string lastName)
{
    return Query(x => x.LastName == lastName)
        .OrderBy(x => x.FirstName).AsList();
}

public IList<EmployeeSimple> SortByMiddleNameDescFirstName(string lastName)
{
    return Query(x => x.LastName == lastName)
        .OrderByDescending(x => x.MiddleName).ThenBy(x => x.FirstName).AsList();
}

public IList<EmployeeSimple> SortByMiddleNameFirstName(string lastName)
{
    return Query(x => x.LastName == lastName)
        .OrderBy(x => x.MiddleName).ThenBy(x => x.FirstName).AsList();
}

ServiceStack

public IList<Employee> SortByFirstName(string lastName)
{
    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        return db.Select(db.From<Employee>().Where(x => x.LastName == lastName)
            .OrderBy(x => new { x.FirstName })).ToList();
    }
}

public IList<Employee> SortByMiddleNameDescFirstName(string lastName)

{
    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        return db.Select(db.From<Employee>().Where(x => x.LastName == lastName)
            .OrderByDescending(x => new { x.MiddleName }).ThenBy(x => new { x.FirstName })).ToList();
    }
}

public IList<Employee> SortByMiddleNameFirstName(string lastName)

{
    using (var db = _dbConnectionFactory.OpenDbConnection())
    {
        return db.Select(db.From<Employee>().Where(x => x.LastName == lastName)
            .OrderBy(x => new { x.MiddleName, x.FirstName })).ToList();
    }
}