2.2 breaks our queries and generates complex SQL

[sebastianrosch]

We accidentally updated DynamicFilters to v2.2 through Nuget and all our many of our queries did no longer work. We are only applying an IsDeleted filter globally and with version 1.4, the queries only contained an additional Where clause, as was expected.

Now, with version 2.2, the queries are way longer and more complex, with several Outer Apply statements and the results are wrong compared to v1.4.

I will post specific examples once I had more time to investigate.

[sebastianrosch]

Here is on example in 1.4, where the only addition done by DynamicFilters is the DeletedOn = NULL in the Where clause:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Url] AS [Url], 
    [Extent1].[Title] AS [Title], ...
    FROM   (SELECT [Var_11].[Id] AS [Id], [Var_11].[Url] AS [Url], [Var_11].[Title] AS [Title], [Var_11].[Order] AS [Order], [Var_11].[ParentId] AS [ParentId], [Var_11].[IsActive] AS [IsActive], ..... [Var_11].[ModuleId] AS [ModuleId], [Var_11].[CreatedOn] AS [CreatedOn], [Var_11].[CreatedBy] AS [CreatedBy], [Var_11].[UpdatedOn] AS [UpdatedOn], [Var_11].[UpdatedBy] AS [UpdatedBy], [Var_11].[DeletedOn] AS [DeletedOn], [Var_11].[DeletedBy] AS [DeletedBy]
        FROM [dbo].[ConfigMenuItem] AS [Var_11]
        WHERE ([Var_11].[DeletedOn] IS NULL)  ) AS [Extent1]
    LEFT OUTER JOIN  (SELECT [Var_12].[Id] AS [Id], [Var_12].[Url] AS [Url], [Var_12].[Title] AS [Title], [Var_12].[Order] AS [Order], [Var_12].[ParentId] AS [ParentId], [Var_12].[IsActive] AS [IsActive],...... [Var_12].[ModuleId] AS [ModuleId], [Var_12].[CreatedOn] AS [CreatedOn], [Var_12].[CreatedBy] AS [CreatedBy], [Var_12].[UpdatedOn] AS [UpdatedOn], [Var_12].[UpdatedBy] AS [UpdatedBy], [Var_12].[DeletedOn] AS [DeletedOn], [Var_12].[DeletedBy] AS [DeletedBy]
        FROM [dbo].[ConfigMenuItem] AS [Var_12]
        WHERE ([Var_12].[DeletedOn] IS NULL)  ) AS [Extent2] ON [Extent1].[ParentId] = [Extent2].[Id]
    WHERE (1 = [Extent1].[IsActive]) AND (([Extent2].[Id] IS NULL) OR (([Extent2].[Id] IS NOT NULL) AND (1 = [Extent2].[IsActive])))

Here is the same query with v2.2:

