Bug - OrderBy with Children

Bug - OrderBy with Children

Postby Nahmis » Sun 04 Oct 2009 11:17

With the following Linq Query:
Code: Select all
var globalsites = from gs in Data.Context.GlobalSites.Current(GridMain)
         join add in Data.Context.Address on gs.PrimaryBusinessAddress equals add into gsa
         from a in gsa.DefaultIfEmpty()
         orderby a.AddressLine1, a.City, a.State, a.Country
              select new   {
                        gs.Id,
                        gs.RecordStatus,
                        gs.EntityId,
                        gs.DateModified,
                        gs.SiteName,
                        PrimaryBusinessAddress = a
                        };


We get the following SQL:
Code: Select all
SELECT t1."Id", t1."RecordStatus", t1."EntityId", t1."DateModified", t1."SiteName", t1.ID, t1.LOCATION_DETAIL_ID, t1.ADDRESS_LINE1, t1.ADDRESS_LINE2, t1.ADDRESS_LINE3, t1.CITY, t1.ZIP_CODE, t1.ADDRESS_TYPE, t1.IS_PRIMARY, t1.OWNER, t1.LAST_USER, t1.MODIFIED_REASON, t1.DATE_CREATED, t1.DATE_MODIFIED, t1.VERSION, t1.ENTITY_ID, t1.CONTACT_ID, t1.GLOBAL_ORGANIZATION_ID, t1.GLOBAL_SITE_ID, t1.RECORD_STATUS, t1.CONTACT_NAME, t1.INSTITUTION_NAME, t1.DATE_START, t1.LOCATION_OBJECT_ID, t1.PROTOCOL_ID, t1.STATE, t1.COUNTRY, t1.TIME_ZONE
FROM (
    SELECT t2.ID AS "Id", t2.RECORD_STATUS AS "RecordStatus", t2.ENTITY_ID AS "EntityId", t2.DATE_MODIFIED AS "DateModified", t2.SITE_NAME AS "SiteName", t2.ID1 AS ID, t2.LOCATION_DETAIL_ID, t2.ADDRESS_LINE1, t2.ADDRESS_LINE2, t2.ADDRESS_LINE3, t2.CITY, t2.ZIP_CODE, t2.ADDRESS_TYPE, t2.IS_PRIMARY, t2.OWNER, t2.LAST_USER, t2.MODIFIED_REASON, t2.DATE_CREATED, t2.DATE_MODIFIED1 AS DATE_MODIFIED, t2.VERSION, t2.ENTITY_ID1 AS ENTITY_ID, t2.CONTACT_ID, t2.GLOBAL_ORGANIZATION_ID, t2.GLOBAL_SITE_ID, t2.RECORD_STATUS1 AS RECORD_STATUS, t2.CONTACT_NAME, t2.INSTITUTION_NAME, t2.DATE_START, t2.LOCATION_OBJECT_ID, t2.PROTOCOL_ID, t2.STATE, t2.COUNTRY, t2.TIME_ZONE, ROW_NUMBER() OVER (ORDER BY t2.SITE_NAME) AS rnum
    FROM (
        SELECT t3.ID, t3.SITE_NAME, t3.DATE_MODIFIED, t3.ENTITY_ID, t3.RECORD_STATUS, t4.ID AS ID1, t4.LOCATION_DETAIL_ID, t4.ADDRESS_LINE1, t4.ADDRESS_LINE2, t4.ADDRESS_LINE3, t4.CITY, t4.ZIP_CODE, t4.ADDRESS_TYPE, t4.IS_PRIMARY, t4.OWNER, t4.LAST_USER, t4.MODIFIED_REASON, t4.DATE_CREATED, t4.DATE_MODIFIED AS DATE_MODIFIED1, t4.VERSION, t4.ENTITY_ID AS ENTITY_ID1, t4.CONTACT_ID, t4.GLOBAL_ORGANIZATION_ID, t4.GLOBAL_SITE_ID, t4.RECORD_STATUS AS RECORD_STATUS1, t4.CONTACT_NAME, t4.INSTITUTION_NAME, t4.DATE_START, t4.LOCATION_OBJECT_ID, t4.PROTOCOL_ID, t4.STATE, t4.COUNTRY, t4.TIME_ZONE
        FROM I22.GLOBAL_SITE t3
        LEFT OUTER JOIN I22.ADDRESS t4 ON t3.PRIMARY_BUSINESS_ADDRESS_ID = t4.ID
        WHERE (t3.RECORD_STATUS = :p0) AND (t3.ENTITY_ID = :p1)
        ) t2
    WHERE (:p2 <> 0) AND (t2.RECORD_STATUS = :p3) AND (t2.ENTITY_ID = :p4)
    ORDER BY t2.ADDRESS_LINE1, t2.CITY, t2.STATE, t2.COUNTRY, t2.SITE_NAME
    ) t1
