QueryFilterManager.AllowPropertyFilter: wrong SQL join statement

[GertArnold]

QueryFilter 1.7.15, EF 6.2.0, .Net 4.7

I'm a bit wary of complaining about this awesome piece of work, but I hope it will help make it even better.

tl;dr QueryFilterManager.AllowPropertyFilter = true joins navigation properties incorrectly.

I have a class model of Planets (parent) + Moons (child). Here's an excerpt of Planet:

public class Planet
{
    public int ID { get; set; }
    public bool Inhabitable { get; set; }
    public ICollection<Moon> Moons { get; set; }
}

I define a QueryFilter in the context hosting this class model and enable AllowPropertyFilter:

db.Filter<Planet>(items => items.Where(p => p.Inhabitable == true));
QueryFilterManager.AllowPropertyFilter = true;

Now when I run this query (in Linqpad)...

db.Set<Moon>().Select(m => new { m.Name, Planet = m.Planet.Name }).Dump();

...the expected ouput is:

Moon Earth Phobos NULL Deimos NULL

But the actual output is:

Moon Earth Phobos Earth Deimos Earth

This is because the generated SQL query is way off:

SELECT 
	1 AS [C1], 
	[Extent1].[Name] AS [Name], 
	[Limit1].[Name] AS [Name1]
	FROM  [dbo].[Moons] AS [Extent1]
	LEFT OUTER JOIN  (SELECT TOP (1) [Extent2].[Name] AS [Name]
		FROM [dbo].[Planets] AS [Extent2]
		WHERE 1 = [Extent2].[Inhabitable] ) AS [Limit1] ON 1 = 1

As you see, the join doesn't even join the primary/foreign key fields. For each moon the first inhabitable planet the database can find is returned.

This is the full Linqpad program to reproduce the issue (just add the NuGet packages).

void Main()
{
    // Create & configure ModelBuilder.
    var mb = new DbModelBuilder();
    mb.Entity<Planet>();

    // Create DbModel.
    var ldbf = new LocalDbConnectionFactory("12", "test");
    using (var conn = ldbf.CreateConnection(@"Server=(localDB)\MSSQLLocalDB;Integrated Security=true;database=planetsPlus"))
    {
        DbModel dbModel = mb.Build(conn);

        // Create compiled model.
        DbCompiledModel compiledModel = dbModel.Compile();

        // Create & configure DbContext.
        Database.SetInitializer<DbContext>(null);
        using (DbContext db = new DbContext(conn, compiledModel, false))
        {
            db.Database.Delete();
            db.Database.Create();

            var earth = new Planet { Name = "Earth", InsertDate = DateTime.Now, Inhabitable = true,
                Characteristics = PlanetCharacteristics.Atmosphere | PlanetCharacteristics.LiquidWater | PlanetCharacteristics.MagnetoSphere | PlanetCharacteristics.Solid };
            earth.Moons.Add(new Moon { Name = "Moon" });
            var mars = new Planet
            {
                Name = "Mars",
                InsertDate = DateTime.Now,
                Characteristics = PlanetCharacteristics.Atmosphere | PlanetCharacteristics.Solid
            };
            mars.Moons.Add(new Moon { Name = "Phobos" });
            mars.Moons.Add(new Moon { Name = "Deimos" });

            db.Set<Planet>().Add(earth);
            db.Set<Planet>().Add(mars);

            db.SaveChanges();
        }
        using (DbContext db = new DbContext(conn, compiledModel, false))
        {
            db.Configuration.ProxyCreationEnabled = false;

            db.Filter<Planet>(items => items.Where(p => p.Inhabitable == true));
            //QueryFilterManager.InitilizeGlobalFilter(db);
            QueryFilterManager.AllowPropertyFilter = true;

            db.Database.Log = s => s.Dump();
            db.Set<Moon>().Select(m => new { m.Name, Planet = m.Planet.Name }).Dump();
            db.Set<Moon>().Include(m => m.Planet).Dump();
            db.Set<Planet>().Select(p => p.Name).Dump();
        }
    }
}

// Define other methods and classes here
public class Planet
{
    public Planet()
    {
        Moons = new HashSet<Moon>();
    }
    public int ID { get; set; }
    public string Name { get; set; }
    public bool Inhabitable { get; set; }
    public DateTime InsertDate { get; set; }
    public PlanetCharacteristics Characteristics { get; set; }
    public ICollection<Moon> Moons { get; set; }
}

public class Moon
{
    public int ID { get; set; }
    public int PlanetID { get; set; }
    public string Name { get; set; }
    public Planet Planet { get; set; }
}

[Flags]
public enum PlanetCharacteristics
{
    Solid = 1,
    Atmosphere = 2,
    LiquidWater = 4,
    MagnetoSphere = 8
}

[GertArnold]

Forgot to mention: I'm planning to use QueryFilter in a database-first context, so I can't use the superior DynamicFilters.

[JonathanMagnan]

Hello @GertArnold ,

Thank you for reporting.

You are right, this property doesn't work correctly.

We tried to support it but so far, we didn't find any solution. Honestly, I'm not sure if we will be able to fix or not.

Best Regards,

Jonathan

[heku]

I have the same problem, sad to hear EFP not support this so far. So is there any workaround work for this?

[raimilcruz]

Is this issue somehow related to the issue #206? For the issue #206 the proposal to workaround is to use the library EntityFramework.DynamicFilters ...