SELECT 
    [Project7].[Id] AS [Id], 
    [Project7].[Url] AS [Url], 
    [Project7].[Title] AS [Title], .....
    [Element4].[Id] AS [Id1], 
    [Element4].[Url] AS [Url1], 
    [Element4].[Title] AS [Title1], ....
    FROM   (SELECT 
        [Project5].[Id] AS [Id], 
        [Project5].[Url] AS [Url], 
        [Project5].[Title] AS [Title], ....
        FROM   (SELECT 
            [Project3].[Id] AS [Id], 
            [Project3].[Url] AS [Url], 
            [Project3].[Title] AS [Title], .....
            [Project3].[Id1] AS [Id1], 
            [Element2].[Id] AS [Id2]
            FROM   (SELECT 
                [Project1].[Id] AS [Id], 
                [Project1].[Url] AS [Url], 
                [Project1].[Title] AS [Title], .....
                [Element1].[Id] AS [Id1]
                FROM   (SELECT 
                    [Extent1].[Id] AS [Id], 
                    [Extent1].[Url] AS [Url], 
                    [Extent1].[Title] AS [Title], ......
                    FROM [dbo].[ConfigMenuItem] AS [Extent1]
                    WHERE ([Extent1].[DeletedOn] IS NULL)  ) AS [Project1]
                OUTER APPLY  (SELECT TOP (1) 
                    [Extent2].[Id] AS [Id], 
                    [Extent2].[ParentId] AS [ParentId], 
                    [Extent2].[DeletedOn] AS [DeletedOn]
                    FROM [dbo].[ConfigMenuItem] AS [Extent2]
                    WHERE (([Extent2].[DeletedOn] IS NULL) ) AND ([Project1].[Id] = [Extent2].[ParentId]) ) AS [Element1] ) AS [Project3]
            OUTER APPLY  (SELECT TOP (1) 
                [Extent3].[Id] AS [Id], 
                [Extent3].[ParentId] AS [ParentId], 
                [Extent3].[DeletedOn] AS [DeletedOn]
                FROM [dbo].[ConfigMenuItem] AS [Extent3]
                WHERE (([Extent3].[DeletedOn] IS NULL) ) AND ([Project3].[Id] = [Extent3].[ParentId]) ) AS [Element2] ) AS [Project5]
        OUTER APPLY  (SELECT TOP (1) 
            [Extent4].[ParentId] AS [ParentId], 
            [Extent4].[IsActive] AS [IsActive], 
            [Extent4].[DeletedOn] AS [DeletedOn]
            FROM [dbo].[ConfigMenuItem] AS [Extent4]
            WHERE (([Extent4].[DeletedOn] IS NULL) ) AND ([Project5].[Id] = [Extent4].[ParentId]) ) AS [Element3]
        WHERE (1 = [Project5].[IsActive]) AND (([Project5].[Id1] IS NULL) OR (([Project5].[Id2] IS NOT NULL) AND (1 = [Element3].[IsActive]))) ) AS [Project7]
    OUTER APPLY  (SELECT TOP (1) 
        [Extent5].[Id] AS [Id], 
        [Extent5].[Url] AS [Url], 
        [Extent5].[Title] AS [Title], ......
        FROM [dbo].[ConfigMenuItem] AS [Extent5]
        WHERE (([Extent5].[DeletedOn] IS NULL) ) AND ([Project7].[Id] = [Extent5].[ParentId]) ) AS [Element4]

[jcachat]

Please post the models and filters. I can't get enough information from only the sql query. Also your queries do not show any Dynamic Filter parameters at all so from looking at it right now, I would have to conclude that Dynamic Filters is not involved. So if that is not the actual query captured by SQL Profiler, please post that as well.

[stormsjr1]

We have what appears to be a similar problem with v2.3.0. In our case, we have a self-referencing entity defined as follows:

