I'm having troubles in the following situation:

I have the following entity model:

'ElementEntity' has an m-1 relation with 'ElementCategorieEntity', with a field mapped 'ElementCategorieen' to it.

'ActiviteitEntity' inherits from ElementEntity through a 1-1 relation between 'ElementEntity' and 'ActivityEntity'

Now I'm trying to execute the following query:

[TestMethod]
public void GetAlleActiviteitenVoorCategorie()
{
    using (IVersionableAdapter adapter = DataAccessAdapterFactory.GetDefault())
    {
        LinqMetaData linqMetaData = new LinqMetaData(adapter);
        
        var entities = linqMetaData
            .Activiteit.Where(pEntity => pEntity.ElementCategorieen.Any(
                pCategorieEntity => pCategorieEntity.CategorieId == Guid.Empty)
            ).ToList();

        Assert.AreEqual(1, entities.Count);
    }
}

I've overridden the following method in my DataAccessAdapter (query works fine without it)

protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
{
    // select the entity fetch to modify
    var entityName = fieldsToFetch
        .Cast<IEntityField2>()
        .Select(field => field.ContainingObjectName)
        .Distinct()
        .Last();

    if (entityName == "ActiviteitEntity")
        filter.AddWithAnd(ActiviteitFields.VersieStartdatum <= DateTime.Now);

    return base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);
}

Running the test with the code in the DataAccessAdapter enabled causes the following exception:

TestCase 'CC.ECDGenie.Zorg.Test.ElementenCatalogus.ElementenCatalogusTests.GetAlleActiviteitenVoorCategorie'
failed: System.NullReferenceException: Object reference not set to an instance of an object.
    at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateParameter(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, ParameterDirection direction, Object valueToSet)
    at SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareValuePredicate.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)
    at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)
    at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker)
    at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
    at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\DatabaseSpecific\DataAccessAdapter.User.cs(46,0): at CC.ECDGenie.Dal.DatabaseSpecific.DataAccessAdapter.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    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.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

So it seems like not all LINQ - PredicateExpression combinations can be used, is there maybe some workaround?

I'm using the June 6th final release of LLBLGen. ORMSupportClasses: 2.6.08.0606 DQE.SqlServer : 2.6.08.0606 LinqSupportClasses: 2.6.08.0606

This is the entire trace:

