Bug - OrderBy with Children

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Nahmis
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Bug - OrderBy with Children

Post by 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  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  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

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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

Post by 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?

Nahmis
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Post by 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

Nahmis
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Update

Post by 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 27 Oct 2009 14:10

We have fixed the problem.
The fix will be included in the new Beta build of dotConnects.

Nahmis
Posts: 30
Joined: Mon 13 Jul 2009 21:38

Post by 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!

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 28 Oct 2009 15:07

We plan to release the build this week.

Post Reply