public class UserAccount : IAmSoftDeleted
{
    public string FirstName { get; set; }
    public DateTime? LastLoggedInDate { get; set; }
    public string LastName { get; set; }
    public string MiddleName { get; set; }
    public DateTime CreateDate { get; set; }
    public UserAccount CreatorUser { get; set; }
    public int? CreatorUserId { get; set; }
    public DateTime? DeleteDate { get; set; }
    public UserAccount DeleteUser { get; set; }
    public int? DeleteUserId { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? LastModifiedDate { get; set; }
    public UserAccount LastModifiedUser { get; set; }
    public int? LastModifiedUserId { get; set; }
}

The filter is as follows:

modelBuilder.Filter("SoftDelete", (IAmSoftDeleted d) => d.IsDeleted, false);

In v1.4.11, the generated SQL was...

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[CreatorUserId] AS [CreatorUserId], 
    [Extent1].[CreateDate] AS [CreateDate], 
    [Extent1].[LastModifiedUserId] AS [LastModifiedUserId], 
    [Extent1].[LastModifiedDate] AS [LastModifiedDate], 
    [Extent1].[UserName] AS [UserName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent2].[UserName] AS [UserName1], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent3].[UserName] AS [UserName2]
    FROM    (SELECT [Var_1].[Id] AS [Id], [Var_1].[FirstName] AS [FirstName], [Var_1].[LastName] AS [LastName], [Var_1].[CreateDate] AS [CreateDate], [Var_1].[CreatorUserId] AS [CreatorUserId], [Var_1].[LastModifiedDate] AS [LastModifiedDate], [Var_1].[LastModifiedUserId] AS [LastModifiedUserId], [Var_1].[UserName] AS [UserName]
        FROM [dbo].[UserAccounts] AS [Var_1]
        WHERE ([Var_1].[IsDeleted] = @DynamicFilterParam_000001)  ) AS [Extent1]
    LEFT OUTER JOIN  (SELECT [Var_2].[Id] AS [Id], [Var_2].[UserName] AS [UserName]
        FROM [dbo].[UserAccounts] AS [Var_2]
        WHERE ([Var_2].[IsDeleted] = @DynamicFilterParam_000001)  ) AS [Extent2] ON [Extent1].[LastModifiedUserId] = [Extent2].[Id]
    LEFT OUTER JOIN  (SELECT [Var_3].[Id] AS [Id], [Var_3].[UserName] AS [UserName]
        FROM [dbo].[UserAccounts] AS [Var_3]
        WHERE ([Var_3].[IsDeleted] = @DynamicFilterParam_000001)  ) AS [Extent3] ON [Extent1].[CreatorUserId] = [Extent3].[Id]',N'@DynamicFilterParam_000001 bit,@DynamicFilterParam_000002 bit',@DynamicFilterParam_000001=0,@DynamicFilterParam_000002=NULL

In 2.3.0, it is...

exec sp_executesql N'SELECT 
    [Project2].[Id] AS [Id], 
    [Project2].[CreatorUserId] AS [CreatorUserId], 
    [Project2].[CreateDate] AS [CreateDate], 
    [Project2].[LastModifiedUserId] AS [LastModifiedUserId], 
    [Project2].[LastModifiedDate] AS [LastModifiedDate], 
    [Project2].[UserName] AS [UserName], 
    [Project2].[LastName] AS [LastName], 
    [Project2].[UserName1] AS [UserName1], 
    [Project2].[FirstName] AS [FirstName], 
    [Element2].[UserName] AS [UserName2]
    FROM   (SELECT 
        [Filter1].[Id] AS [Id], 
        [Filter1].[FirstName] AS [FirstName], 
        [Filter1].[LastName] AS [LastName], 
        [Filter1].[CreateDate] AS [CreateDate], 
        [Filter1].[CreatorUserId] AS [CreatorUserId], 
        [Filter1].[LastModifiedDate] AS [LastModifiedDate], 
        [Filter1].[LastModifiedUserId] AS [LastModifiedUserId], 
        [Filter1].[UserName] AS [UserName], 
        [Element1].[UserName] AS [UserName1]
        FROM   (SELECT [Extent1].[Id] AS [Id], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[CreateDate] AS [CreateDate], [Extent1].[CreatorUserId] AS [CreatorUserId], [Extent1].[LastModifiedDate] AS [LastModifiedDate], [Extent1].[LastModifiedUserId] AS [LastModifiedUserId], [Extent1].[UserName] AS [UserName]
            FROM [dbo].[UserAccounts] AS [Extent1]
            WHERE ([Extent1].[IsDeleted] = @DynamicFilterParam_000001)  ) AS [Filter1]
        OUTER APPLY  (SELECT TOP (1) 
            [Extent2].[IsDeleted] AS [IsDeleted], 
            [Extent2].[LastModifiedUserId] AS [LastModifiedUserId], 
            [Extent2].[UserName] AS [UserName]
            FROM [dbo].[UserAccounts] AS [Extent2]
            WHERE (([Extent2].[IsDeleted] = @DynamicFilterParam_000001) ) AND ([Filter1].[Id] = [Extent2].[LastModifiedUserId]) ) AS [Element1] ) AS [Project2]
    OUTER APPLY  (SELECT TOP (1) 
        [Extent3].[CreatorUserId] AS [CreatorUserId], 
        [Extent3].[IsDeleted] AS [IsDeleted], 
        [Extent3].[UserName] AS [UserName]
        FROM [dbo].[UserAccounts] AS [Extent3]
        WHERE (([Extent3].[IsDeleted] = @DynamicFilterParam_000001) ) AND ([Project2].[Id] = [Extent3].[CreatorUserId]) ) AS [Element2]',N'@DynamicFilterParam_000001 bit,@DynamicFilterParam_000002 bit',@DynamicFilterParam_000001=0,@DynamicFilterParam_000002=NULL

