Adapter

I have just upgraded to 2.5 from 2.0 refeshed the catalogue and regenerated the code

adapter.FetchEntityCollection(jobs, bucket, maxCount, sorter, JobListPrefetchPath());

gives the following exception:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException occurred Message="An exception was caught during the execution of a retrieval query: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" RuntimeBuild="08312007" RuntimeVersion="2.5.0.0" QueryExecuted="\r\n\tQuery: SELECT [WaterWorksRC1].[dbo].[Address].[Id], [WaterWorksRC1].[dbo].[Address].[Basis2Id], [WaterWorksRC1].[dbo].[Address].[Address1], [WaterWorksRC1].[dbo].[Address].[Address2], [WaterWorksRC1].[dbo].[Address].[Address3], [WaterWorksRC1].[dbo].[Address].[Address4], [WaterWorksRC1].[dbo].[Address].[Address5], [WaterWorksRC1].[dbo].[Address].[Address6], [WaterWorksRC1].[dbo].[Address].[Address7], [WaterWorksRC1].[dbo].[Address].[Address8], [WaterWorksRC1].[dbo].[Address].[Postcode], [WaterWorksRC1].[dbo].[Address].[PropertyNumber1], [WaterWorksRC1].[dbo].[Address].[PropertyNumber2], [WaterWorksRC1].[dbo].[Address].[DisplayAddress], [WaterWorksRC1].[dbo].[Address].[LastUpdate], [WaterWorksRC1].[dbo].[Address].[Easting], [WaterWorksRC1].[dbo].[Address].[Northing], [WaterWorksRC1].[dbo].[Address].[Version] FROM [WaterWorksRC1].[dbo].[Address] WHERE ( [WaterWorksRC1].[dbo].[Address].[Id] IN (SELECT [WaterWorksRC1].[dbo].[Job].[WorkLocationFk] FROM ( [WaterWorksRC1].[dbo].[Customer] INNER JOIN [WaterWorksRC1].[dbo].[Job] ON [WaterWorksRC1].[dbo].[Customer].[Id]=[WaterWorksRC1].[dbo].[Job].[CustomerFk]) WHERE ( ( ( [WaterWorksRC1].[dbo].[Job].[ClosedAt] IS NULL AND [WaterWorksRC1].[dbo].[Job].[CancelledAt] IS NULL))) ORDER BY [WaterWorksRC1].[dbo].[Job].[Id] DESC))\r\n" StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath) at Cwc.WaterWorks.ServiceLayer.WaterWorksServiceLayer.GetJobsForList(JobFilter filter, Int32 maxCount) in C:\work\WorkManagement\RC1\WaterWorksServiceLayer\Job.cs:line 243

Seems like the inner select needs a TOP to work, but I can't see how to specify this other than via maxCount which is set to 100 for this call.

Setting the sorter to null prevents the error.

---- Also ----

    public static IPrefetchPath2 JobListPrefetchPath()
    {
        IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.JobEntity);

        IPrefetchPathElement2 prefetchJobType = prefetchPath.Add(JobEntity.PrefetchPathJobType);
        prefetchJobType.SubPath.Add(JobTypeEntity.PrefetchPathJobTypeCategory);

        prefetchPath.Add(JobEntity.PrefetchPathWorkLocation);
        prefetchPath.Add(JobEntity.PrefetchPathCustomer);

        return prefetchPath;
    }

If I specify a maxAmountOfItemsToReturn to one then it still crashed.

We had this issue reported to us last week, but we couldn't reproduce it and the user didn't respond anymore to the thread on the forum (helpdesk, so I can't link to it).

What we need from you is: - the code you used to execute the fetch. So the call to the FetchEntityCollection, and also if possible the code to setup the sorter etc. (edit): you seem to have provided the call, but not the code which sets all the parameters passed to the call, could you provide that code as well please? Also, your code which sets up the prefetch path doesn't refer to 'Address'. Is that 'worklocation' ?

I used this code to reproduce it (but this worked OK)

[Test]
public void PrefetchPathWithTopAndOrderBySubQueryTest()
{
    PrefetchPath2 path = new PrefetchPath2(EntityType.EmployeeEntity);
    path.Add(EmployeeEntity.PrefetchPathOrders);
    SortExpression sorter = new SortExpression(EmployeeFields.LastName | SortOperator.Ascending);
    EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>();

    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.ParameterisedPrefetchPathThreshold = 3;
        adapter.FetchEntityCollection(employees, null, 6, sorter, path);
    }

    Assert.AreEqual(6, employees.Count);
}