Method Enter: DataAccessAdapterBase.StartTransaction
:   Transaction name: ElementenCatalogusTests. Isolation level: Serializable.
Method Enter: DataAccessAdapterBase.OpenConnection
: New connection created.
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.StartTransaction
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[CC.ECDGenie.Dal.EntityClasses.ActiviteitEntity]).Where(pEntity => pEntity.ElementCategorieen.Any(pCategorieEntity => (pCategorieEntity.CategorieId = Guid.Empty)))
:
Method Enter: DataAccessAdapterBase.FetchEntityCollection(8)
Method Enter: DataAccessAdapterBase.FetchEntityCollectionInternal(7)
Active Entity Collection Description: 
    EntityCollection: CC.ECDGenie.Dal.HelperClasses.EntityCollection`1[[CC.ECDGenie.Dal.EntityClasses.ActiviteitEntity, CC.ECDGenie.Dal, Version=1.0.3089.27824, Culture=neutral, PublicKeyToken=null]].    Will contain entities of type: ActiviteitEntity

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_2].[element_id] AS [ElementId] FROM [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_CatalogusElementCategorie] [LPLA_2]  WHERE ( [LPA_L1].[id] = [LPLA_2].[element_id] AND ( [LPLA_2].[element_categorie_id] = @CategorieId1))
    Parameter: @CategorieId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 00000000-0000-0000-0000-000000000000.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
Method Enter: DataAccessAdapterBase.Rollback
Method Enter: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Rollback

Sorry for the long post!

I removed the linq trace at level 4, that's not really interesting .

The problem is that overriding that method is too late: you can't add a filter there, as everything is already handled: so adding a filter there requires you to also set the persistence infos of the fields. You don't do that, so the field in the predicate doesn't have a persistence info. So ActiviteitFields.VersieStartdatum has no persistence info Call GetPersistenceInfo(ActiviteitFields.VersieStartdatum) as well and pass that as persistenceinfo to the predicate.

Can't this predicate be done inside the linq query btw?

Otis wrote:

I removed the linq trace at level 4, that's not really interesting .

The problem is that overriding that method is too late: you can't add a filter there, as everything is already handled: so adding a filter there requires you to also set the persistence infos of the fields. You don't do that, so the field in the predicate doesn't have a persistence info. So ActiviteitFields.VersieStartdatum has no persistence info Call GetPersistenceInfo(ActiviteitFields.VersieStartdatum) as well and pass that as persistenceinfo to the predicate.

Can't this predicate be done inside the linq query btw?

Well, the expression is going to be a lot more complex, and should be included in each LINQ query and it's prefetch paths, and we have about 27 of these entities, so all the linq expressions would become quite huge and messy.

I didn't find an easy way to have reusable LINQ queries for entities exposing the same fields trough interfaces, because I can't use something similar to

linqMetaData.Cast<IVersionable>.Where()...

(I do understand why this wouldn't work and the fields can't be bound, but it would be useful to be able to do something like that). Because this doesn't work I would have to create the same reusable Expression<Func<EntityType,bool>> functions for each entity.

It would be really helpful if I could hack into this DataAccessAdapter for additional default predicates (because it works for all the prefetch paths as well).

I've tried the following:

filter.Add(new FieldCompareValuePredicate(ActiviteitFields.VersieStartdatum, GetFieldPersistenceInfo(ActiviteitFields.VersieStartdatum), ComparisonOperator.LessEqual, DateTime.Now));

but produces the result:

TestCase 'CC.ECDGenie.Zorg.Test.ElementenCatalogus.ElementenCatalogusTests.GetAlleActiviteitenVoorCategorie'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
    SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       --- End of inner exception stack trace ---
    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.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

I've also tried concatenating the fieldPersistenceInfos array with this field:

persistenceInfoObjects = persistenceInfoObjects.Concat(new[] { GetFieldPersistenceInfo(ActiviteitFields.VersieStartdatum) }).ToArray();

causing the following error:

TestCase 'CC.ECDGenie.Zorg.Test.ElementenCatalogus.ElementenCatalogusTests.GetAlleActiviteitenVoorCategorie'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: The length of the list of fields for the select list (13) isn't the same as the length of the list of persistence info objects passed in (14). This happens when using adapter the DBSpecific project is out of sync with the DBGeneric project.
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
    at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\DatabaseSpecific\DataAccessAdapter.User.cs(60,0): at CC.ECDGenie.Dal.DatabaseSpecific.DataAccessAdapter.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    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.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\CC.ECDGenie.Zorg.Test\ElementenCatalogus\ElementenCatalogusTests.cs(116,0): at CC.ECDGenie.Zorg.Test.ElementenCatalogus.ElementenCatalogusTests.GetAlleActiviteitenVoorCategorie()

So unfortunately no luck yet

Any other tips?

Thanks!

Wiebe

Also reusing the instances provided by the CreateSelectDQ to the fields and persistence info doesn't to work:

protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
{

    List<IEntityField2> entityFields = fieldsToFetch.Cast<IEntityField2>().ToList();

    var entityName = entityFields
        .Select(field => field.ContainingObjectName)
        .Distinct()
        .Last();
    
    if (entityName == "ActiviteitEntity")
    {
        //filter.AddWithAnd(ActiviteitFields.VersieStartdatum <= DateTime.Now);
        IEntityField2 versieStartdatumField = entityFields.Where(pField2 => pField2.Name == "VersieStartdatum").Single();
        IFieldPersistenceInfo versieStartdatumPersistenceInfo = persistenceInfoObjects[entityFields.IndexOf(versieStartdatumField)];
        filter.Add(new FieldCompareValuePredicate(versieStartdatumField, versieStartdatumPersistenceInfo, ComparisonOperator.LessEqual, DateTime.Now));
    }

    
    return base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);
}

Thought this might work:

public override void FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, int maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, int pageNumber, int pageSize)
{
    AddVersioning(collectionToFill, filterBucket);
    base.FetchEntityCollection(collectionToFill, filterBucket, maxNumberOfItemsToReturn, sortClauses, prefetchPath, excludedIncludedFields, pageNumber, pageSize);
}

protected override void FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
{
    AddVersioning(rootEntities, filterBucket);
    base.FetchPrefetchPath(rootEntities, filterBucket, maxNumberOfItemsToReturn, sortClauses, prefetchPath);
}

private static void AddVersioning(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket)
{
    Type entityType = collectionToFill
            .GetType()
            .GetGenericArguments()
            .Where(pType => typeof(IVersionable).IsAssignableFrom(pType))
            .SingleOrDefault();

    if(entityType!=null && typeof(ActiviteitEntity).IsAssignableFrom(entityType))
    {
        filterBucket.PredicateExpression.Add(ActiviteitFields.VersieStartdatum <= DateTime.Now);
    }
}

No success

TestCase 'CC.ECDGenie.Zorg.Test.ElementenCatalogus.ElementenCatalogusTests.GetAlleActiviteitenVoorCategorie'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
    SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       --- End of inner exception stack trace ---
    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.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\DatabaseSpecific\DataAccessAdapter.User.cs(52,0): at CC.ECDGenie.Dal.DatabaseSpecific.DataAccessAdapter.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\CC.ECDGenie.Zorg.Test\ElementenCatalogus\ElementenCatalogusTests.cs(116,0): at CC.ECDGenie.Zorg.Test.ElementenCatalogus.ElementenCatalogusTests.GetAlleActiviteitenVoorCategorie()

TestCase 'CC.ECDGenie.Zorg.Test.ElementenCatalogus.ElementenCatalogusTests.GetAlleActiviteitenVoorCategorie' failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Would you please post the generated SQL query?

Walaa wrote:

TestCase 'CC.ECDGenie.Zorg.Test.ElementenCatalogus.ElementenCatalogusTests.GetAlleActiviteitenVoorCategorie' failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Would you please post the generated SQL query?

Hi Walaa,

This is the last bit of the trace (after all the LINQ parsing traces):

Method Enter: DataAccessAdapterBase.FetchEntityCollection(8)
Method Enter: DataAccessAdapterBase.FetchEntityCollectionInternal(7)
Active Entity Collection Description: 
    EntityCollection: CC.ECDGenie.Dal.HelperClasses.EntityCollection`1[[CC.ECDGenie.Dal.EntityClasses.ActiviteitEntity, CC.ECDGenie.Dal, Version=1.0.3090.18405, Culture=neutral, PublicKeyToken=null]].    Will contain entities of type: ActiviteitEntity

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_2].[element_id] AS [ElementId] FROM [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_CatalogusElementCategorie] [LPLA_2]  WHERE ( [LPA_L1].[id] = [LPLA_2].[element_id] AND ( [LPLA_2].[element_categorie_id] = @CategorieId1))
    Parameter: @CategorieId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 00000000-0000-0000-0000-000000000000.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT DISTINCT [LPA_L1].[id] AS [F3_0], [LPA_L1].[naam] AS [F3_1], [LPA_L1].[versie_startdatum] AS [F3_2], [LPA_L1].[omschrijving] AS [F3_3], [LPA_L1].[creator_account_id] AS [F3_4], [LPA_L1].[versie_einddatum] AS [F3_5], [LPA_L1].[versie_nummer] AS [F3_6], [LPA_L1].[versie_object_id] AS [F3_7], [LPA_L2].[id] AS [F1_8], [LPA_L2].[is_afdelings_overschrijdend] AS [F1_9], [LPA_L2].[financierings_methode_id] AS [F1_10], [LPA_L2].[minimale_functie_id] AS [F1_11], [LPA_L2].[is_plan_prioriteit] AS [F1_12] FROM ( [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_Element] [LPA_L1]  INNER JOIN [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_Activiteit] [LPA_L2]  ON  [LPA_L1].[id]=[LPA_L2].[id]) WHERE ( ( (  EXISTS (SELECT [LPLA_2].[element_id] AS [ElementId] FROM [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_CatalogusElementCategorie] [LPLA_2]  WHERE ( [LPA_L1].[id] = [LPLA_2].[element_id] AND ( [LPLA_2].[element_categorie_id] = @CategorieId1)))) AND [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_Element].[versie_startdatum] <= @VersieStartdatum2) AND ( [LPA_L2].[id] IS NOT NULL))
    Parameter: @CategorieId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 00000000-0000-0000-0000-000000000000.
    Parameter: @VersieStartdatum2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 17-6-2008 11:15:14.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
