Devart Forums

Posted: **Sun 04 Oct 2009 11:17**

With the following Linq Query:

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

Posted: **Tue 06 Oct 2009 10:04**

We are investigating this issue. I will let you know about the results of our investigation.

Posted: **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?

Posted: **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.

Posted: **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.

Posted: **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.

Posted: **Tue 27 Oct 2009 14:10**

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

Posted: **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!

Posted: **Wed 28 Oct 2009 15:07**

We plan to release the build this week.

Devart Forums

Bug - OrderBy with Children

Bug - OrderBy with Children

Update