Using LLBLGen adapter framework 5.8, for unit testing I would like to ensure that no one adds a where clause to an IQueryable (LLBLGenProQuery<T>). Ignoring the merits and the fragility of this approach, is this possible using the QueryCreationManager and CreateSelectDQ maybe? I know this is possible using the PredicateExpression class:

        public string GetQuery(PredicateExpression filter)
        {
            InsertPersistenceInfoObjects(filter);
            filter.DatabaseSpecificCreator = daa.GetDbSpecificCreatorInstance();
            return filter.ToQueryText();
        }

but not how to do it from an IQueryable/LLBLGenProQuery. I.e. go from LLBLGenProQuery to a the SQL string for the whole query or just the where clause.

Thanks.

You have a where clause and that has to be kept with the query or you want to execute the query without any where clause? As that's not clear to me from your question. (like, you have an IQueryable and it might have a where clause and you want to prevent another where to be appended, vs. you have an IQueryable without a where clause and you want to keep it that way)

If you want to see what sql / commands are created during a test, you can use this:

public class CommandInterceptorDataAccessAdapter : DataAccessAdapter
{
    private CommandTrackingQueryCreationManager _queryCreationManager;
    
    /// <summary>CTor</summary>
    public CommandInterceptorDataAccessAdapter()
    {
    }


    /// <summary>CTor</summary>
    /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
    public CommandInterceptorDataAccessAdapter(bool keepConnectionOpen) : base(keepConnectionOpen)
    {
    }


    /// <summary>CTor</summary>
    /// <param name="connectionString">The connection string to use when connecting to the database.</param>
    public CommandInterceptorDataAccessAdapter(string connectionString) : base(connectionString)
    {
    }


    /// <summary>CTor</summary>
    /// <param name="connectionString">The connection string to use when connecting to the database.</param>
    /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
    public CommandInterceptorDataAccessAdapter(string connectionString, bool keepConnectionOpen) : base(connectionString, keepConnectionOpen)
    {
    }


    /// <summary>CTor.</summary>
    /// <param name="connectionString">The connection string to use when connecting to the database.</param>
    /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
    /// <param name="catalogNameUsageSetting"> Configures this data access adapter object how to threat catalog names in persistence information.</param>
    /// <param name="catalogNameToUse"> The name to use if catalogNameUsageSetting is set to ForceName. Ignored otherwise.</param>
    /// <remarks>For backwards compatibility.</remarks>
    public CommandInterceptorDataAccessAdapter(string connectionString, bool keepConnectionOpen, CatalogNameUsage catalogNameUsageSetting, string catalogNameToUse) : base(connectionString, keepConnectionOpen, catalogNameUsageSetting, catalogNameToUse)
    {
    }


    /// <summary>CTor</summary>
    /// <param name="connectionString">The connection string to use when connecting to the database.</param>
    /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
    /// <param name="schemaNameUsageSetting">Configures this data access adapter object how to threat schema names in persistence information.</param>
    /// <param name="schemaNameToUse">Oracle specific. The name to use if schemaNameUsageSetting is set to ForceName. Ignored otherwise.</param>
    public CommandInterceptorDataAccessAdapter(string connectionString, bool keepConnectionOpen, SchemaNameUsage schemaNameUsageSetting, string schemaNameToUse) : base(connectionString, keepConnectionOpen, schemaNameUsageSetting, schemaNameToUse)
    {
    }


    /// <summary>CTor.</summary>
    /// <param name="connectionString">The connection string to use when connecting to the database.</param>
    /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
    /// <param name="catalogNameOverwrites"> The from-to name value pairs and setting for the overwriting of catalog names. Can be null.</param>
    /// <param name="schemaNameOverwrites"> The from-to name value pairs and setting for the overwriting of schema names. Can be null.</param>
    public CommandInterceptorDataAccessAdapter(string connectionString, bool keepConnectionOpen, CatalogNameOverwriteHashtable catalogNameOverwrites, SchemaNameOverwriteHashtable schemaNameOverwrites) : base(connectionString, keepConnectionOpen, catalogNameOverwrites, schemaNameOverwrites)
    {
    }


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

