locked
Entity Framework and dynamic order by statements RRS feed

  • Question

  • Hey all.

    I have been struggling to get this working. I wish to have an EF statement take in a column to order by. My original statement was this:

    var Query = from P in DbContext.People
                       where P.BusinessUnits.Any(BU =>BU.BusinessUnitID == businessUnitId)
                       orderby P.LastName
                       select P;
    

    And I changed this to the following:

    var Query = from P in DbContext.People
                       where P.BusinessUnits.Any(BU =>BU.BusinessUnitID == businessUnitId)
                       orderby sortField
                       select P;
    

    Where "sortField" is the column we wish to sort on, and is a string i.e. "LastName". However, it does not appear to work, it does no sorting, and the outputted SQL string is completely wrong. Anyone got this working before?


    Developer
    Tuesday, December 13, 2011 4:09 PM

Answers

  • Hi eyeballpaul,

    Welcome!

    You can also try Dynamic LINQ here: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by eyeballpaul Wednesday, December 14, 2011 9:09 AM
    Wednesday, December 14, 2011 7:27 AM
  • Hi eyeballpaul;

    You can't do it in the way you have attempted but you can construct the query in steps as shown in the following code snippet.

    var Query = from P in DbContext.People
                where P.BusinessUnits.Any(BU =>BU.BusinessUnitID == businessUnitId)
                select P;
    
    if( sortField == "LastName" )
    {
        Query = Query.OrderBy( p => p.LastName );
    }
    
    // Other test
    
    if( sortField == "BusinessUnitID" )
    {
        Query = Query.OrderBy( p => p.BusinessUnitID );
    }
    
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Alan_chen Wednesday, December 14, 2011 7:27 AM
    • Marked as answer by eyeballpaul Wednesday, December 14, 2011 9:05 AM
    Tuesday, December 13, 2011 6:23 PM
  • Thanks for your answers.

    It can also be done by using:

                // holds the order by expression for the query
                Expression<Func<Person, Object>> OrderByExpression = null;
    
                // get the expression from the sort field passed in
                switch (sortField.ToLower())
                {
                    case "personpartyid":
                        OrderByExpression = p => p.PersonPartyID;
                        break;
                    case "salutation":
                        OrderByExpression = p => p.Salutation;
                        break;
                    case "firstname":
                        OrderByExpression = p => p.FirstName;
                        break;
                    case "lastname":
                        OrderByExpression = p => p.LastName;
                        break;
                    default:
                        OrderByExpression = p => p.LastName;
                        break;
                }
    
                
                // Create a context to the transaction database
                using (VortexTransactionDatabase DbContext = new VortexTransactionDatabase(transactionDbConnectionString))
                {
                    // Get the people from the database
                    var Query = from P in DbContext.People
                                .Where(P => P.BusinessUnits.Any(BU => BU.BusinessUnitID == businessUnitId))
                                .OrderBy(OrderByExpression)
                                select P;
    
                    // now return the list of people
                    return Query.Skip(startPositionOfUsers - 1).Take(numberOfUsersToReturn).ToList();
                }
    


    Developer
    • Marked as answer by eyeballpaul Wednesday, December 14, 2011 9:08 AM
    Wednesday, December 14, 2011 9:08 AM

All replies

  • Hi eyeballpaul;

    You can't do it in the way you have attempted but you can construct the query in steps as shown in the following code snippet.

    var Query = from P in DbContext.People
                where P.BusinessUnits.Any(BU =>BU.BusinessUnitID == businessUnitId)
                select P;
    
    if( sortField == "LastName" )
    {
        Query = Query.OrderBy( p => p.LastName );
    }
    
    // Other test
    
    if( sortField == "BusinessUnitID" )
    {
        Query = Query.OrderBy( p => p.BusinessUnitID );
    }
    
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Alan_chen Wednesday, December 14, 2011 7:27 AM
    • Marked as answer by eyeballpaul Wednesday, December 14, 2011 9:05 AM
    Tuesday, December 13, 2011 6:23 PM
  • Hi eyeballpaul,

    Welcome!

    You can also try Dynamic LINQ here: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by eyeballpaul Wednesday, December 14, 2011 9:09 AM
    Wednesday, December 14, 2011 7:27 AM
  • Thanks for your answers.

    It can also be done by using:

                // holds the order by expression for the query
                Expression<Func<Person, Object>> OrderByExpression = null;
    
                // get the expression from the sort field passed in
                switch (sortField.ToLower())
                {
                    case "personpartyid":
                        OrderByExpression = p => p.PersonPartyID;
                        break;
                    case "salutation":
                        OrderByExpression = p => p.Salutation;
                        break;
                    case "firstname":
                        OrderByExpression = p => p.FirstName;
                        break;
                    case "lastname":
                        OrderByExpression = p => p.LastName;
                        break;
                    default:
                        OrderByExpression = p => p.LastName;
                        break;
                }
    
                
                // Create a context to the transaction database
                using (VortexTransactionDatabase DbContext = new VortexTransactionDatabase(transactionDbConnectionString))
                {
                    // Get the people from the database
                    var Query = from P in DbContext.People
                                .Where(P => P.BusinessUnits.Any(BU => BU.BusinessUnitID == businessUnitId))
                                .OrderBy(OrderByExpression)
                                select P;
    
                    // now return the list of people
                    return Query.Skip(startPositionOfUsers - 1).Take(numberOfUsersToReturn).ToList();
                }
    


    Developer
    • Marked as answer by eyeballpaul Wednesday, December 14, 2011 9:08 AM
    Wednesday, December 14, 2011 9:08 AM
  • Nice answer and sample code.
    Friday, March 16, 2018 1:29 PM
  • i found another good answer in this link https://codereview.stackexchange.com/questions/3560/dynamic-filtering-and-sorting-with-entity-framework
    Friday, March 16, 2018 1:36 PM