Some bug about Guid,Guid? filter and Include query

[lewyue]

And see the test code

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

    public interface IMayHaveTenant
    {
        Guid? TenantId { get; set; }
    }

    public class ProductType : ISoftDelete, IMayHaveTenant
    {
        public ProductType()
        {
        }

        public int Id { get; set; }

        public string Name { get; set; }

        public int? ParentId { get; set; }

        [ForeignKey("ParentId")]
        public ProductType Parent { get; set; }
        [InverseProperty("Parent")]
        public ICollection<ProductType> Children { get; set; }

        public bool IsDeleted { get; set; }

        public Guid? TenantId { get; set; }

    }

      public class UserInfo : ISoftDelete
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public int? ParentId { get; set; }

        [ForeignKey("ParentId")]
        public UserInfo Parent { get; set; }
        [InverseProperty("Parent")]
        public ICollection<UserInfo> Children { get; set; }

        public bool IsDeleted { get; set; }
    }

 modelBuilder.Filter("IsDeleted", (ISoftDelete d) => d.IsDeleted, false);
modelBuilder.Filter("IMayHaveTenant", (IMayHaveTenant t, Guid? tenantId) => t.TenantId == tenantId || (Guid?)t.TenantId == null, Guid.Empty);

        public static void InitData()
        {
            ShopDbContext context = new ShopDbContext();

            if (context.ProductTypes.Count() > 0) return;

            ProductType productType1 = new ProductType()
            {
                Name = "1",
                ParentId = 1,
                IsDeleted = false,
                TenantId = new Guid("ffffffff-ffff-ffff-ffff-000000000000"),
            };
            ProductType productType2 = new ProductType()
            {
                Name = "2",
                ParentId = 1,
                IsDeleted = false,
                TenantId = new Guid("ffffffff-ffff-ffff-ffff-000000000000"),
            };
            ProductType productType3 = new ProductType()
            {
                Name = "3",
                ParentId = 1,
                IsDeleted = true,
                TenantId = null,
            };
            ProductType productType4 = new ProductType()
            {
                Name = "4",
                ParentId = 1,
                IsDeleted = true,
                TenantId = new Guid("ffffffff-ffff-ffff-ffff-000000000000"),
            };
            ProductType productType5 = new ProductType()
            {
                Name = "5",
                ParentId = 1,
                IsDeleted = false,
                TenantId = null,
            };
            context.ProductTypes.Add(productType1);
            context.ProductTypes.Add(productType2);
            context.ProductTypes.Add(productType3);
            context.ProductTypes.Add(productType4);
            context.ProductTypes.Add(productType5);
            context.SaveChanges();

            UserInfo userInfo1 = new UserInfo()
            {
                Name = "1",
                ParentId = null,
                IsDeleted = false
            };
            UserInfo userInfo2 = new UserInfo()
            {
                Name = "2",
                ParentId = null,
                IsDeleted = false
            };
            UserInfo userInfo3 = new UserInfo()
            {
                Name = "3",
                ParentId = null,
                IsDeleted = false
            };
            UserInfo userInfo4 = new UserInfo()
            {
                Name = "4",
                ParentId = 1,
                IsDeleted = false
            };
            context.UserInfos.Add(userInfo1);
            context.UserInfos.Add(userInfo2);
            context.UserInfos.Add(userInfo3);
            context.UserInfos.Add(userInfo4);
            context.SaveChanges();
        }

           ShopDbContext context = new ShopDbContext();

            //Userinfo have an ISoftDelete filter,and type is bool
            //this will not load parent when EntityFramework.DynamicFilters >= 2.0.0,but load correct when <=1.4.11
            //The correct SQL need "[Project1].[ParentId] = [Extend2].[Id]" but real SQL is "[Project1].[Id] = [Extend2].[ParentId]" 
            //primary and forign opposite
            var userInfo4 = context.UserInfos
                .Include(t => t.Parent)
                .FirstOrDefault(t => t.Id == 4);
            Console.WriteLine(string.Format("Refer loaded:{0}", userInfo4.Parent != null));


            //Userinfo have an ISoftDelete filter type is bool and an IMayHaveTenant filter type is Guid?
            //When IMayHaveTenand have an not null value,it get entity OK
            var key = new Guid("ffffffff-ffff-ffff-ffff-000000000000");
            context.SetFilterGlobalParameterValue("IMayHaveTenant", (Guid?)key);
            var type2 = context.ProductTypes
                .FirstOrDefault(t => t.Id == 2);
            Console.WriteLine(string.Format("Entity loaded:{0}", type2 != null));

            //And now load parent,thie parent refer is not loaded
            type2 = context.ProductTypes
                .Include(t => t.Parent)
                .FirstOrDefault(t => t.Id == 2);
            Console.WriteLine(string.Format("Refer loaded:{0}", type2.Parent != null));

            //When IMayHaveTenand is null,then entity will not load
            //Current SQL is  (TenantId IS NULL and @TenantId IS NULL) OR ([TenantId]=@TenantId) 
            //But all version SQL will like  [Extent1].[TenantId] = (CAST(NULL AS uniqueidentifier)) ,CAST( [Extent1].[TenantId] AS uniqueidentifier) IS NULL),[Extent1].[TenantId]=NULL and so on 
            context.SetFilterGlobalParameterValue("IMayHaveTenant", "TenantId", (Guid?)null);
            var type3 = context.ProductTypes
                .FirstOrDefault(t => t.Id == 3);
            Console.WriteLine(string.Format("Entity loaded:{0}", type3 != null));

            Console.ReadKey();
            return;