([Filter1].[Id] = [Extent2].[LastModifiedUserId]) should be ([Filter1].[LastModifiedUserId] = [Extent2].[Id])

and

([Project2].[Id] = [Extent3].[CreatorUserId]) should be ([Project2].[CreatorUserId] = [Extent3].[Id])

[RubenDelange]

I updated from v1.4.11 to v2.4.0 the other day and suddenly a (only 1) very slow SQL query popped up.

query_1.4.11.txt
query_2.4.0.txt (very slow)

In code, I use a "where Id = 'guid'" filter and I map the EF entity to a DTO using AutoMapper's IQueryable extension. Version of AutoMapper is 4.1.1 in both scenario's above.
DynamicFilters is used to add a SoftDelete filter to every query.

Target DTO in this case is rather complex due to it's nested structure and 2 properties pointing to the same SQL table, but with v1.4.11 I never had any performance issues with it.

Target dto.txt

If I compare the generated query in v2.4.0, I notice the use of "OUTER APPLY" statements. The v1.4.11 query doesn't have those. The example that @stormsjr1 posted is also an example of this behavior.

Anyway, if I revert back to v1.4.11 all is good again, so it looks like the v2.4.0 version has something to do with it.

[ScarlettCode]

Just want to add we seeing the same thing. From left to right. No filtering. 1.4 filtering. 2.x filtering. That's getting back a single record but you can definitely see the perf decrease from 1.4 to 2. Happy to supply more info if needed.

[ArnaudB88]

Hi,
Any updates about this issue yet?
We also have this issue in some of our projects (using this package for a soft delete filter).
I tried to disable the filter before executing the query but is has no effect, the generated sql query is extremely long (11000 line query vs 300 line query).
Because of this behaviour we are forced to move back from v2.7 to v1.4 for several big projects (missing other nice functionality).
Any update would be welcome :)

Thx!

[ArnaudB88]

Hello,
Is it possible to fix this issue please? The latest release version (2.10) still has this bug which causes complex queries. We are forced to keep using the old v1.4 version because we don't want those complex queries on our production systems.
Thanks!

[JonathanMagnan]

Hello @ArnaudB88 ,

Thank you, I will try to reproduce this issue in the next few days and better understand what in the code has been modified to causing this issue.

Best Regards,

Jonathan

[JonathanMagnan]

Hello guys,

Do anyone of you could help me to reproduce this issue

Here is a code a tried to reproduce it with self-hierarchy as @stormsjr1 reported.

If I could get a working example that causes the problem, I will be able to continue the investigation about the issue

using System.Data.Entity;
using System.Linq;
using System.Windows.Forms;
using EntityFramework.DynamicFilters;
namespace Z.Lab
{
    public partial class Form_Request_FilterLongQuery : Form
    {
        public Form_Request_FilterLongQuery()
        {
            InitializeComponent();

            // CLEAR
            using (var ctx = new EntityContext())
            {
                ctx.UserAccounts.RemoveRange(ctx.UserAccounts);
                ctx.SaveChanges();
            }

            // SEED
            using (var ctx = new EntityContext())
            {
                ctx.UserAccounts.Add(new UserAccount() { ColumnInt = 1, IsDeleted = false });
                ctx.UserAccounts.Add(new UserAccount() { ColumnInt = 2, IsDeleted = true });
                ctx.SaveChanges();
            }

            // TEST
            using (var ctx = new EntityContext())
            {
                var list = ctx.UserAccounts
                    .Include(x => x.CreatorUser)
                    .Include(x => x.DeleteUser)
                    .Include(x => x.LastModifiedUser)
                    .ToList();
            }
        }