I set the threshold so the code is forced to produce a subquery.

SELECT [Northwind].[dbo].[Orders].[OrderID] AS [OrderId], 
...
FROM [Northwind].[dbo].[Orders]  
WHERE 
( [Northwind].[dbo].[Orders].[EmployeeID] IN 
(
    SELECT TOP 6 [Northwind].[dbo].[Employees].[EmployeeID] AS [EmployeeId] 
    FROM [Northwind].[dbo].[Employees]  ORDER BY [Northwind].[dbo].[Employees].[LastName] ASC
    )
)

        public EntityCollection<JobEntity> GetJobsForList(JobFilter filter, int maxCount)
        {
            EntityCollection<JobEntity> jobs;
            try
            {
                jobs = new EntityCollection<JobEntity>(new JobEntityFactory());
                RelationPredicateBucket bucket = new RelationPredicateBucket();

                switch(filter.StateFilter)
                {
                    case StateFilter.All:
                        {
                            break;
                        }
                    case StateFilter.Live:
                        {
                            bucket.PredicateExpression.Add(new FieldCompareNullPredicate(JobFields.ClosedAt, null));
                            bucket.PredicateExpression.Add(new FieldCompareNullPredicate(JobFields.CancelledAt, null));
                            break;
                        }
                    case StateFilter.Started:
                        {
                            bucket.PredicateExpression.Add(!new FieldCompareNullPredicate(JobFields.StartedAt, null));
                            break;
                        }
                    case StateFilter.Cancelled:
                        {
                            bucket.PredicateExpression.Add(!new FieldCompareNullPredicate(JobFields.CancelledAt, null));
                            break;
                        }
                    case StateFilter.Closed:
                        {
                            bucket.PredicateExpression.Add(!new FieldCompareNullPredicate(JobFields.ClosedAt, null));
                            break;
                        }
                    case StateFilter.Error:
                        {
                            FieldLikePredicate fieldLikePredicate = new FieldLikePredicate(JobFields.ReasonWaiting, null, "Action Error:%");
                            fieldLikePredicate.CaseSensitiveCollation = true;
                            bucket.PredicateExpression.Add(fieldLikePredicate);
                            break;
                        }
                }

[... extra filter code removed as it is not applicable..]

                Context tempContext = new Context();
                tempContext.Add(jobs);
                SortExpression sorter = new SortExpression(JobFields.Id | SortOperator.Descending);
                adapter.FetchEntityCollection(jobs, bucket, maxCount, sorter, JobListPrefetchPath());

                filter.TotalMatchesCount = adapter.GetDbCount(new JobEntityFactory().CreateFields(), bucket, null, false);
                filter.ReturnedMatchesCount = jobs.Count;

                databaseStatus.LastException = null;
            }
            catch (Exception ex)
            {
                databaseStatus.LastException = ex;
                logger.LogException("Error fetching jobs ", ex);
                jobs = null;
            }
            return jobs;
}

StateFilter is Live in this case and yes WorkLocation is Address here

Thanks Sam. The only reason this could occur is that the maxlimit is 0 and the sorter isn't at the time the subquery has to be created. This will lead to a sorter and no TOP clause. This can only happen if there's a bug in our code so I'll check if I can find out why the maxlimit is 0 at that spot. Likely due to already fetched prefetch path nodes, which isn't the case in my test.

Reproduced!