[jcachat]

I'm not sure I'm following what problem you are trying to describe. It may be helpful if you could include the test data for those entities so that I can replicate what you are doing. It may also be related to #76 so please check there to see if it's the same issue. I do not have a solution to that issue yet.

It does seem like you are saying there is an issue with the null checks on the filters? Instead of casting to (Guid?) and then comparing to "null", could you try using the ".HasValue" property and see if that make a difference? So change "(Guid?)t.TenantId == null" to "!t.TenantId.HasValue".

[lewyue]

Tks for reply.
The code in my topic is not my project code , just write for reappear bugs
Please see my last code ,i have write question and reason in annotation.
Expect for fix.

[lewyue]

First question ,linq query like this,have an include method to load refer nav property

            var userInfo4 = context.UserInfos
                .Include(t => t.Parent)
                .FirstOrDefault(t => t.Id == 4);

If Userinfo have an ISoftDelete filter,and type is bool
Query will not load parent when EntityFramework.DynamicFilters >= 2.0.0
But load correct when <=1.4.11
I have following the sql in sql profile,when use out apply ,the correct sql shoud be
WHERE [Project1].[ParentId] = [Extend2].[Id]
but real sql is
WHERE [Project1].[Id] = [Extend2].[ParentId]
primary and forign opposite to cause refer nav property load fail...

[lewyue]

Second when we want to user a nullable filter,if we set the filter value to null
The sql should be
WHERE ParentId IS NULL
In some cases,sql is
WHERE ParentId == NULL

So entity will query fail...

[lewyue]

And third
When filter is Guid? and set to null
I have tried every verison EF.DynamicFilters ,and see many error sql like
WHERE [Extent1].[TenantId] = (CAST(NULL AS uniqueidentifier))
WHERE CAST( [Extent1].[TenantId] AS uniqueidentifier) IS NULL)
WHERE [Extent1].[TenantId]=NULL
and so on... Sure,the entity will query fail
I hope the sql is
WHERE (TenantId IS NULL and @TenantId IS NULL) OR ([TenantId]=@TenantId)

NOTE:I have change the DynamicFilter parameter to a simple name like @TenantID to read easy

[lewyue]

The last i found the sql has too many no useful nesting sub query in verision >=2.0.0 than 1.4.11
maybe is prepare for nav property filter
but cause severe performance problems

Hope my poor english explains clearly.I add the InitData code in topic
Tks for you good job. @jcachat

[jcachat]

I'm going to try to answer each of these posts based on the "question number" you referenced in them. With 4 being your "last one".

1. I believe this is related to issue Filter not working on TPT #76. Please see that issue for more details. Except that I have not seen the FK's be reversed like that. It might be helpful if you posted the ENTIRE sql statement (as generated by both 1.4 and 2). Just posting those "where" fragments doesn't tell me which table "Project1" and "Extend2" are referencing and they will probably be different between those 2 queries.