        public class EntityContext : DbContext
        {
            public EntityContext() : base("CodeFirstEntities")
            {
            }

            public DbSet<UserAccount> UserAccounts { get; set; }

            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Filter("SoftDelete", (IAmSoftDeleted d) => d.IsDeleted, false);

                modelBuilder.Types().Configure(x => x.ToTable(GetType().DeclaringType != null ? GetType().DeclaringType.FullName.Replace(".", "_") + "_" + x.ClrType.Name : ""));

                base.OnModelCreating(modelBuilder);
            }
        }



        public interface IAmSoftDeleted
        {
            bool IsDeleted { get; set; }
        }

        public class UserAccount : IAmSoftDeleted
        {
            public int ID { get; set; }
            public int ColumnInt { get; set; }
            public bool IsDeleted { get; set; }
            public int? CreatorUserID { get; set; }
            public UserAccount CreatorUser { get; set; }
            public int? DeleteUserID { get; set; }
            public UserAccount DeleteUser { get; set; }
            public int? LastModifiedUserID { get; set; }
            public UserAccount LastModifiedUser { get; set; }
        }
    }
}

Best Regards,

Jonathan

[stormsjr1]

I was able to recreate the issue in 2.3.0 with the following:

namespace App
{
    public interface IAmSoftDeleted
    {
        bool IsDeleted { get; set; }
    }

    public class EntityContext : DbContext
    {
        public EntityContext() : base("CodeFirstEntities")
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<EntityContext>());
        }

        public DbSet<UserAccount> UserAccounts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Filter("SoftDelete", (IAmSoftDeleted d) => d.IsDeleted, false);
            base.OnModelCreating(modelBuilder);
        }
    }

    public class UserAccount : IAmSoftDeleted
    {
        public DateTime CreateDate { get; set; }
        public UserAccount CreatorUser { get; set; }
        public int? CreatorUserId { get; set; }
        public DateTime? DeleteDate { get; set; }
        public UserAccount DeleteUser { get; set; }
        public int? DeleteUserId { get; set; }
        public string FirstName { get; set; }
        public int Id { get; set; }
        public bool IsDeleted { get; set; }
        public DateTime? LastLoggedInDate { get; set; }
        public DateTime? LastModifiedDate { get; set; }
        public UserAccount LastModifiedUser { get; set; }
        public int? LastModifiedUserId { get; set; }
        public string LastName { get; set; }
        public string MiddleName { get; set; }
        public string UserName { get; set; }
    }

    internal class Program
    {
        private static void Main(string[] args)
        {
            var accounts = new List<UserAccount>
            {
                new UserAccount {CreateDate = DateTime.Now, CreatorUserId = 1},
                new UserAccount {CreateDate = DateTime.Now, CreatorUserId = 2},
                new UserAccount {CreateDate = DateTime.Now, CreatorUserId = 1, LastModifiedUserId = 1}
            };

            // SEED
            using (var ctx = new EntityContext())
            {
                ctx.UserAccounts.AddRange(accounts);
                ctx.SaveChanges();
            }

            // TEST
            using (var ctx = new EntityContext())
            {
                var list = ctx.UserAccounts
                    .Include(x => x.CreatorUser)
                    .Include(x => x.LastModifiedUser)
                    .Select(x => new
                    {
                        x.Id,
                        x.CreatorUser,
                        x.CreatorUserId,
                        x.CreateDate,
                        x.LastModifiedUserId,
                        x.LastModifiedUser,
                        x.UserName,
                        x.FirstName,
                        x.LastName
                    })
                    .ToList();

                foreach (var account in list)
                {
                    if (account.LastModifiedUserId != account.LastModifiedUser?.Id)
                    {
                        Console.WriteLine($"User {account.Id} was last modified by [{account.LastModifiedUserId}] but query returned [{account.LastModifiedUser?.Id}]");
                    }
                }

                Console.ReadLine();
            }
        }
    }
}