    /// <summary>
    /// Gets the last generated command.
    /// </summary>
    public DbCommand LastGeneratedCommand
    {
        get
        {
            if(_queryCreationManager.GeneratedCommands == null)
            {
                return null;
            }
            return _queryCreationManager.GeneratedCommands.Last();
        }
    }
    
    
    public List<DbCommand> GeneratedCommands => _queryCreationManager.GeneratedCommands;
}

public class CommandTrackingQueryCreationManager : QueryCreationManager
{
    public CommandTrackingQueryCreationManager(DataAccessAdapterCore containingAdapter, IPersistenceInfoProvider persistenceInfoProvider) 
        : base(containingAdapter, persistenceInfoProvider)
    {}
    
    
    protected override IRetrievalQuery CreateSelectDQ(QueryParameters parameters)
    {
        if(this.GeneratedCommands == null)
        {
            this.GeneratedCommands = new List<DbCommand>();
        }
        var toReturn = base.CreateSelectDQ(parameters);
        this.GeneratedCommands.Add(toReturn.Command);
        return toReturn;
    }
    

    public List<DbCommand> GeneratedCommands { get; private set; }
}

this adapter class executes the query as-is and also collects all DbCommands created, so with e.g. nested queries all are there in GeneratedCommands.

Thanks for that class, I might be able to use that, but if possible I'd like to avoid hitting a real database in the test.

I have this class

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Reflection;
using System.Text;
using SD.LLBLGen.Pro.LinqSupportClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.ORMSupportClasses.Adapter;

namespace TheHub.Entities.Extra
{
    public class QueryCreationManagerEx : QueryCreationManager
    {
        DataAccessAdapterCore daa;

        public QueryCreationManagerEx(DataAccessAdapterCore containingAdapter, IPersistenceInfoProvider persistenceInfoProvider) : base(containingAdapter, persistenceInfoProvider)
        {
            daa = containingAdapter;
        }

        public string GetQuery(PredicateExpression filter)
        {
            InsertPersistenceInfoObjects(filter);
            filter.DatabaseSpecificCreator = daa.GetDbSpecificCreatorInstance();
            return filter.ToQueryText();
        }

        public string GetQuery<T>(LLBLGenProQuery<T> query)
        {
            return ....;
        }
    }
}

Where you can see the code for turning a PredicateExpression into a string, and below that is what I'm trying to achieve. Overload GetQuery so that I can pass either a PredicateExpression or a LLBLGenProQuery<T>. Doesn't have to be done with a QueryCreationManager, that's just how I did it for PredicateExpression.

The IQueryable has to go through the linq provider first, then it ends up in e.g. FetchEntityCollection or FetchEntityCollectionAsync (or one of the projection methods) which then create the query and execute it using e.g. ExecuteMultiRowRetrievalQuery/ExecuteSingleRowRetrievalQuery.

But, they all work with the IRetrievalQuery object created in the class I gave you. So I think the 'easiest' is to wrap the IRetrievalQuery object you get from var toReturn = base.CreateSelectDQ(parameters); in CreateSelectDQ in the class I gave you in your own IRetrievalQuery implementing class and when one of the execute methods is called, simply return. Would that work?

That's it! Thanks.

I have this DataAccessAdapter and associated mocks:

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.ORMSupportClasses.Adapter;
using TheHub.Entities.DatabaseSpecific;

namespace ServerObjects.Data.Tests.LLCoolJ
{
    public class TestingDataAccessAdapter : DataAccessAdapter
    {
        public TestingDataAccessAdapter(string connectionString) : base(connectionString)
        {
        }

        public string LastQueryText { get; private set; }

        protected override QueryCreationManager CreateQueryCreationManager(IPersistenceInfoProvider persistenceInfoProvider)
        {
            return new TestingQueryCreationManager(this, persistenceInfoProvider);
        }