2 & 3) Please try changing your filter to "!t.TenantId.HasValue" as I suggested in my first response and see if that makes a difference. I'm wondering if the cast to Guid? may be causing the problem and this will tell me if that's the case.

4. The difference in SQL there is because it is trying to query out your Parent property and also apply a filter to it. That must be done with an embedded select because when the filter is applied to that record, it can cause that record to not be returned - which will leave the property null in the entity after the query executes while still returning the main entity. If an "inner join" was done, applying that filter would cause nothing to be returned for your main entity due to the child property being filtered out. This is new behavior in v2 and was a result of the changes made to apply filters using CSpace (instead of SSpace) in order to support filters on navigation properties.

[lewyue]

Here is question 1 linq query,
This entity only has a ISoftDelete filter,all data in table IsDeteleted is false,
So will not disturb by other factors

            var userInfo4 = context.UserInfos
                .Include(t => t.Parent)
                .FirstOrDefault(t => t.Id == 4);

1.4.11 sql in profiler

exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[ParentId] AS [ParentId], 
    [Extent1].[IsDeleted] AS [IsDeleted], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[Name] AS [Name1], 
    [Extent2].[ParentId] AS [ParentId1], 
    [Extent2].[IsDeleted] AS [IsDeleted1]
    FROM   (SELECT [Var_1].[Id] AS [Id], [Var_1].[Name] AS [Name], [Var_1].[ParentId] AS [ParentId], [Var_1].[IsDeleted] AS [IsDeleted]
        FROM [dbo].[UserInfoes] AS [Var_1]
        WHERE ([Var_1].[IsDeleted] = @DynamicFilterParam_000001)  ) AS [Extent1]
    LEFT OUTER JOIN  (SELECT [Var_2].[Id] AS [Id], [Var_2].[Name] AS [Name], [Var_2].[ParentId] AS [ParentId], [Var_2].[IsDeleted] AS [IsDeleted]
        FROM [dbo].[UserInfoes] AS [Var_2]
        WHERE ([Var_2].[IsDeleted] = @DynamicFilterParam_000001)  ) AS [Extent2] ON [Extent1].[ParentId] = [Extent2].[Id]
    WHERE 4 = [Extent1].[Id]',N'@DynamicFilterParam_000001 bit,@DynamicFilterParam_000002 bit',@DynamicFilterParam_000001=0,@DynamicFilterParam_000002=NULL

The join is correct
ON [Extent1].[ParentId] = [Extent2].[Id]

2.4.0 sql in profile

exec sp_executesql N'SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[Name] AS [Name], 
    [Limit1].[ParentId] AS [ParentId], 
    [Limit1].[IsDeleted] AS [IsDeleted], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[Name1] AS [Name1], 
    [Limit1].[ParentId1] AS [ParentId1], 
    [Limit1].[IsDeleted1] AS [IsDeleted1]
    FROM ( SELECT TOP (1) 
        [Project1].[Id] AS [Id], 
        [Project1].[Name] AS [Name], 
        [Project1].[ParentId] AS [ParentId], 
        [Project1].[IsDeleted] AS [IsDeleted], 
        [Element1].[Id] AS [Id1], 
        [Element1].[Name] AS [Name1], 
        [Element1].[ParentId] AS [ParentId1], 
        [Element1].[IsDeleted] AS [IsDeleted1]
        FROM   (SELECT 
            [Extent1].[Id] AS [Id], 
            [Extent1].[Name] AS [Name], 
            [Extent1].[ParentId] AS [ParentId], 
            [Extent1].[IsDeleted] AS [IsDeleted]
            FROM [dbo].[UserInfoes] AS [Extent1]
            WHERE (([Extent1].[IsDeleted] = @DynamicFilterParam_000001) ) AND (4 = [Extent1].[Id]) ) AS [Project1]
        OUTER APPLY  (SELECT TOP (1) 
            [Extent2].[Id] AS [Id], 
            [Extent2].[Name] AS [Name], 
            [Extent2].[ParentId] AS [ParentId], 
            [Extent2].[IsDeleted] AS [IsDeleted]
            FROM [dbo].[UserInfoes] AS [Extent2]
            WHERE (([Extent2].[IsDeleted] = @DynamicFilterParam_000001) ) AND ([Project1].[Id] = [Extent2].[ParentId]) ) AS [Element1]
    )  AS [Limit1]',N'@DynamicFilterParam_000001 bit,@DynamicFilterParam_000002 bit',@DynamicFilterParam_000001=0,@DynamicFilterParam_000002=NULL

