This project is read-only.

SF 2.0 OrderBy in queries

May 17, 2011 at 12:07 PM

Hi,

When I define a query like 

                // Default Query
                dqm[typeof(CustomerBO)] =
                    (from c in Database.Query<CustomerBO>()
                     where c.AuditInfo.Deleted == null
                     select new
                     {
                         Entity = c.ToLite(),
                         c.Id,
                         c.Name,
                         c.FASReference,
                         c.Email,
                     }).OrderBy(o => o.Name).ToDynamic();

It results in this SQL Server (2005) statement:

exec sp_executesql N'SELECT TOP (@p0) cuticbo.Id, cuticbo.ToStr, cuticbo.Name, cuticbo.FasReference, cuticbo.Email
FROM CUTICustomerBO AS cuticbo
WHERE (cuticbo.AuditInfo_Deleted) IS NULL
ORDER BY cuticbo.Name',N'@p0 int',@p0=250

The customers are shown ordered by their name in the search control.

When Iclick on a column header the following SQL statement is fired:

exec sp_executesql N'SELECT TOP (@p0) s4.Id, s4.ToStr, s4.Name, s4.FasReference, s4.Email
FROM (
  (SELECT cuticbo.Id, cuticbo.ToStr, cuticbo.Name, cuticbo.FasReference, cuticbo.Email
  FROM CUTICustomerBO AS cuticbo
  WHERE (cuticbo.AuditInfo_Deleted) IS NULL
  ORDER BY cuticbo.Name)
) AS s4
ORDER BY s4.Email',N'@p0 int',@p0=250

Resulting in an error message:

The translated query has an ORDERBY in a innner SELECT statement, write the query in a different way

Am I missing something ? This used to work in version 1.

TIA,

Marcel Numeijer

 

 

 

 

 

 

 

 

 


May 17, 2011 at 10:03 PM
Edited May 17, 2011 at 10:04 PM

Hi nuub, 

We usually don't put OrderBy on queries registered in the DQM, since then there's no way to get rid of it (then you order by something else AND the new order criteria, not instead). 

What we usually do is use the Order property in the FindOptions, this is the proper way or setting a default Order for a dynamic query.

 

Given said that, you right this used to work:

In Signum Framework 1.0 we were able to 'reorder orderby' so they are always in the outer most query. 

In Signum Framework 2.0 we removed all this complex code and we rely in the programmer using OrderBy at the end of the query, we have been like this for a year now. 

Two weeks ago, i regret about this decision and I put the feature in again, with some other big changes in the LINQ Provided that I've made recently 

 * OrderBy reordering 

 * Reverse, Last and LastOrDefault 

        * Some other candies (Truncate, etc...)

 * Database.QueryMList<> with UnsafeDelete and UnsafeUpdate support

 * Entities are now Retrived using LINQ queries only (Retriever class is gone) resulting in much better performance. 

All this features are over and have been tested against our battery of queries (arround 400 tests) but we haven't integrated it yet with the rest of our applications so i'm not 100% confident yet.

We are also preparing other big changes in the way 'dot joins' work:

        * Being able to simplify some SelectMany to joins/ outer joins

        * Being able to efficiently translate First/Single/FirstOrDefault/SingleOrDefault when inside of the query

I hope I've find time to finish all this changes, and test them properly. They shouldn't make any code changes. 

 

Kind regards!