Hi, we've been running on 5.4 for years and we're looking to renew our licence and move to 5.9. I've downloaded the trial for 5.9 to test it out.

We currently have 2 custom query engines to workaround legacy issues where we couldn't use NOLOCK or FOR SYSTEM_TIME (Temporal Tables). Each engine is used by a respective adapter: DateEffectiveDataAccessAdapter and NoLockDataAccessAdapter, which both override CreateDynamicQueryEngine and return our engine instead.

However, on upgrade I can't work out how to fix our existing classes. There also doesn't appear to be any way of using QueryHints unless you use QuerySpec/LINQ unless I'm mistaken?

Our existing date effective class

    public class DateEffectiveDynamicQueryEngine: DynamicQueryEngine
    {
        private readonly DateTime? _effectiveDate;
        private readonly DateTime? _endDate;
        private readonly DateEffectiveMode _mode;

        public DateEffectiveDynamicQueryEngine(DateEffectiveMode mode, DateTime? effectiveDate, DateTime? endDate)
            :base()
        {
            _effectiveDate = effectiveDate;
            _endDate = endDate;
            _mode = mode;
        }

        protected override void CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, bool relationsSpecified, bool sortClausesSpecified)
        {
            // Ensure that if there's a chain of relations we append the temporal table directive to the last in the chain
            List<string> aliases = new List<string>();
            if (relationsToWalk.Count > 0)
            {
                for (int i = 0; i < relationsToWalk.Count; i++)
                {
                    aliases.Add(relationsToWalk[i].HintTargetNameRightOperand);
                    aliases.Add(relationsToWalk[i].HintTargetNameLeftOperand);
                }
            }
            else
            {
                aliases.Add(null);
            }

            foreach (string alias in aliases.Distinct())
            {
                FromClauseElementDirective directive = new FromClauseElementDirective();
                directive.DirectiveType = FromClauseElementDirectiveType.SystemTimeTemporalTablePredicate;

                switch (_mode)
                {
                    case DateEffectiveMode.AsOf:
                        directive.Directive = String.Format("as of '{0}'", _effectiveDate.Value.ToString("O"));
                        break;
                    case DateEffectiveMode.BetweenAnd:
                        directive.Directive = String.Format("between '{0}' and ' {1}", _effectiveDate.Value.ToString("O"), _endDate.Value.ToString("O"));
                        break;
                    case DateEffectiveMode.FromTo:
                        directive.Directive = String.Format("from '{0}' to ' {1}", _effectiveDate.Value.ToString("O"), _endDate.Value.ToString("O"));
                        break;
                    case DateEffectiveMode.All:
                        directive.Directive = String.Format("All");
                        break;
                }

                directive.Alias = alias;
                relationsToWalk.AddFromClauseDirectives(new FromClauseElementDirective[] { directive });
            }

            base.CreateSelectDQ(selectList, fieldsPersistenceInfo, query, selectFilter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, relationsSpecified, sortClausesSpecified);
        }
    }

Our current NOLOCK class

    public class NoLockDynamicQueryEngine : DynamicQueryEngine
    {
        protected override void CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, bool relationsSpecified, bool sortClausesSpecified)
        {
            // Ensure that if there's a chain of relations we append the temporal table directive to the last in the chain
            List<string> aliases = new List<string>();
            if (relationsToWalk.Count > 0)
            {
                for (int i = 0; i < relationsToWalk.Count; i++)
                {
                    aliases.Add(relationsToWalk[i].HintTargetNameRightOperand);
                    aliases.Add(relationsToWalk[i].HintTargetNameLeftOperand);
                }
            }
            else
            {
                aliases.Add(null);
            }

            foreach (string alias in aliases.Distinct())
            {
                FromClauseElementDirective directive = new FromClauseElementDirective
                {
                    Alias = alias,
                    Directive = "NOLOCK"
                };

                relationsToWalk.AddFromClauseDirectives(new FromClauseElementDirective[] { directive });
            }

            base.CreateSelectDQ(selectList, fieldsPersistenceInfo, query, selectFilter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, relationsSpecified, sortClausesSpecified);
        }
    }

So how can I use NOLOCK hints in a query or use FOR SYSTEM_TIME using Adapter? Or how can I amend the above classes?

Also, I see that we can no longer use GetFieldPersistenceInfo from our partial classes of DataAccessAdapter. The migration notes say that we have to create a derived class from QueryCreationManager like this:

    public class CustomQueryCreationManager : QueryCreationManager
    {
        public CustomQueryCreationManager(DataAccessAdapterCore containingAdapter, IPersistenceInfoProvider persistenceInfoProvider) 
            : base(containingAdapter, persistenceInfoProvider)
        {
        }

        public IFieldPersistenceInfo Foo(IFieldInfo fieldInfo)
        {
            return GetFieldPersistenceInfo(fieldInfo);
        }
    }

But then how do you actually call CreateQueryCreationManager from the DataAccessAdapter, because it needs an IPersistenceInfoProvider? These are the methods that are currently failing to build, so even if I override CreateQueryCreationManager as suggested, I will still need to call it.

        public string GetFieldName(IEntityField2 field)
        {
            IFieldPersistenceInfo i = GetFieldPersistenceInfo(field);
            return i.SourceColumnName;
        }
        public string GetTableName(IEntityField2 field)
        {
            IFieldPersistenceInfo i = GetFieldPersistenceInfo(field);
            return i.SourceObjectName;
        }

i.e. What goes in place of "SOMETHING" here?

        public string GetFieldName(IEntityField2 field)
        {
            var foo = CreateQueryCreationManager(SOMETHING ??);

            IFieldPersistenceInfo i = ((CustomQueryCreationManager)foo).Foo(field);
            return i.SourceColumnName;
        }