        public class TestingQueryCreationManager : QueryCreationManager
        {
            readonly TestingDataAccessAdapter containingAdapter;

            public TestingQueryCreationManager(DataAccessAdapterCore containingAdapter, IPersistenceInfoProvider persistenceInfoProvider) : base(containingAdapter, persistenceInfoProvider)
            {
                this.containingAdapter = (TestingDataAccessAdapter)containingAdapter;
            }

            protected override IRetrievalQuery CreateSelectDQ(QueryParameters queryParameters)
            {
                var query = base.CreateSelectDQ(queryParameters);
                containingAdapter.LastQueryText = query.ToString();
                return new NullRetrievalQuery(query);
            }

            public class NullRetrievalQuery : IRetrievalQuery
            {
                readonly IRetrievalQuery query;

                public NullRetrievalQuery(IRetrievalQuery query)
                {
                    this.query = query;
                }

                public void Dispose()
                {
                    query.Dispose();
                }

                public void AddParameter(DbParameter parameterToAdd)
                {
                    query.AddParameter(parameterToAdd);
                }

                public void AddParameters(IList parametersToAdd)
                {
                    query.AddParameters(parametersToAdd);
                }

                public void AddOutputParameterValueForSync(ParameterValue valueAsParameterValue)
                {
                    query.AddOutputParameterValueForSync(valueAsParameterValue);
                }

                public IParameterFieldRelation AddParameterFieldRelation(IEntityFieldCore field, DbParameter parameter, TypeConverter typeConverterToUse)
                {
                    return query.AddParameterFieldRelation(field, parameter, typeConverterToUse);
                }

                public IParameterFieldRelation AddParameterFieldRelation(IEntityFieldCore field, DbParameter parameter, TypeConverter typeConverterToUse, bool parameterValueCanBeNull)
                {
                    return query.AddParameterFieldRelation(field, parameter, typeConverterToUse, parameterValueCanBeNull);
                }

                public void ReflectOutputValuesInRelatedFields()
                {
                    query.ReflectOutputValuesInRelatedFields();
                }

                public void ReflectOutputValuesInParameterValues()
                {
                    query.ReflectOutputValuesInParameterValues();
                }

                public void WireTransaction(DbTransaction transactionToWire)
                {
                    query.WireTransaction(transactionToWire);
                }

                public void SetCommandTimeout(int timeoutInterval)
                {
                    query.SetCommandTimeout(timeoutInterval);
                }

                public void SetCommandText(string commandText)
                {
                    query.SetCommandText(commandText);
                }

                public DbConnection Connection
                {
                    get => query.Connection;
                    set => query.Connection = value;
                }

                public DbCommand Command
                {
                    get => query.Command;
                    set => query.Command = value;
                }

                public IList Parameters { get => query.Parameters; }
                public List<IParameterFieldRelation> ParameterFieldRelations { get => query.ParameterFieldRelations; }

                public Task<DbDataReader> ExecuteAsync(CommandBehavior behavior, CancellationToken cancellationToken)
                {
                    throw new NotImplementedException();
                }

                public Task<object> ExecuteScalarAsync(CancellationToken cancellationToken)
                {
                    throw new NotImplementedException();
                }

                public DbDataReader Execute(CommandBehavior behavior)
                {
                    return new TestingDbDataReader();
                }

                public class TestingDbDataReader : DbDataReader
                {
                    public override string GetName(int ordinal) => throw new NotImplementedException();

                    public override int GetValues(object[] values) => throw new NotImplementedException();

                    public override bool IsDBNull(int ordinal) => throw new NotImplementedException();

                    public override int FieldCount { get; }

                    public override object this[int ordinal] => throw new NotImplementedException();

                    public override object this[string name] => throw new NotImplementedException();

                    public override bool HasRows { get; }
                    public override bool IsClosed { get; }
                    public override int RecordsAffected { get; }