[Test]
public void PrefetchPathWithTopAndOrderBySubQueryTest()
{
    PrefetchPath2 path = new PrefetchPath2(EntityType.EmployeeEntity);
    path.Add(EmployeeEntity.PrefetchPathOrders).SubPath.Add(OrderEntity.PrefetchPathOrderDetails);
    path.Add(EmployeeEntity.PrefetchPathEmployeeTerritories);
    SortExpression sorter = new SortExpression(EmployeeFields.LastName | SortOperator.Ascending);
    EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>();
    RelationPredicateBucket filter = new RelationPredicateBucket();
    filter.Relations.Add(EmployeeEntity.Relations.RegionEntityUsingRegionId);

    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.ParameterisedPrefetchPathThreshold = 3;
        adapter.FetchEntityCollection(employees, filter, 6, sorter, path);
    }

    Assert.AreEqual(6, employees.Count);
}

It's the relations part which causes this. (you cut away that line, but it's in the sql) the relations could cause duplicates. Your Customer entity has 1 or more fields which don't allow 'DISTINCT'. (in my test, employee has an image field, which can't be used with distinct) so TOP has to be emitted with distinct. However, because TOP has to be emitted on a query which can lead to duplicates, TOP isn't emitted.

This leads to problems, because the order by IS emitted. Normally, this isn't a problem, but in a subquery, it is.

The code has to avoid emitting the order by if the top isn't emitted. I'll fix this. (It's also strange that TOP isn't emitted with DISTINCT, as the set itself contains just 1 field, which isn't an image field... )

yeah sorry I missed that bit!

bucket.Relations.Add(JobEntity.Relations.CustomerEntityUsingCustomerFk);

that wasn't supposed to be called as the filter that used it was off, if I put that inside the if where it was supposed to be then it works fine.

I found the issue. The sorter AND top are passed to the subquery. However, the sorter is perhaps on another field in the entity than the PK. The thing is though that in sqlserver, if DISTINCT is emitted, the sorter has to have all fields in the selectlist. If not, it will give an error (example: select DISTINCT top 10 orderid from orders order by customerid desc) The DQE checks for this and simply refuses to emit the DISTINCT keyword. But as I said above, if relations are specified and DISTINCT isn't specified, TOP could give wrong results.

So we're in a catch 22 here. This query:

select o.* from orders o
where employeeid in
(
    select DISTINCT TOP 4 e.employeeid, e.firstname
    from employees e
    order by e.firstname
)

isn't possible, as you can only select 1 column in the subquery, however you have to sort on another one to reproduce the same set of ID's as the initial root entity query (in your case 'jobs')

The solution for this is:

select o.* from orders o
where employeeid in
(
    select employeeid from
    (
        select DISTINCT TOP 4 e.employeeid, e.firstname
        from employees e
        order by e.firstname
    ) as tmp
)

However, that's not possible on several databases we support, as they don't support derived tables (the select in the from clause). So the issue would be unsolved for these db's if this was added...

What a pain... This TOP & order by optimization was added in v2.5 to avoid the situation where for example you have 1000 employees which filed 10,000 orders and you were just fetching the top 4 as in the above query, so you therefore would only need much less orders to fetch than all of them.

I hope to find a solution for this soon, otherwise I'll switch off this optimization for now.

Btw, to work around it for now, set the ParameterizedPrefetchPathThreshold to 100 at least, it will create parameterized queries instead of a subquery.

(edit) it's not solvable without making a lot of changes. So we'll switch off this optimization in the next build. This will lead to v2.0 behavior for these particular queries. (other optimizations are still performed for prefetch paths)

OK thanks for the quick response Otis, sorry to be the cause of any pain....

SamMackrill wrote:

OK thanks for the quick response Otis, sorry to be the cause of any pain....

No problem, it's a complex sql issue that's the root cause.

We have some ideas how to solve this but it would require api changes etc. so we've postponed the fix (the optimization which works) till v2.6 which is the service release with solely fixes which would break code and Linq support. Till then, we'll switch off the optimization.

Sorry to be dim but please can you tell me where to set ParameterizedPrefetchPathThreshold, your docs reference it but don't say how to set it.

Cheers!

It's a property of the DataAccessAdapter.

(Note: The current build has the v2.0 behavior back, so you may also use it).