Query with .Join() on filtered entities fails with invalid cast exception from MaterializedDataRecord to anonymous type

[mlyczek]

DynamicFilters version: 3.0.1 EntityFramework version: 6.1.3 Database version: SQL Server 2014

Example with failing unit test: https://github.com/mlyczek/EF.DynamicFilters.ConditionalEnableIssue

In our project we have a query that uses .Join() operation. Both entities have filters defined on them (although disabled for that specific query as it's supposed to be cross-tenant, but that appears to be not really relevant in this issue). Everything worked fine in version 1.4.6 of DynamicFilters. After upgrading to 3.0.1 Entity Framework for some reason is trying to cast MaterializedDataRecord to our anonymous type (i.e. the result of that join) after reading data from DB and fails at it (obviously).

I prepared UnitTest to show what's happening, please look into AnonymousJoinTests in repository mentioned at the top of this issue. Two tests are failing. Even in a simple situation when we join to the same table on primary key (I know it's not very real world scenario, but it generates very simple SQL and shows that it has nothing to do with the query). Two tests ShouldNotFailWhileJoinToSelfWithFilters and ShouldNotFailWhileJoinToSelfWithoutFilters show the problem. The first test fails, while the second one passes. The only difference between the two is that the first one queries an entity that has dynamic filter defined whereas the latter uses entity without dynamic filter defined. There is a command line logger enabled on EF in this DbContext to show SQL queries generated in those two tests and that SQL queries are very simple and similar:

ShouldNotFailWhileJoinToSelfWithFilters query

SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[ShoppingCarts] AS [Extent1]
    WHERE (([Extent1].[Name] = @DynamicFilterParam_000001) OR (([Extent1].[Name] IS NULL) AND (@DynamicFilterParam_000001 IS NULL)) OR (@DynamicFilterParam_000002 IS NOT NULL)) AND (([Extent1].[Name] = @DynamicFilterParam_000001) OR (([Extent1].[Name] IS NULL) AND (@DynamicFilterParam_000001 IS NULL)) OR (@DynamicFilterParam_000002 IS NOT NULL))

ShouldNotFailWhileJoinToSelfWithoutFilters query

SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Brand] AS [Brand]
    FROM [dbo].[Cars] AS [Extent1]

I first thought that this is related to the anonymous projection, but the ShouldNotFailWithAnonymousProjection test passes despite the fact that it's a query on a filtered entity and SQL is also similar (but there is no .Join() in the C# code):

SELECT 
    1 AS [C1], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[ShoppingCarts] AS [Extent1]
    WHERE ([Extent1].[Name] = @DynamicFilterParam_000001) OR (([Extent1].[Name] IS NULL) AND (@DynamicFilterParam_000001 IS NULL)) OR (@DynamicFilterParam_000002 IS NOT NULL)

This looks like in EF command interceptor during filter application for queries with .Join(), there is something happening that forces EF to return the generic MaterializedDataRecord instead of my defined anonymous object, but that's only my suspicion.

This either forces us to stay with the old DynamicFilters version or to change the query (probably split it into two) to not use .Join() operation.

[JonathanMagnan]

Hello @mlyczek ,

Thank you for reporting.

We will look at it very soon.

Best Regards,

Jonathan

[lordpalf]

Any update on this one? We cannot upgrade to the latest because of this :(