The outer apoly join is
WHERE (([Extent2].[IsDeleted] = @DynamicFilterParam_000001) ) AND ([Project1].[Id] = [Extent2].[ParentId]) ) AS [Element1]
Here is quer result

Id Name ParentId IsDeleted Id1 Name1 ParentId1 IsDeleted1
4 4 1 0 NULL NULL NULL NULL

This join will cause parent load fail,the correct should be
WHERE (([Extent2].[IsDeleted] = @DynamicFilterParam_000001) ) AND ([Project1].[ParentId] = [Extent2].[Id]) ) AS [Element1]
and change the sql will get data

Id Name ParentId IsDeleted Id1 Name1 ParentId1 IsDeleted1
4 4 1 0 1 1 NULL 0

UserInfo4's parent is UserInfo1,the result is correct!

[jcachat]

I reproduced your first problem and tracked it down. I have a fix but need to do a little more testing and ran out of time tonight. I'll try to get it pushed out tomorrow.

[lewyue]

Glad for see your reply ,when question 1 fixed ,I will explain 2 and 3
I belive the sql is not correct too

[jcachat]

Could you please explain it now? If there's a change I need to make, I would rather push out once than multiple times.

[lewyue]

OK, Now i explain Q2
Any entity if have an filter value is Guid?Linq query will not get entity

context.ProductTypes.Get(1)
context.ProductTypes.FirstOrDefault();
context.ProductTypes.Where();

the sql will have sql like

WHERE [Extent1].[TenantId]=NULL

Filter set is
modelBuilder.Filter("IMayHaveTenant", (IMayHaveTenant t, Guid? tenantId) => t.TenantId == tenantId || (Guid?)t.TenantId == null, Guid.Empty);
also test
modelBuilder.Filter("IMayHaveTenant", (IMayHaveTenant t, Guid? tenantId) => t.TenantId == tenantId || !t.TenantId.HasValue(), Guid.Empty);

the correct sql should be

WHERE [Extent1].[TenantId] is NULL

Such as my IMayHaveTenant filter,but if the filter value is int? long? the sql is correct like

WHERE [Extent1].[TenantId] is NULL

I have test in ef with no filter
context.ProductTypes.Where(t.TenantId == null);
it get an correct sql too

[lewyue]

Now is Q3,very like Q2
Also entity has a Guid? filter
If we don't know filter parameter is a guid or null,
the filter sql query where should be

WHERE (TenantId IS NULL and @TenantId IS NULL) OR ([TenantId]=@TenantId)

And real filter query i have see

[Extent1].[TenantId] = (CAST(NULL AS uniqueidentifier))
CAST( [Extent1].[TenantId] AS uniqueidentifier) IS NULL)
@TenantId==null and [Extent1].[TenantId]==NULL OR ([TenantId]=@TenantId)

As above ,(CAST(NULL AS uniqueidentifier)) will never get a right value to compare
In many version ,I have see other sql deal wrong with guid,here only give the representative sql
So i think,may be like many other frame,Guid is deal as refer type ,but Guid should as value type .
TKS

[jcachat]

For Q2 & Q3: I cannot reproduce the bad sql problems you are seeing - including seeing "cast" in the sql. I cut-and-pasted your code exactly as you posted it above.

However, in the code you posted, you have this statement:

context.SetFilterGlobalParameterValue("IMayHaveTenant", "TenantId", (Guid?)null);

This is referencing the "tenantId" parameter name incorrectly. Case matters here - that needs to be "tenantId" not "TenantId". Change that to the following and the set parameter will match correctly.

context.SetFilterGlobalParameterValue("IMayHaveTenant", "tenantId", (Guid?)null);

Or you can remove the parameter name from that statement completely since there is only 1 parameter.