                    public override bool NextResult() => throw new NotImplementedException();

                    public override bool Read() => false;

                    public override int Depth { get; }

                    public override int GetOrdinal(string name) => throw new NotImplementedException();

                    public override bool GetBoolean(int ordinal) => throw new NotImplementedException();

                    public override byte GetByte(int ordinal) => throw new NotImplementedException();

                    public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length) => throw new NotImplementedException();

                    public override char GetChar(int ordinal) => throw new NotImplementedException();

                    public override long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length) => throw new NotImplementedException();

                    public override Guid GetGuid(int ordinal) => throw new NotImplementedException();

                    public override short GetInt16(int ordinal) => throw new NotImplementedException();

                    public override int GetInt32(int ordinal) => throw new NotImplementedException();

                    public override long GetInt64(int ordinal) => throw new NotImplementedException();

                    public override DateTime GetDateTime(int ordinal) => throw new NotImplementedException();

                    public override string GetString(int ordinal) => throw new NotImplementedException();

                    public override decimal GetDecimal(int ordinal) => throw new NotImplementedException();

                    public override double GetDouble(int ordinal) => throw new NotImplementedException();

                    public override float GetFloat(int ordinal) => throw new NotImplementedException();

                    public override string GetDataTypeName(int ordinal) => throw new NotImplementedException();

                    public override Type GetFieldType(int ordinal) => throw new NotImplementedException();

                    public override object GetValue(int ordinal) => throw new NotImplementedException();

                    public override IEnumerator GetEnumerator() => throw new NotImplementedException();
                }

                public object ExecuteScalar()
                {
                    return null;
                }

                public void CacheCurrentRow()
                {
                    query.CacheCurrentRow();
                }

                public void ReadComplete()
                {
                    query.ReadComplete();
                }

                public bool RequiresClientSideLimitation { get; set; }
                public bool RequiresClientSideDistinctFiltering { get; set; }
                public bool RequiresClientSidePaging { get; set; }
                public int ManualRowsToSkip { get; set; }
                public int ManualRowsToTake { get; set; }
                public int ResultsetNumber { get; set; }
                public bool CacheResultset { get; set; }
                public string CacheTag { get; set; }
                public string QueryTag { get; set; }
                public TimeSpan CacheDuration { get; set; }
                public bool OverwriteIfPresent { get; set; }
                public bool NoNameOverwriting { get; set; }
            }
        }
    }
}

And then I can write a test function:

        static bool dqeConfigured;
        bool HasWhereFilterFor<T>(IQueryable<T> q, EntityField2 entityField2)
        {
            if (!dqeConfigured) // TODO thread lock?
            {
                RuntimeConfiguration.ConfigureDQE<SQLServerDQEConfiguration>(
                    c => c.SetTraceLevel(TraceLevel.Off)
                        .AddDbProviderFactory(typeof(System.Data.SqlClient.SqlClientFactory))
                        .AddCatalogNameOverwrite("*", "")
                        .SetDefaultCompatibilityLevel(SqlServerCompatibilityLevel.SqlServer2012));
                dqeConfigured = true;
            }

            // TODO 
            TransactionScopeWrapperBase.SetDefaultConnectionString(TransactionScopeWrapperBase.Connections.Debug);
            var daa = new TestingDataAccessAdapter(TransactionScopeWrapperBase.DefaultConnectionString);

            ((LLBLGenProProvider2)q.Provider).AdapterToUse = daa;
            ((ILLBLGenProQuery)q).Execute<IEnumerable<T>>();

            var query = daa.LastQueryText;
            
            // TOOD: better parsing, group by/having/table name qualification
            var whereStarts = query.IndexOf("where", StringComparison.OrdinalIgnoreCase);
            if (whereStarts == -1) return false;

            return query.Substring(whereStarts).IndexOf("[" + entityField2.Name + "]", StringComparison.OrdinalIgnoreCase) > -1;
        }

great! Thanks for sharing!