Filter not wrapping other criteria about entityframework.dynamicfilters HOT 10 CLOSED

It looks like the problem may lie in the DynamicFilterQueryVisitor.cs
The default switch looks like it tries to account for existing clauses, but doesn't account for them not being enclosed.

from entityframework.dynamicfilters.

I see what you mean. Should not be a problem to wrap that in ()'s in the query visitor. I'm busy all day today but I should be able to get that sorted tomorrow.

from entityframework.dynamicfilters.

Thanks! I found another example but in the opposite direction. I set my "IsActive" filter to null to disable it, but when running an EntityFramework "Find" by ID, the resulting query finds multiple records where there should be only one due to the filter not being grouped properly. I started looking into it myself, but apparently have a lot to learn in this area! Hopefully this helps.

Example:

SELECT
Extent1.CompanyID,
Extent1.Name,
Extent1.Phone1,
Extent1.Phone2,
Extent1.Email1,
Extent1.Email2,
Extent1.WebsiteURL,
Extent1.ApplicationURL,
Extent1.LogoLargePath,
Extent1.LogoMediumPath,
Extent1.LogoSmallPath,
Extent1.Active,
Extent1.MasterTenant,
Extent1.Created,
Extent1.CreatedBy,
Extent1.Edited,
Extent1.EditedBy
FROM Company AS Extent1
WHERE Extent1.CompanyID = @p0 AND (Extent1.Active = @DynamicFilterParam_IsActive_Active) OR (@DynamicFilterParam_IsActive_Active IS NULL)

from entityframework.dynamicfilters.

I've been trying to reproduce this but I'm seeing a different behavior than what you are showing in your captured sql. When I specify additional filters on entity queries (using linq's .Where() clause, for example), those additional filters cause EF to create sub-tables in the query. Because of this, enclosing any additional filters in ()'s is actually not necessary.

For example, if I add an "IsActive" flag to the BlogEntry model in my example project and then query them like this (in the Query() method in Program.cs):

bool active = true;
var blogEntries = context.BlogEntries.Where(b => b.IsActive == active).ToList();

I get this sql:

exec sp_executesql N'SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[AccountID] AS [AccountID], 
    [Extent1].[Body] AS [Body], 
    [Extent1].[IsDeleted] AS [IsDeleted], 
    [Extent1].[IsActive] AS [IsActive]
    FROM ( SELECT [Var_2].[ID] AS [ID], [Var_2].[AccountID] AS [AccountID], [Var_2].[Body] AS [Body], [Var_2].[IsDeleted] AS [IsDeleted], [Var_2].[IsActive] AS [IsActive]
        FROM [dbo].[BlogEntries] AS [Var_2]
        WHERE ([Var_2].[IsDeleted] = @DynamicFilterParam_IsDeleted_IsDeleted OR @DynamicFilterParam_IsDeleted_IsDeleted IS NULL) AND ([Var_2].[AccountID] = @DynamicFilterParam_BlogEntriesForCurrentUser_AccountID OR @DynamicFilterParam_BlogEntriesForCurrentUser_AccountID IS NULL)
    )  AS [Extent1]
    WHERE [Extent1].[IsActive] = @p__linq__0',N'@DynamicFilterParam_IsDeleted_IsDeleted bit,@DynamicFilterParam_BlogEntriesForCurrentUser_AccountID uniqueidentifier,@p__linq__0 bit',@DynamicFilterParam_IsDeleted_IsDeleted=0,@DynamicFilterParam_BlogEntriesForCurrentUser_AccountID='DE00FC6C-B078-E411-BDC9-BC5FF44B998D',@p__linq__0=1

Do you have an example project you can email to me at [email protected] that demonstrates the problem? And can you check the version of EF you are using? The package should require EF 6.1.1 but maybe that's not specified correctly.

from entityframework.dynamicfilters.

Hi! I am putting it together right now, trying to include both of my example. Thanks!

from entityframework.dynamicfilters.

This turned out to be an issue with how I was handling user provided filters (using .Where(), for example). It was not apparent unless using MySQL (as Relic79 is) which does not generate embedded queries as eagerly as when using MS SQL Server.

As a bonus, in fixing this, it also fixes the query generation in MS SQL Server to NOT generate those embedded queries in the first place. That had already been noticed and reported as issue #2.

The fixes are checked in and an update has been push to NuGet.

from entityframework.dynamicfilters.

Thanks! This works for basic queries with where clauses, but I am finding that if I use any includes to eager load navigation properties, the filter no longer gets applied to the parent entity.

I would expect to not be able to filter the actual navigation properties, but am hoping it should still filter the parent entity. I can produce a repro if needed but simply including a navigation and adding a where should cause the issue at least on MySQL.

Thanks!

from entityframework.dynamicfilters.

Yeah, it should still filter the parent as well as any child properties. Or at least, that was the intention. :) I'll check it out. It should be easy to repro.

from entityframework.dynamicfilters.

Just posted a fix (to github and NuGet) that should correct this. Try that when you can and let me know if that covers everything.

It was something broken with how I made the previous fix. It was only attempting to add the dynamic filters once per query even if there were filters specified on multiple entity types. So it should now filter on the main entity as well as any included properties.

Just beware the issue discussed here #5 which will affect included properties if you re-use a DbContext and change the filter values in between query executions. If you need to do that, you will need to construct a new DbContext so that it does not have a cache of entities that may not match your new filter values.

from entityframework.dynamicfilters.

It appears to be working for me now, I can live with #5, and it looks like if I couldn't for some reason new up another DBContext, this may help: http://msdn.microsoft.com/en-us/library/bb896255.aspx which talks about the objectcontext refresh option. Thanks!

from entityframework.dynamicfilters.