WHERE (t1.rnum > :p5) AND (t1.rnum <= :p6)


Notice the orderby isn't applied to the row number:
Code: Select all
ROW_NUMBER() OVER (ORDER BY t2.SITE_NAME) AS rnum


This means that no matter what orderby we specify, it isn't carried and when we grab the first 25 rows, we only get an ordered set from the first 25 items on the parent table, not an order of the entire set then grabbing the first 25.



What we're able to do in Linq-to-SQL is the following:
Code: Select all
var globalsites = from gs in Data.Context.GlobalSites.Current(GridMain)
         orderby gs.PrimaryBusinessAddress.AddressLine1, gs.PrimaryBusinessAddress.City, gs.PrimaryBusinessAddress.State, gs.PrimaryBusinessAddress.Country
         select new {
                        gs.Id,
                        gs.RecordStatus,
                        gs.EntityId,
                        gs.DateModified,
                        gs.SiteName,
                        gs.PrimaryBusinessAddress
                        };


In Linq-to-SQL this results in a left joined subtable and an outer orderby that uses the columns from it. In dotConnect it's much different:
Code: Select all
SELECT t1."Id", t1."RecordStatus", t1."EntityId", t1."DateModified", t1."SiteName", t1.PRIMARY_BUSINESS_ADDRESS_ID
FROM (
    SELECT t2.ID AS "Id", t2.RECORD_STATUS AS "RecordStatus", t2.ENTITY_ID AS "EntityId", t2.DATE_MODIFIED AS "DateModified", t2.SITE_NAME AS "SiteName", t2.PRIMARY_BUSINESS_ADDRESS_ID, ROW_NUMBER() OVER (ORDER BY t2.SITE_NAME) AS rnum
    FROM (
        SELECT t3.ID, t3.SITE_NAME, t3.DATE_MODIFIED, t3.ENTITY_ID, t3.RECORD_STATUS, t3.PRIMARY_BUSINESS_ADDRESS_ID, t7.ADDRESS_LINE1, t6.CITY, t5.STATE, t4.COUNTRY
        FROM I22.GLOBAL_SITE t3
        INNER JOIN I22.ADDRESS t4 ON t3.PRIMARY_BUSINESS_ADDRESS_ID = t4.ID
        INNER JOIN I22.ADDRESS t5 ON t3.PRIMARY_BUSINESS_ADDRESS_ID = t5.ID
        INNER JOIN I22.ADDRESS t6 ON t3.PRIMARY_BUSINESS_ADDRESS_ID = t6.ID
        INNER JOIN I22.ADDRESS t7 ON t3.PRIMARY_BUSINESS_ADDRESS_ID = t7.ID
        WHERE (t3.RECORD_STATUS = :p0) AND (t3.ENTITY_ID = :p1)
        ) t2
    WHERE (:p2 <> 0) AND (t2.RECORD_STATUS = :p3) AND (t2.ENTITY_ID = :p4)
    ORDER BY t2.ADDRESS_LINE1, t2.CITY, t2.STATE, t2.COUNTRY, t2.SITE_NAME
    ) t1