In v2.3.0, the generated query is as follows:

exec sp_executesql N'SELECT 
    [Project3].[Id] AS [Id], 
    [Project3].[Id1] AS [Id1], 
    [Project3].[CreateDate1] AS [CreateDate], 
    [Project3].[CreatorUserId1] AS [CreatorUserId], 
    [Project3].[DeleteDate] AS [DeleteDate], 
    [Project3].[DeleteUserId] AS [DeleteUserId], 
    [Project3].[FirstName1] AS [FirstName], 
    [Project3].[IsDeleted] AS [IsDeleted], 
    [Project3].[LastLoggedInDate] AS [LastLoggedInDate], 
    [Project3].[LastModifiedDate] AS [LastModifiedDate], 
    [Project3].[LastModifiedUserId1] AS [LastModifiedUserId], 
    [Project3].[LastName1] AS [LastName], 
    [Project3].[MiddleName] AS [MiddleName], 
    [Project3].[UserName1] AS [UserName], 
    [Project3].[CreatorUserId] AS [CreatorUserId1], 
    [Project3].[CreateDate] AS [CreateDate1], 
    [Project3].[LastModifiedUserId] AS [LastModifiedUserId1], 
    [Element2].[Id] AS [Id2], 
    [Element2].[CreateDate] AS [CreateDate2], 
    [Element2].[CreatorUserId] AS [CreatorUserId2], 
    [Element2].[DeleteDate] AS [DeleteDate1], 
    [Element2].[DeleteUserId] AS [DeleteUserId1], 
    [Element2].[FirstName] AS [FirstName1], 
    [Element2].[IsDeleted] AS [IsDeleted1], 
    [Element2].[LastLoggedInDate] AS [LastLoggedInDate1], 
    [Element2].[LastModifiedDate] AS [LastModifiedDate1], 
    [Element2].[LastModifiedUserId] AS [LastModifiedUserId2], 
    [Element2].[LastName] AS [LastName1], 
    [Element2].[MiddleName] AS [MiddleName1], 
    [Element2].[UserName] AS [UserName1], 
    [Project3].[UserName] AS [UserName2], 
    [Project3].[FirstName] AS [FirstName2], 
    [Project3].[LastName] AS [LastName2]
    FROM   (SELECT 
        [Project1].[Id] AS [Id], 
        [Project1].[CreateDate] AS [CreateDate], 
        [Project1].[CreatorUserId] AS [CreatorUserId], 
        [Project1].[FirstName] AS [FirstName], 
        [Project1].[LastModifiedUserId] AS [LastModifiedUserId], 
        [Project1].[LastName] AS [LastName], 
        [Project1].[UserName] AS [UserName], 
        [Element1].[Id] AS [Id1], 
        [Element1].[CreateDate] AS [CreateDate1], 
        [Element1].[CreatorUserId] AS [CreatorUserId1], 
        [Element1].[DeleteDate] AS [DeleteDate], 
        [Element1].[DeleteUserId] AS [DeleteUserId], 
        [Element1].[FirstName] AS [FirstName1], 
        [Element1].[IsDeleted] AS [IsDeleted], 
        [Element1].[LastLoggedInDate] AS [LastLoggedInDate], 
        [Element1].[LastModifiedDate] AS [LastModifiedDate], 
        [Element1].[LastModifiedUserId] AS [LastModifiedUserId1], 
        [Element1].[LastName] AS [LastName1], 
        [Element1].[MiddleName] AS [MiddleName], 
        [Element1].[UserName] AS [UserName1]
        FROM   (SELECT 
            [Extent1].[Id] AS [Id], 
            [Extent1].[CreateDate] AS [CreateDate], 
            [Extent1].[CreatorUserId] AS [CreatorUserId], 
            [Extent1].[FirstName] AS [FirstName], 
            [Extent1].[LastModifiedUserId] AS [LastModifiedUserId], 
            [Extent1].[LastName] AS [LastName], 
            [Extent1].[UserName] AS [UserName]
            FROM [dbo].[UserAccounts] AS [Extent1]
            WHERE ([Extent1].[IsDeleted] = @DynamicFilterParam_000001)  ) AS [Project1]
        OUTER APPLY  (SELECT TOP (1) 
            [Extent2].[Id] AS [Id], 
            [Extent2].[CreateDate] AS [CreateDate], 
            [Extent2].[CreatorUserId] AS [CreatorUserId], 
            [Extent2].[DeleteDate] AS [DeleteDate], 
            [Extent2].[DeleteUserId] AS [DeleteUserId], 
            [Extent2].[FirstName] AS [FirstName], 
            [Extent2].[IsDeleted] AS [IsDeleted], 
            [Extent2].[LastLoggedInDate] AS [LastLoggedInDate], 
            [Extent2].[LastModifiedDate] AS [LastModifiedDate], 
            [Extent2].[LastModifiedUserId] AS [LastModifiedUserId], 
            [Extent2].[LastName] AS [LastName], 
            [Extent2].[MiddleName] AS [MiddleName], 
            [Extent2].[UserName] AS [UserName]
            FROM [dbo].[UserAccounts] AS [Extent2]
            WHERE (([Extent2].[IsDeleted] = @DynamicFilterParam_000001) ) AND ([Project1].[Id] = [Extent2].[CreatorUserId]) ) AS [Element1] ) AS [Project3]
    OUTER APPLY  (SELECT TOP (1) 
        [Extent3].[Id] AS [Id], 
        [Extent3].[CreateDate] AS [CreateDate], 
        [Extent3].[CreatorUserId] AS [CreatorUserId], 
        [Extent3].[DeleteDate] AS [DeleteDate], 
        [Extent3].[DeleteUserId] AS [DeleteUserId], 
        [Extent3].[FirstName] AS [FirstName], 
        [Extent3].[IsDeleted] AS [IsDeleted], 
        [Extent3].[LastLoggedInDate] AS [LastLoggedInDate], 
        [Extent3].[LastModifiedDate] AS [LastModifiedDate], 
        [Extent3].[LastModifiedUserId] AS [LastModifiedUserId], 
        [Extent3].[LastName] AS [LastName], 
        [Extent3].[MiddleName] AS [MiddleName], 
        [Extent3].[UserName] AS [UserName]
        FROM [dbo].[UserAccounts] AS [Extent3]
        WHERE (([Extent3].[IsDeleted] = @DynamicFilterParam_000001) ) AND ([Project3].[Id] = [Extent3].[LastModifiedUserId]) ) AS [Element2]',N'@DynamicFilterParam_000001 bit,@DynamicFilterParam_000002 bit',@DynamicFilterParam_000001=0,@DynamicFilterParam_000002=NULL

In 2.10, however, the dynamic SQL is correct:

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[CreateDate] AS [CreateDate], 
    [Extent2].[CreatorUserId] AS [CreatorUserId], 
    [Extent2].[DeleteDate] AS [DeleteDate], 
    [Extent2].[DeleteUserId] AS [DeleteUserId], 
    [Extent2].[FirstName] AS [FirstName], 
    [Extent2].[IsDeleted] AS [IsDeleted], 
    [Extent2].[LastLoggedInDate] AS [LastLoggedInDate], 
    [Extent2].[LastModifiedDate] AS [LastModifiedDate], 
    [Extent2].[LastModifiedUserId] AS [LastModifiedUserId], 
    [Extent2].[LastName] AS [LastName], 
    [Extent2].[MiddleName] AS [MiddleName], 
    [Extent2].[UserName] AS [UserName], 
    [Extent1].[CreatorUserId] AS [CreatorUserId1], 
    [Extent1].[CreateDate] AS [CreateDate1], 
    [Extent1].[LastModifiedUserId] AS [LastModifiedUserId1], 
    [Extent3].[Id] AS [Id2], 
    [Extent3].[CreateDate] AS [CreateDate2], 
    [Extent3].[CreatorUserId] AS [CreatorUserId2], 
    [Extent3].[DeleteDate] AS [DeleteDate1], 
    [Extent3].[DeleteUserId] AS [DeleteUserId1], 
    [Extent3].[FirstName] AS [FirstName1], 
    [Extent3].[IsDeleted] AS [IsDeleted1], 
    [Extent3].[LastLoggedInDate] AS [LastLoggedInDate1], 
    [Extent3].[LastModifiedDate] AS [LastModifiedDate1], 
    [Extent3].[LastModifiedUserId] AS [LastModifiedUserId2], 
    [Extent3].[LastName] AS [LastName1], 
    [Extent3].[MiddleName] AS [MiddleName1], 
    [Extent3].[UserName] AS [UserName1], 
    [Extent1].[UserName] AS [UserName2], 
    [Extent1].[FirstName] AS [FirstName2], 
    [Extent1].[LastName] AS [LastName2]
    FROM   [dbo].[UserAccounts] AS [Extent1]
    LEFT OUTER JOIN [dbo].[UserAccounts] AS [Extent2] ON (([Extent2].[IsDeleted] = @DynamicFilterParam_000001) ) AND ([Extent1].[CreatorUserId] = [Extent2].[Id])
    LEFT OUTER JOIN [dbo].[UserAccounts] AS [Extent3] ON (([Extent3].[IsDeleted] = @DynamicFilterParam_000001) ) AND ([Extent1].[LastModifiedUserId] = [Extent3].[Id])
    WHERE ([Extent1].[IsDeleted] = @DynamicFilterParam_000001) ',N'@DynamicFilterParam_000001 bit,@DynamicFilterParam_000002 bit',@DynamicFilterParam_000001=0,@DynamicFilterParam_000002=NULL