Also, try this for your filter:

modelBuilder.Filter("IMayHaveTenant", (IMayHaveTenant t, Guid? tenantId) => t.TenantId == tenantId, Guid.Empty);

Notice this is not doing any null checks at all. The library sees that you are working with a nullable type. So it automatically includes the necessary "is null" and "is not null" checks as necessary. This is the resulting SQL I get for both tests (one with tenantId set to a guid value and one with it set to null):

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[TenantId] AS [TenantId]
    FROM [dbo].[ProductTypes] AS [Extent1]
    WHERE ((([Extent1].[TenantId] IS NULL) AND (@DynamicFilterParam_000001 IS NULL)) OR (([Extent1].[TenantId] IS NOT NULL) AND (([Extent1].[TenantId] = @DynamicFilterParam_000001) OR (([Extent1].[TenantId] IS NULL) AND (@DynamicFilterParam_000001 IS NULL)))) ) AND (1 = [Extent1].[Id])',N'@DynamicFilterParam_000001 uniqueidentifier,@DynamicFilterParam_000002 bit',@DynamicFilterParam_000001='FFFFFFFF-FFFF-FFFF-FFFF-000000000000',@DynamicFilterParam_000002=NULL

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[TenantId] AS [TenantId]
    FROM [dbo].[ProductTypes] AS [Extent1]
    WHERE ((([Extent1].[TenantId] IS NULL) AND (@DynamicFilterParam_000001 IS NULL)) OR (([Extent1].[TenantId] IS NOT NULL) AND (([Extent1].[TenantId] = @DynamicFilterParam_000001) OR (([Extent1].[TenantId] IS NULL) AND (@DynamicFilterParam_000001 IS NULL)))) ) AND (2 = [Extent1].[Id])',N'@DynamicFilterParam_000001 uniqueidentifier,@DynamicFilterParam_000002 bit',@DynamicFilterParam_000001=NULL,@DynamicFilterParam_000002=NULL

Please make these adjustments and re-test. If you still see problems, please double check your actual code against what you posted to see if there are any discrepancies.

[jcachat]

Just pushed out a new NuGet package with the fix for your first issue. Let me know how that works.

[lewyue]

Glad for release pack,
I want to show test project here ,bug zip file upload is ban

[lewyue]

Q2,Q3 i have test in 2.5.0 and now is no problem,i just have some suggestion in sql

Here is entity

    public class ProductType : ISoftDelete, IMayHaveTenant
    {
        public ProductType()
        {
        }

        public int Id { get; set; }

        public string Name { get; set; }

        public int? ParentId { get; set; }

        [ForeignKey("ParentId")]
        public ProductType Parent { get; set; }
        [InverseProperty("Parent")]
        public ICollection<ProductType> Children { get; set; }

        public bool IsDeleted { get; set; }

        public Guid? TenantId { get; set; }

    }

Filter :

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

    public interface IMayHaveTenant
    {
        Guid? TenantId { get; set; }
    }

InitData

            ProductType productType1 = new ProductType()
            {
                Id= 1,
                Name = "1",
                ParentId = null,
                IsDeleted = false,
                TenantId = new Guid("ffffffff-ffff-ffff-ffff-000000000000"),
            };
            ProductType productType2 = new ProductType()
            {
                Id= 2,
                Name = "2",
                ParentId = 1,
                IsDeleted = false,
                TenantId = new Guid("ffffffff-ffff-ffff-ffff-000000000000"),
            };
            ProductType productType3 = new ProductType()
            {
                Id= 3,
                Name = "3",
                ParentId = 1,
                IsDeleted = true,
                TenantId = null,
            };
            ProductType productType4 = new ProductType()
            {
                Id= 4,
                Name = "4",
                ParentId = 1,
                IsDeleted = true,
                TenantId = new Guid("ffffffff-ffff-ffff-ffff-000000000000"),
            };
            ProductType productType5 = new ProductType()
            {
                Id= 5,
                Name = "5",
                ParentId = 1,
                IsDeleted = false,
                TenantId = null,
            };