WHERE (t1.rnum > :p5) AND (t1.rnum <= :p6)


Notice we quadruple join to the same table and it's an INNER JOIN, meaning that anything without a PrimaryBusinessAddress is left out entirely. Additionally, every row executes:
Code: Select all
SELECT t1.ID, t1.LOCATION_DETAIL_ID, t1.ADDRESS_LINE1, t1.ADDRESS_LINE2, t1.ADDRESS_LINE3, t1.CITY, t1.ZIP_CODE, t1.ADDRESS_TYPE, t1.IS_PRIMARY, t1.OWNER, t1.LAST_USER, t1.MODIFIED_REASON, t1.DATE_CREATED, t1.DATE_MODIFIED, t1.VERSION, t1.ENTITY_ID, t1.CONTACT_ID, t1.GLOBAL_ORGANIZATION_ID, t1.GLOBAL_SITE_ID, t1.RECORD_STATUS, t1.CONTACT_NAME, t1.INSTITUTION_NAME, t1.DATE_START, t1.LOCATION_OBJECT_ID, t1.PROTOCOL_ID, t1.STATE, t1.COUNTRY, t1.TIME_ZONE
FROM I22.ADDRESS t1
WHERE :np0 = t1.ID


Instead of 1 query for 1:1 children, we get children*n+1 performance and most of the results get cut off.

Is there something we're missing in the model, or is this the expected behavior? So far we can find no way to sort by a child's properties, even when that child is a 1:1. The PrimaryBusinessAddress in our case is on the parent object, GlobalSite has a PrimaryBusinessAddressId column, so there's no need to do quadruple joins, but we can't see to get both a left join and ordering working.

On a side note:
Code: Select all
.OrderBy(gs => gs.PrimaryBusinessAddress.AddressLine1)