Method Enter: DataAccessAdapterBase.OpenConnection
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
Method Enter: DataAccessAdapterBase.Rollback
Method Enter: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Reset
Method Exit: DataAccessAdapterBase.Rollback
TestCase 'CC.ECDGenie.Zorg.Test.ElementenCatalogus.ElementenCatalogusTests.GetAlleActiviteitenVoorCategorie'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
    SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: The multi-part identifier "DEV_CC_ECDGenie.dbo.tb_ECDGenie_Element.versie_startdatum" could not be bound.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       --- End of inner exception stack trace ---
    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.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\DatabaseSpecific\DataAccessAdapter.User.cs(52,0): at CC.ECDGenie.Dal.DatabaseSpecific.DataAccessAdapter.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

The problem is that you want to add predicates which refer to elements elsewhere in the query which got an alias in the linq query. So you have to hunt down these aliases as well, which is undoable, as you might run into a situation where you can't decide which one to pick.

The question of course is: what exactly do you want to add to the query which has to be re-used? Are these filters actually filters which have to be added transparently, so without the developer's notice? Or are these filters simply filters which have to be used a lot? Because you could try using .Where extension methods applied to already created queries: var q1 = from ....

var q2 = q1.Where(... );

Otis wrote:

The problem is that you want to add predicates which refer to elements elsewhere in the query which got an alias in the linq query. So you have to hunt down these aliases as well, which is undoable, as you might run into a situation where you can't decide which one to pick.

The question of course is: what exactly do you want to add to the query which has to be re-used? Are these filters actually filters which have to be added transparently, so without the developer's notice? Or are these filters simply filters which have to be used a lot? Because you could try using .Where extension methods applied to already created queries: var q1 = from ....

var q2 = q1.Where(... );

Well, preferably without the Developers notice.

Chaining linq queries works fine, however we have about 30 entities with a similar signature (StartDate, EndDate, etc), which means we have to create 30 queries * the possible combinations of those queries, while predicates are easily constructed in 1 place for multiple entity types, opposed to dynamically creating linq queries.

We can reuse LINQ queries like this as well:

        private static Expression<Func<ActiviteitEntity,bool>> ActiveOnDate(DateTime dateTime)
        {
            return activiteit => (activiteit.VersieEinddatum == null
                                  || activiteit.VersieEinddatum.Value > dateTime) &&
                                 activiteit.VersieStartdatum.Value <= dateTime;
        }

        public ActiviteitEntity GetActiveActiviteitByObjectId(Guid pObjectId, DateTime dateTime)
        {
            return this.DataContext.Activiteit
                .Where(ActiveOnDate(dateTime))
                .Where(pEntity => pEntity.VersieObjectId == pObjectId).First();
        }

However the c# compiler allows the following because it's possible in-memory, but unfortunately LLBLGen doesn't know how to handle this (because obviously the interface members don't have to be implemented by the actual entity properties, so they can never be bound to fields in the database):

        private static Expression<Func<IVersionable,bool>> ActiveOnDate(DateTime dateTime)
        {
            return versionable => (versionable.VersieEinddatum == null
                                  || versionable.VersieEinddatum.Value > dateTime) &&
                                 versionable.VersieStartdatum.Value <= dateTime;
        }

        public ActiviteitEntity GetActiveActiviteitByObjectId(Guid pObjectId, DateTime dateTime)
        {
            return this.DataContext.Activiteit.Cast<IVersionable>()
                .Where(ActiveOnDate(dateTime))
                .Cast<ActiviteitEntity>()
                .Where(pEntity => pEntity.VersieObjectId == pObjectId).Single();
        }

I'm afraid we'll have to go with the first option, recreating all methods for all different entities, and including them in all prefetch paths (which can be reused as well in the same way).

p.s. for simple queries my hacking into the DataAccessAdapter works fine, and I understand why now, because with simple queries there's no automatic alias generated for the tables...

Thanks!

Wiebe