So for my particular case, it appears to have been resolved somewhere between 2.3.0 and 2.10.0.

[JonathanMagnan]

Hello @stormsjr1 ,

Thank you for your example, I will try it this weekend and check what I can do.

Best Regards,

Jonathan

[JonathanMagnan]

Thank you @stormsjr1 ,

I tried your scenario and didn't understand why it was working until I realized you explicitly told the latest version was working correctly in this case. Your investigation may give me a hint to check when someone will be able to report a non-working code.

If someone could provide me an example that doesn't work with the latest version, I will be happy to investigate it.

Best Regards,

Jonathan

[ArnaudB88]

Hi @JonathanMagnan ,

Recently this bug became a big issue in one of our production systems.
I've send you a personal email with demo code and a database so you can reproduce this bug.
Can you pick this up again?

Thanks!
Arnaud

[JonathanMagnan]

Awesome @ArnaudB88 ,

Yes, one of my developers will look to your project.

[ArnaudB88]

Hi @JonathanMagnan

Any update on this bug yet?

Thanks

[JonathanMagnan]

Unfortunately from what I remember, we started to look at it but abandoned to try to fix it at this moment.

The v2.x had a lot of major changes to re-write query which is caused at the same time those kinds of performance issues.

So when we tried to fix it, we caused a lot of side impact in the unit test project and never found the perfect solution.

We are very open if someone could make a pull request with a fix but I don't think on our side we will put more time on it as we already spend several days trying to fix it and didn't make any progress.

[ArnaudB88]

Thank you for the feedback.
I find it unfortunate that no further investigation will be done. This means this repo should not be used anymore in big projects.

[JonathanMagnan]

The project is open source @ArnaudB88 ,

You can do it on your side and provide a pull request if you find a solution.

You must understand that my time or my developer time is not free or unlimited. Trying to fix this issue means that issue from other developers in other projects would not be fixed. So, unfortunately, we need to make some choices.