Throws:
Code: Select all
Specified method is not supported.
Devart.Data.Linq.Provider.Query.a.a(bh A_0) +428
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0) +852
   Devart.Data.Linq.Provider.Query.c.b(bc A_0) +787
   Devart.Data.Linq.Provider.Query.c.a(bc A_0) +19
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0) +1098
   Devart.Data.Linq.Provider.Query.SqlVisitor.c(SqlExpression A_0) +8
   Devart.Data.Linq.Provider.Query.SqlVisitor.e(bo A_0) +215
   Devart.Data.Linq.Provider.Query.c.a(bo A_0) +83
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(ax A_0) +64
   Devart.Data.Linq.Provider.Query.c.a(ax A_0) +71
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0) +1423
   Devart.Data.Linq.Provider.Query.SqlVisitor.e(SqlNode A_0) +7
   Devart.Data.Linq.Provider.Query.SqlVisitor.e(bo A_0) +27
   Devart.Data.Linq.Provider.Query.c.a(bo A_0) +83
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(ax A_0) +64
   Devart.Data.Linq.Provider.Query.c.a(ax A_0) +71
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0) +1423
   Devart.Data.Linq.Provider.Query.SqlVisitor.e(SqlNode A_0) +7
   Devart.Data.Linq.Provider.Query.SqlVisitor.e(bo A_0) +27
   Devart.Data.Linq.Provider.Query.c.a(bo A_0) +83
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(ax A_0) +64
   Devart.Data.Linq.Provider.Query.c.a(ax A_0) +71
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0) +1423
   Devart.Data.Linq.Provider.Query.SqlVisitor.e(SqlNode A_0) +7
   Devart.Data.Linq.Provider.Query.SqlVisitor.e(bo A_0) +27
   Devart.Data.Linq.Provider.Query.c.a(bo A_0) +83
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(ax A_0) +64
   Devart.Data.Linq.Provider.Query.c.a(ax A_0) +71
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0) +1423
   Devart.Data.Linq.Provider.Query.c.a(av A_0) +51
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0) +109
   Devart.Data.Linq.Provider.Query.SqlVisitor.c(SqlExpression A_0) +8
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(d A_0) +67
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0) +1344
   Devart.Data.Linq.Provider.Query.SqlVisitor.e(bo A_0) +342
   Devart.Data.Linq.Provider.Query.c.a(bo A_0) +83
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(ax A_0) +64
   Devart.Data.Linq.Provider.Query.c.a(ax A_0) +71
   Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0) +1423
   Devart.Data.Linq.Provider.Query.p.a(SqlNode A_0, SqlFactory A_1, TypeSystemProvider A_2, ao A_3, a A_4) +79
   Devart.Data.Linq.Provider.DataProvider.a(c A_0, Type A_1, SqlNode A_2, IList`1 A_3) +184
   Devart.Data.Linq.Provider.DataProvider.a(Expression A_0) +143
   Devart.Data.Linq.Provider.DataProvider.h(Expression A_0) +67
   Devart.Data.Linq.DataQuery`1.a(Expression A_0) +29
   System.Web.Query.Dynamic.DynamicQueryable.Count(IQueryable source) +148
   System.Web.UI.WebControls.DynamicQueryableWrapper.Count(IQueryable source) +6
   System.Web.UI.WebControls.LinqDataSourceView.ExecuteSelectAutoSortAndPage(IQueryable source, DataSourceSelectArguments arguments) +115
   System.Web.UI.WebControls.LinqDataSourceView.ExecuteSelectQuery(LinqDataSourceSelectEventArgs selectEventArgs, Object selectResult, Object table, Boolean storeOriginalValues) +327
   System.Web.UI.WebControls.LinqDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +457
Nahmis
 
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Postby AndreyR » Tue 06 Oct 2009 10:04

We are investigating this issue. I will let you know about the results of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Fri 09 Oct 2009 12:11

Could you please provide the LINQ query generated in Current(GridMain)?
Where did you set the Skip and Take parameters?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Nahmis » Sat 10 Oct 2009 14:01

The Take and Skip are set by the GridView/LinqData source, we have no code for this:

Here is the Current method:

Code: Select all
      public static IQueryable Current(this IQueryable source, Grid grid) where T : IBaseEntity
      {
         var result = source.Where(GetEntityExpression(grid));
         return !grid.InHistory && grid.RecStatus != Enums.RecordStatus.All ? result.Where(GetRecordStatusExpression((long)grid.RecStatus)) : result;
      }

      private static Expression> GetEntityExpression(Grid grid)
      {
         var entParam = Expression.Parameter(typeof(T), "ent");
         return Expression.Lambda>(Expression.Equal(Expression.Property(entParam, "EntityId"), Expression.Constant(grid.InHistory ? grid.HistoryID : 0L)), new[] { entParam });
      }

      private static Expression> GetRecordStatusExpression(long status)
      {
         var entParam = Expression.Parameter(typeof(T), "ent");
         return Expression.Lambda>(Expression.Equal(Expression.Property(entParam, "RecordStatus"), Expression.Constant(status)), new[] { entParam });
      }


It's basically performing .Where(EntityId == 0 && RecordStatus == 0) we just have other uses for expression trees and CompiledQueries so it's broken up in the methods above. You can just exclude the .Current() call from testing, same result on the sorting.
Nahmis
 
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Postby AndreyR » Mon 12 Oct 2009 15:06

Thank you for the clarification.
We have found some problems due to your help.
I will let you know as soon as these problems are fixed.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Update

Postby Nahmis » Tue 27 Oct 2009 13:22

Any update or timeline on this? It's a major bug for us since we can't sort on anything joined.
Nahmis
 
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Postby AndreyR » Tue 27 Oct 2009 14:10

We have fixed the problem.
The fix will be included in the new Beta build of dotConnects.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Nahmis » Tue 27 Oct 2009 14:40

Excellent, if you can post when this build is available we'll be happy to test on our end and provide feedback.

Thanks for the prompt response!
Nahmis
 
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Postby AndreyR » Wed 28 Oct 2009 15:07

We plan to release the build this week.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle