Build Version: 5.5 (5.5.0) RTM, Build Date: 02-Nov-2018 Project: Adapter project targetting .NET 4.5.2 Database: MS SQL 2016

We have 2 tables in our database, invoice, and invoice detail. A single invoice can have multiple details, but a given detail will only ever have one invoice. In our solution, we have a derived model (DTO Class Model) that has an Invoice derived element where we have included the invoice detail. We have the designer set up to add Model to the end of the derived element class names, so the Invoice derived element is named InvoiceEntityModel. We typically use Queryables based off of the runtime framework entities to build our database queries.

Trying to filter invoices with the following example code throws an error:

private static void ProjectionErrorExample(IQueryable<InvoiceEntity> queryable)
{
    var projected = queryable.ProjectToInvoiceEntityModel();
    var projectedResult = projected
            .Where(x => x.InvoiceDetails.Any(y => y.OriginLocationId == 2272))
            .FirstOrDefault();
}

The Error:

Test method Gravitate.Tests.Integration.GravitateServicesTests.TestProjectionError threw exception: 
System.NullReferenceException: Object reference not set to an instance of an object.
    at SD.LLBLGen.Pro.ORMSupportClasses.EntityNameFinder.Traverse(IEntityFieldCore field)
   at SD.LLBLGen.Pro.ORMSupportClasses.QueryApiObjectTraverser.Traverse(IEnumerable`1 toTraverse)
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.DetermineInheritanceRelations(IEnumerable`1 fields, IRelationCollection relations, IPredicate filter, IInheritanceInfoProvider inheritanceProvider, UniqueValueList`1 outerScopeAliases)
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.AddAdditionalInheritanceInformationToSetPredicate(IInheritanceInfoProvider infoProvider, UniqueValueList`1 outerAliases, FieldCompareSetPredicate setPredicate)
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.AddAdditionalInheritanceRelationsToRelationCollectionBasedOnFilter(IRelationCollection relations, IPredicateExpression filter, IInheritanceInfoProvider infoProvider)
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.AddInheritanceRelatedElementsToQueryElementsForDynamicList(InheritanceHierarchyType hierarchyType, IEnumerable`1 fields, IPredicateExpression filter, IRelationCollection relations, IInheritanceInfoProvider infoProvider, String forEntityName, List`1 aliasesAlreadyTypeFiltered)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.PreprocessQueryElements(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass22_0.<FetchProjection>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteHierarchicalValueListProjection(QueryExpression toExecute, IRelationPredicateBucket additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteHierarchicalValueListProjection(QueryExpression toExecute)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.<>c__DisplayClass4_0.<ExecuteValueListProjection>b__1()
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
   at Gravitate.Tests.Integration.GravitateServicesTests.ProjectionErrorExample(IQueryable`1 queryable) in C:\workspace\gravitate\Gravitate.Core.CTRM_V5\Gravitate.Tests.Integration\GravitateServicesTests.cs:line 36
   at Gravitate.Tests.Integration.GravitateServicesTests.TestProjectionError() in C:\workspace\gravitate\Gravitate.Core.CTRM_V5\Gravitate.Tests.Integration\GravitateServicesTests.cs:line 20

Additionally, the following methods against the queryable itself have no issue.

private static void SuccessfulFilter(IQueryable<InvoiceEntity> queryable)
{
    var queryResults = queryable
            .Where(x => x.InvoiceDetails.Any(y => y.OriginLocationId == 2272))
            .FirstOrDefault();
}

private static void SuccessfulFilterWithProjection(IQueryable<InvoiceEntity> queryable)
{
    var queryResults = queryable
            .Where(x => x.InvoiceDetails.Any(y => y.OriginLocationId == 2272))
            .ProjectToInvoiceEntityModel()
            .FirstOrDefault();
}

Any thoughts on why this might be? Are we not allowed to have a predicate against a child object on the projection?

Are you sure details with OriginLocationId 2272 return something? You might be falling in this case:

Be aware that the in-memory projection doesn't contain any null checks, so if a related entity is null (Nothing in VB.NET) and it's used in a navigation in the projection, using the projection method will lead to a NullReferenceException being thrown.

Please try to put the ProjectTo... method at the end of the projections, not at the beginning. private static void ProjectionErrorExample(IQueryable<InvoiceEntity> queryable)

{
    var projectedResult = queryable
            .Where(x => x.InvoiceDetails.Any(y => y.OriginLocationId == 2272))
            .ProjectToInvoiceEntityModel()
            .FirstOrDefault();
}

So I think at this point it's not an in-memory projection because queryable execution is delayed until I call FirstOrDefault. My understanding is that when I add a where clause or projection to the queryable, that expression tree is converted to an appropriate SQL query that runs against the DB on execution (how it seems to work with a queryable on an entity class).

Is that not something you can do with derived models?

In my case, I explicitly want to filter on the fields available to the derived element, and not everything available on the entity itself. In your example the where clause filters on the Entity object (you could have a filter on anything present on an invoice entity or any tables attached via a relationship), which I don't really want here. Normally I can do a standard projection into a type I've defined, and then add a where clause after the fact, so I had assumed I could do it with derived elements.

I don't know if it helps, but if I run a SQL trace and debug through this I hit the exception before anything happens on the database, so I don't think this is happening when data is being mapped, but maybe when the initial query is being built?

Thanks!

The ProjectTo* methods build a new Queryable, however the projection doesn't happen until you enumerate that queryable. You append new predicates to the elements after that, which are all appended to the queryable. The ProjectTo* method you're using is also a complex one as it performs multiple queries, as it will obtain a nested set for the related elements.

You then enumerate (or .. execute really) the queryable with the FirstOrDefault method and at that point the whole queryable is evaluated and converted to SQL.

However it can't find the field in the derived element as an entity field with a mapping, so it keels over, as it doesn't have a relationship between the derived element field and the entity field it's coming from (that relationship is formulated in the projectto* method, but there's no meta-data for that available so it can't determine, based on the property of the derived element type InvoiceEntityModel, which table field it has to use for the SQL query. )

This happens when you mix in-memory code and database targeting code in a linq query. the ProjectTo* methods are meant to be the end call of the queryable: after that you call methods that enumerate the queryable so it gets executed.

So what David suggested is actually the right thing to do here:

{
    var projectedResult = queryable
            .Where(x => x.InvoiceDetails.Any(y => y.OriginLocationId == 2272))
            .ProjectToInvoiceEntityModel()
            .FirstOrDefault();
}

Here the whole query before ProjectToInvoiceEntityModel is executed on the database, and as it's all entity elements, it knows how to transform this to SQL, and the result of that is then projected to the InvoiceEntityModel object.

Thanks that makes sense! I'm used to dealing with projections that happen in a single statement (like below), so I incorrectly assumed that it was all happening in one query, partly because filtering does seem to work correctly on fields present in the top-level collection.

Example Projection:

// example projection on IQueryable<InvoiceEntity>
var projection = queryable.Select(x => new
{
        x.InvoiceId,
        InvoiceDetails = x.InvoiceDetails.Select(detail => new
        {
                detail.InvoiceDetailId, 
                detail.OriginLocationId
        })
}).ToArray();

Is there a place in the documentation where it talks about this scenario? or did I just misunderstand the documentation talking about in-memory projections? Totally fine either way, but if this is somewhere (and I didn't just misunderstand) I'd like to read through it just to improve my general understanding.

Thanks again!

There is a separate documentation for Derived Models

And here is a link to the projection part of the docs, where you can find a code example. https://www.llblgen.com/Documentation/5.5/Derived%20Models/dto_llblgen.htm#example-projection-on-database-query