OK, I've worked it all out. Please let me know if there are any better ways of doing what I've done below, i.e. by using your query hints and not having to crowbar it in like we have here:

Date effective:

    public class DateEffectiveDynamicQueryEngine: DynamicQueryEngine
    {
        private readonly DateTime? _effectiveDate;
        private readonly DateTime? _endDate;
        private readonly DateEffectiveMode _mode;

        public DateEffectiveDynamicQueryEngine(DateEffectiveMode mode, DateTime? effectiveDate, DateTime? endDate)
            :base()
        {
            _effectiveDate = effectiveDate;
            _endDate = endDate;
            _mode = mode;
        }

        protected override IRetrievalQuery CreateSelectDQImpl(QueryParameters parameters, DbConnection connectionToUse, bool emitQueryHints = true)
        {
            // Ensure that if there's a chain of relations we append the temporal table directive to the last in the chain
            IRelationCollection relationsToWalk = parameters.RelationsToUse;

            List<string> aliases = new List<string>();
            if (relationsToWalk.Count > 0)
            {
                for (int i = 0; i < relationsToWalk.Count; i++)
                {
                    aliases.Add(relationsToWalk[i].HintTargetNameRightOperand);
                    aliases.Add(relationsToWalk[i].HintTargetNameLeftOperand);
                }
            }
            else
            {
                aliases.Add(null);
            }

            foreach (string alias in aliases.Distinct())
            {
                FromClauseElementDirective directive = new FromClauseElementDirective();
                directive.DirectiveType = FromClauseElementDirectiveType.SystemTimeTemporalTablePredicate;

                switch (_mode)
                {
                    case DateEffectiveMode.AsOf:
                        directive.Directive = String.Format("as of '{0}'", _effectiveDate.Value.ToString("O"));
                        break;
                    case DateEffectiveMode.BetweenAnd:
                        directive.Directive = String.Format("between '{0}' and ' {1}", _effectiveDate.Value.ToString("O"), _endDate.Value.ToString("O"));
                        break;
                    case DateEffectiveMode.FromTo:
                        directive.Directive = String.Format("from '{0}' to ' {1}", _effectiveDate.Value.ToString("O"), _endDate.Value.ToString("O"));
                        break;
                    case DateEffectiveMode.All:
                        directive.Directive = String.Format("All");
                        break;
                }

                directive.Alias = alias;
                relationsToWalk.AddFromClauseDirectives(new FromClauseElementDirective[] { directive });
            }

            return base.CreateSelectDQImpl(parameters, connectionToUse, emitQueryHints);
        }
    }

No lock:

    public class NoLockDynamicQueryEngine : DynamicQueryEngine
    {

        protected override IRetrievalQuery CreateSelectDQImpl(QueryParameters parameters, DbConnection connectionToUse, bool emitQueryHints = true)
        {
            // Ensure that if there's a chain of relations we append the temporal table directive to the last in the chain
            IRelationCollection relationsToWalk = parameters.RelationsToUse;

            List<string> aliases = new List<string>();
            if (relationsToWalk.Count > 0)
            {
                for (int i = 0; i < relationsToWalk.Count; i++)
                {
                    aliases.Add(relationsToWalk[i].HintTargetNameRightOperand);
                    aliases.Add(relationsToWalk[i].HintTargetNameLeftOperand);
                }
            }
            else
            {
                aliases.Add(null);
            }

            foreach (string alias in aliases.Distinct())
            {
                FromClauseElementDirective directive = new FromClauseElementDirective
                {
                    Alias = alias,
                    Directive = "NOLOCK"
                };

                relationsToWalk.AddFromClauseDirectives(new FromClauseElementDirective[] { directive });
            }

            return base.CreateSelectDQImpl(parameters, connectionToUse, emitQueryHints);
        }
    }

And IFieldPersistenceInfo:

    public class CustomQueryCreationManager : QueryCreationManager
    {
        public CustomQueryCreationManager(DataAccessAdapterCore containingAdapter, IPersistenceInfoProvider persistenceInfoProvider) 
            : base(containingAdapter, persistenceInfoProvider)
        {
        }

        public IFieldPersistenceInfo Foo(IFieldInfo fieldInfo)
        {
            return GetFieldPersistenceInfo(fieldInfo);
        }
    }

        private CustomQueryCreationManager _queryCreationManager;

        protected override QueryCreationManager CreateQueryCreationManager(IPersistenceInfoProvider persistenceInfoProvider)
        {
            _queryCreationManager = new CustomQueryCreationManager(this, persistenceInfoProvider);
            return _queryCreationManager;
        }

        public string GetFieldName(IEntityField2 field)
        {
            IFieldPersistenceInfo i = _queryCreationManager.Foo(field);
            return i.SourceColumnName;
        }
        public string GetTableName(IEntityField2 field)
        {
            IFieldPersistenceInfo i = _queryCreationManager.Foo(field);
            return i.SourceObjectName;
        }

For the temporal table predicates, the 'easiest' is to rewrite queries using them as queryspec queries and simply use the extension method. It's not as simple as appending things to a predicate as the target entity might end up as a derived table, a join etc. This is been taken care of by the queryspec handlers. I understand having 100s of queries using these might result in a lot of work so it's a balance if it's worth the trouble or keeping the changes you made around.

Another way to do this is by overriding CreateSelectDQ in the querycreationmanager. It receives a QueryParameters object which contains everything to produce the query. This way you can augment the elements for e.g. joins and the like with the temporal table code you already have, which frees you from altering the DQE. I think the code you end up with this way might be a bit simpler (as you already have to create a custom querycreationmanager anyway)

NOLOCK and temporal table predicates follow the same procedure, and are placed at the same location, so there's no easy way to append NOLOCK hints, they're assigned with the target which can be deep inside the query.