DbContext filter set,both used,and same effect in sql:
modelBuilder.Filter("IMayHaveTenant", (IMayHaveTenant t, Guid? tenantId) => t.TenantId == tenantId || !t.TenantId.HasValue, Guid.Empty);

modelBuilder.Filter("IMayHaveTenant", (IMayHaveTenant t, Guid? tenantId) => t.TenantId == tenantId || (Guid?)t.TenantId == null, Guid.Empty);

Now is the first linq query:

            context.SetFilterGlobalParameterValue("IMayHaveTenant", "TenantId", (Guid?)null);
            var type3 = context.ProductTypes
                .FirstOrDefault(t => t.Id == 4);
            Console.WriteLine(string.Format("Entity loaded:{0}", type3 != null));

I have see 2.5.0 can get entity ,so Q2 now just some suggestion for optimize ,sql is

SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[ParentId] AS [ParentId], 
    [Extent1].[IsDeleted] AS [IsDeleted], 
    [Extent1].[TenantId] AS [TenantId]
    FROM [dbo].[ProductTypes] AS [Extent1]
    WHERE 
	(
		(
			([Extent1].[TenantId] IS NULL) AND (@DynamicFilterParam_000003 IS NULL)
		) 
		OR 
		(
			([Extent1].[TenantId] IS NOT NULL) 
			AND 
			(
				([Extent1].[TenantId] = @DynamicFilterParam_000003) 
				OR 
				(
					([Extent1].[TenantId] IS NULL) 
					AND 
					(@DynamicFilterParam_000003 IS NULL)
				)
			)
		) 
		OR 
		([Extent1].[TenantId] IS NULL) 
	) 
	AND 
	(
		([Extent1].[IsDeleted] = @DynamicFilterParam_000001) 
	) 
	AND 
	(4 = [Extent1].[Id])

The suggest sql is

SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[ParentId] AS [ParentId], 
    [Extent1].[IsDeleted] AS [IsDeleted], 
    [Extent1].[TenantId] AS [TenantId]
    FROM [dbo].[ProductTypes] AS [Extent1]
    WHERE 
	(
		(
			([Extent1].[TenantId] IS NULL) AND (@DynamicFilterParam_000003 IS NULL)
		) 
		OR 
		(
			([Extent1].[TenantId] IS NOT NULL) 
			AND 
			([Extent1].[TenantId] = @DynamicFilterParam_000003) 
		) 
	) 
	AND 
	(
		([Extent1].[IsDeleted] = @DynamicFilterParam_000001) 
	) 
	AND 
	(4 = [Extent1].[Id])

[lewyue]

Now continute for Q3 and up
Here is linq query

            type2 = context.ProductTypes
                .Include(t => t.Parent)
                .FirstOrDefault(t => t.Id == 2);
            Console.WriteLine(string.Format("Refer loaded:{0}", type2.Parent != null));

and use 2.5.0,sql is

SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[Name] AS [Name], 
    [Limit1].[ParentId] AS [ParentId], 
    [Limit1].[IsDeleted] AS [IsDeleted], 
    [Limit1].[TenantId] AS [TenantId], 
    [Limit1].[Id1] AS [Id1], 
    [Limit1].[Name1] AS [Name1], 
    [Limit1].[ParentId1] AS [ParentId1], 
    [Limit1].[IsDeleted1] AS [IsDeleted1], 
    [Limit1].[TenantId1] AS [TenantId1]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[ParentId] AS [ParentId], 
        [Extent1].[IsDeleted] AS [IsDeleted], 
        [Extent1].[TenantId] AS [TenantId], 
        [Extent2].[Id] AS [Id1], 
        [Extent2].[Name] AS [Name1], 
        [Extent2].[ParentId] AS [ParentId1], 
        [Extent2].[IsDeleted] AS [IsDeleted1], 
        [Extent2].[TenantId] AS [TenantId1]
        FROM  [dbo].[ProductTypes] AS [Extent1]
        LEFT OUTER JOIN [dbo].[ProductTypes] AS [Extent2] 
		ON 
		(
			(
				([Extent2].[TenantId] IS NULL) 
				AND (@DynamicFilterParam_000003 IS NULL)
			) 
			OR 
			(
				([Extent2].[TenantId] IS NOT NULL) 
				AND 
				(
					([Extent2].[TenantId] = @DynamicFilterParam_000003) 
					OR 
					(
						([Extent2].[TenantId] IS NULL) 
						AND 
						(@DynamicFilterParam_000003 IS NULL)
					)
				)
			) 
			OR 
			([Extent2].[TenantId] IS NULL) 
		) 
		AND 
		(
			([Extent2].[IsDeleted] = @DynamicFilterParam_000001) 
		) 
		AND 
		([Extent1].[ParentId] = [Extent2].[Id])
        WHERE 
		(
			(
				([Extent1].[TenantId] IS NULL) 
				AND 
				(@DynamicFilterParam_000003 IS NULL)
			) 
			OR 
			(
				([Extent1].[TenantId] IS NOT NULL) 
				AND 
				(
					([Extent1].[TenantId] = @DynamicFilterParam_000003) 
					OR 
					(
						([Extent1].[TenantId] IS NULL) 
						AND 
						(@DynamicFilterParam_000003 IS NULL)
					)
				)
			) 
			OR 
			([Extent1].[TenantId] IS NULL) 
		) 
		AND 
		(
			([Extent1].[IsDeleted] = @DynamicFilterParam_000001) 
		) 
		AND 
		(2 = [Extent1].[Id])
    )  AS [Limit1]

and suggest sql

 SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[ParentId] AS [ParentId], 
        [Extent1].[IsDeleted] AS [IsDeleted], 
        [Extent1].[TenantId] AS [TenantId], 
        [Extent2].[Id] AS [Id1], 
        [Extent2].[Name] AS [Name1], 
        [Extent2].[ParentId] AS [ParentId1], 
        [Extent2].[IsDeleted] AS [IsDeleted1], 
        [Extent2].[TenantId] AS [TenantId1]
        FROM  [dbo].[ProductTypes] AS [Extent1]
        LEFT OUTER JOIN [dbo].[ProductTypes] AS [Extent2] 
		ON 
		(
			(
				([Extent2].[TenantId] IS NULL) 
				AND (@DynamicFilterParam_000003 IS NULL)
			) 
			OR 
			(
				([Extent2].[TenantId] IS NOT NULL) 
				AND 
				([Extent2].[TenantId] = @DynamicFilterParam_000003) 
			)
		) 
		AND 
		(
			([Extent2].[IsDeleted] = @DynamicFilterParam_000001) 
		) 
		AND 
		([Extent1].[ParentId] = [Extent2].[Id])
        WHERE 
		(
			(
				([Extent1].[TenantId] IS NULL) 
				AND 
				(@DynamicFilterParam_000003 IS NULL)
			) 
			OR 
			(
				([Extent1].[TenantId] IS NOT NULL) 
				AND 
				([Extent1].[TenantId] = @DynamicFilterParam_000003) 
			) 
			OR 
			([Extent1].[TenantId] IS NULL) 
		) 
		AND 
		(
			([Extent1].[IsDeleted] = @DynamicFilterParam_000001) 
		) 
		AND 
		(2 = [Extent1].[Id])

[lewyue]

I have test 2.5.0,found Q2 and Q3 is miss, so just some sql optimize suggestion.
Tks for you grate job !

[jcachat]

I don't have fine grained control over how EF builds the actual SQL. All I can do is construct it using what EF exposes (which is database agnostic) and it will generate the SQL as best it can (which is database specific). So while the SQL you are suggesting is certainly how I would write it myself, I can't make EF do that.

Just to be clear: The change I made was to fix Q1 but you didn't mention that. Is that fixed for you? And the changes did not affect Q2 & Q3 (I don't think) but you say that is also working correctly now?

So everything is now working other than the non-optimal SQL which I can't control?

[lewyue]

Yes , I test 2.5.0 Q2 and Q3 is working corredtly now.
I have use the same test code for Q2 and Q3,but did't catch error in sql
So i think , maybe Q2 Q3 cause by the same Q1 reason in my test project.

And now i'll try to up version to 2.5.0 ,TKS!

[jcachat]

I believe this is all resolved except for the non-optimal SQL (the "outer apply") which is covered by issue #72. So closing this for now - if there is other information or still problems, please reopen or post a new issue.