none
ORDER BY expression parameter

    Question

  • Dear all,

     

    another question -- is it possible to parametrize the "sorting expression" after ORDER BY? I mean something like this:

     

    Code Snippet

    SELECT o.OrderID, o.Customer.CompanyName, o.EmployeeID

    FROM NorthwindEntities.Orders AS o

    ORDER BY @sortBy SKIP(@toSkip) LIMIT(10)

     

     

     

    and then set the parameter @sortBy value to o.OrderID or whatever else.

     

    (Obviously I want to build a pagable grid with sortable columns :-))

     

    However, this throws a QueryException:

     

    ORDER BY key expression must have at least one reference to the immediate input scope, near ORDER BY clause item, line 3, column 46.

     

    I can circumvent it by string concatenating the query:

     

    Code Snippet
                        @"SELECT o.OrderID, o.Customer.CompanyName, o.EmployeeID
                                            FROM NorthwindEntities.Orders AS o
                        ORDER BY " + sortBy + " SKIP(@toSkip) LIMIT(10)"

     

     

     

    but I don't like this solution. Is it something inherently wrong in the first query or is it some kind of a bug? How would you do this scenario?

     

    Thanks.

    Tuesday, March 25, 2008 9:02 PM

Answers

  • Hi,

    Similarly to regular SQL you cannot use parameters to perform query building.  The reason you are seeing the error is that with the parameter inlined this query really means:

    SELECT o.OrderID, o.Customer.CompanyName, o.EmployeeID

    FROM NorthwindEntities.Orders AS o

    ORDER BY ‘o.OrderID’ SKIP(@toSkip) LIMIT(10)

     

    You could, however, use  ObjectQuery builder methods for this:

    string orderingColumn = "OrderID";

     

    ObjectQuery<IExtendedDataRecord> query = Northwind9Context.CreateQuery<IExtendedDataRecord>(@"

    SELECT o.OrderID, o.Customer.CompanyName, o.EmployeeID

    FROM NorthwindEntities.Orders AS o");

               

    query = query.Skip("it." + orderingColumn, "@toSkip").Top("10");

     

    Hope that helps.

    Thanks,

    Kati

    Tuesday, March 25, 2008 10:18 PM
    Moderator

All replies

  • Hi,

    Similarly to regular SQL you cannot use parameters to perform query building.  The reason you are seeing the error is that with the parameter inlined this query really means:

    SELECT o.OrderID, o.Customer.CompanyName, o.EmployeeID

    FROM NorthwindEntities.Orders AS o

    ORDER BY ‘o.OrderID’ SKIP(@toSkip) LIMIT(10)

     

    You could, however, use  ObjectQuery builder methods for this:

    string orderingColumn = "OrderID";

     

    ObjectQuery<IExtendedDataRecord> query = Northwind9Context.CreateQuery<IExtendedDataRecord>(@"

    SELECT o.OrderID, o.Customer.CompanyName, o.EmployeeID

    FROM NorthwindEntities.Orders AS o");

               

    query = query.Skip("it." + orderingColumn, "@toSkip").Top("10");

     

    Hope that helps.

    Thanks,

    Kati

    Tuesday, March 25, 2008 10:18 PM
    Moderator
  • Thanks, Kati. Another question, another quick reply. You in the EF team are real speedies :-)
    Tuesday, March 25, 2008 10:37 PM