Query: invalid SQL produced for queries with navigation inside predicate of SelectMany-Where-DefaultIfEmpty() pattern

[SanderRossel]

So I have this query with two LEFT JOINS.
Everything worked as expected when I had only one LEFT JOIN.

var dbQuery = from i in context.Ingredients
              from ii in i.CombinationIngredients.Where(ci => ci.IngredientId == forIngredientId).DefaultIfEmpty()
              select new
              {
                  i.Id,
                  i.Name,
                  Linked = ii != null
              };

However, when I add a second LEFT JOIN I see multiple queries in the database and I get an Exception.

var dbQuery = from i in context.Ingredients
              from ii in i.CombinationIngredients.Where(ci => ci.IngredientId == forIngredientId).DefaultIfEmpty()
              from t in i.Translations.Where(l => l.Language.Iso639_1 == language).DefaultIfEmpty()
              select new
              {
                  i.Id,
                  i.Name,
                  t.Translation,
                  Linked = ii != null
              };

I add a Skip(...).Take(100) elsewhere in my code, but one of the queries is a "SELECT [all columns] FROM Ingredient" making this a HUGE performance hit!
But, what's even worse is that it doesn't work AT ALL.

Notice how I added t.Translation to the return value? I get the error "System.Data.SqlClient.SqlException: 'Invalid column name 'Translation'.'" EF somehow figures out that the column Translation is part of the Language table, while it's part of the IngredientTranslation table (which is what i.Translations points to).
One of the generated queries:

SELECT [i.Translations_groupItem.Language0].[Id], [i.Translations_groupItem.Language0].[Iso639_1], [i.Translations_groupItem.Language0].[Translation]
FROM [Language] AS [i.Translations_groupItem.Language0]

I do have a workaround, which is to just write the full join.

var dbQuery = from i in context.Ingredients
              from ii in i.CombinationIngredients.Where(ci => ci.IngredientId == forIngredientId).DefaultIfEmpty()
              join tran in context.IngredientTranslations.Where(l => l.Language.Iso639_1 == language) on i.Id equals tran.IngredientId into tranGroup
              from t in tranGroup.DefaultIfEmpty()
              select new
              {
                  i.Id,
                  i.Name,
                  t.Translation,
                  Linked = ii != null
              };

I'd prefer to use the much shorter "from x in ..." syntax though. I know this works in the non-core EF version.

Further technical details

EF Core version: 2.1.0-preview1-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.6.7

[smitpatel]

@SanderRossel Share code for your model classes & DbContext. Are you using table splitting?

[SanderRossel]

I'm not doing any table splitting, just pretty basic SQL/LINQ. The following sample can be copy/pasted into a console application and should run out of the box after having installed Microsoft.EntityFrameworkCore.SqlServer and having done a Migration to create the database.

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

namespace EFCoreSample
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EFCoreSampleContext())
            {
                // We need some data or the query will be optimized and a JOIN will be eliminated.
                var apple = new Ingredient { Active = true, Name = "Apple" };
                var pear = new Ingredient { Active = true, Name = "Pear" };
                var dutch = new Language { Active = true, Iso639_1 = "nl", Name = "Dutch" };
                var english = new Language { Active = true, Iso639_1 = "en", Name = "English" };
                context.Add(apple);
                context.Add(pear);
                context.Add(new IngredientIngredient { Ingredient = apple, CombinationIngredient = pear });
                context.Add(dutch);
                context.Add(english);
                context.Add(new IngredientTranslation { Ingredient = apple, Language = dutch, Translation = "Appel" });
                context.Add(new IngredientTranslation { Ingredient = pear, Language = dutch, Translation = "Peer" });
                context.SaveChanges();

                int forIngredientId = 1;
                string query = ""; // Filter results on name.
                string language = "nl";

                int page = 1;
                int pageSize = 100;

                // The second LEFT JOIN with "from x in ..." does not work due to a bug in EF Core...
                // Need to write the complete "join ..." syntax.
                var dbQuery = from i in context.Ingredients
                              from ii in i.CombinationIngredients.Where(ci => ci.IngredientId == forIngredientId).DefaultIfEmpty()
                              from t in i.Translations.Where(l => l.Language.Iso639_1 == language).DefaultIfEmpty()
                                  //join tran in context.IngredientTranslations.Where(l => l.Language.Iso639_1 == language) on i.Id equals tran.IngredientId into tranGroup
                                  //from t in tranGroup.DefaultIfEmpty()
                              select new
                              {
                                  i.Id,
                                  i.Name,
                                  t.Translation,
                                  Linked = ii != null
                              };

                if (!string.IsNullOrWhiteSpace(query))
                {
                    dbQuery = dbQuery.Where(i => i.Name.Contains(query) || i.Translation.Contains(query));
                }

                var ingredients = dbQuery.Select(i => new
                {
                    i.Id,
                    Name = i.Translation ?? i.Name,
                    i.Linked
                }).OrderBy(i => i.Name)
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .ToList();
            }
        }
    }

    public class EFCoreSampleContext : DbContext
    {
        public virtual DbSet<Ingredient> Ingredients { get; set; }
        public virtual DbSet<IngredientTranslation> IngredientTranslations { get; set; }
        public virtual DbSet<IngredientIngredient> IngredientIngredients { get; set; }

        public virtual DbSet<Language> Languages { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.\;Database=EFCoreDemo;Trusted_Connection=True;MultipleActiveResultSets=true");
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder.Entity<Ingredient>().ToTable(nameof(Ingredient));
            builder.Entity<Ingredient>()
                .HasMany(e => e.IngredientIngredients)
                .WithOne(e => e.Ingredient)
                .OnDelete(DeleteBehavior.Restrict);
            builder.Entity<Ingredient>()
                .HasMany(e => e.CombinationIngredients)
                .WithOne(e => e.CombinationIngredient)
                .OnDelete(DeleteBehavior.Restrict);

            builder.Entity<IngredientIngredient>().ToTable(nameof(IngredientIngredient));
            builder.Entity<IngredientIngredient>()
                .HasOne(ii => ii.Ingredient)
                .WithMany(i => i.IngredientIngredients)
                .OnDelete(DeleteBehavior.Restrict);
            builder.Entity<IngredientIngredient>()
                .HasOne(ii => ii.CombinationIngredient)
                .WithMany(i => i.CombinationIngredients)
                .OnDelete(DeleteBehavior.Restrict);

            builder.Entity<IngredientTranslation>().ToTable(nameof(IngredientTranslation));

            builder.Entity<Language>().ToTable(nameof(Language));
        }
    }

    public class Ingredient
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool Active { get; set; }

        public virtual ICollection<IngredientTranslation> Translations { get; set; }
        public virtual ICollection<IngredientIngredient> IngredientIngredients { get; set; }
        public virtual ICollection<IngredientIngredient> CombinationIngredients { get; set; }
    }

    public class IngredientIngredient
    {
        public int Id { get; set; }
        public int IngredientId { get; set; }
        public Ingredient Ingredient { get; set; }
        public int CombinationIngredientId { get; set; }
        public Ingredient CombinationIngredient { get; set; }
    }

    public class IngredientTranslation
    {
        public int Id { get; set; }
        public string Translation { get; set; }

        public int IngredientId { get; set; }
        public Ingredient Ingredient { get; set; }

        public int LanguageId { get; set; }
        public Language Language { get; set; }
    }

    public class Language
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool Active { get; set; }
        public string Iso639_1 { get; set; }
    }
}

[divega]

@SanderRossel was this working for you with a previous version?

[maumar]

Problem is the navigation inside the second DefaultIfEmpty(), if the query is simplified to:

from i in ctx.Ingredients
from ii in i.CombinationIngredients.Where(ci => ci.IngredientId == forIngredientId).DefaultIfEmpty()
from t in i.Translations.Where(l => l.LanguageId == 1).DefaultIfEmpty()
select new
{
   i.Id,
   i.Name,
   t.Translation,
   Linked = ii != null
};

we have no problem translating it:

SELECT [i].[Id], [i].[Name], [t].[Translation], CASE
                WHEN [t0].[Id] IS NOT NULL
                THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
            END AS [Linked]
            FROM [Ingredient] AS [i]
            LEFT JOIN (
                SELECT [i.Translations].*
                FROM [IngredientTranslation] AS [i.Translations]
                WHERE [i.Translations].[LanguageId] = 1
            ) AS [t] ON [i].[Id] = [t].[IngredientId]
            LEFT JOIN (
                SELECT [i.CombinationIngredients].*
                FROM [IngredientIngredient] AS [i.CombinationIngredients]
                WHERE [i.CombinationIngredients].[IngredientId] = @__forIngredientId_0
            ) AS [t0] ON [i].[Id] = [t0].[CombinationIngredientId]

[maumar]

Simplified repro:

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var apple = new Ingredient { Active = true, Name = "Apple" };
                var pear = new Ingredient { Active = true, Name = "Pear" };
                var dutch = new Language { Active = true, Iso639_1 = "nl", Name = "Dutch" };
                var english = new Language { Active = true, Iso639_1 = "en", Name = "English" };
                ctx.Add(apple);
                ctx.Add(pear);
                ctx.Add(dutch);
                ctx.Add(english);
                ctx.Add(new IngredientTranslation { Ingredient = apple, Language = dutch, Translation = "Appel" });
                ctx.Add(new IngredientTranslation { Ingredient = pear, Language = dutch, Translation = "Peer" });
                ctx.SaveChanges();
            }

            using (var ctx = new MyContext())
            {
                string language = "nl";

                var query = from i in ctx.Ingredients
                            from t in i.Translations.Where(l => l.Language.Iso639_1 == language).DefaultIfEmpty()
                            select new
                              {
                                  i.Id,
                                  i.Name,
                                  t.Translation,
                              };

                var result = query.ToList();
            }
        }
    }

    public class MyContext : DbContext
    {
        public virtual DbSet<Ingredient> Ingredients { get; set; }
        public virtual DbSet<IngredientTranslation> IngredientTranslations { get; set; }
        public virtual DbSet<Language> Languages { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.;Database=Repro11847;Trusted_Connection=True;MultipleActiveResultSets=True");
        }
    }

    public class Ingredient
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool Active { get; set; }

        public virtual ICollection<IngredientTranslation> Translations { get; set; }
    }

    public class IngredientTranslation
    {
        public int Id { get; set; }
        public string Translation { get; set; }

        public int IngredientId { get; set; }
        public Ingredient Ingredient { get; set; }

        public int LanguageId { get; set; }
        public Language Language { get; set; }
    }

    public class Language
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool Active { get; set; }
        public string Iso639_1 { get; set; }
    }

[maumar]

Verified that this is not a regression - same problem happens on 2.0

[SanderRossel]

The problem happens on EF Core, but not on the full .NET EF. For some reason I thought it happened because there were more than one LEFT JOINs. Glad you found the problem and were able to simplify the example! For now I have a workaround, but I'll be very happy when there's a fix available.

[maumar]

EFCore was written from scratch so it usually doesn't share issues with EF6. We will definitely fix this - navigation inside predicate of the LEFT JOIN pattern is a compelling scenario so it should work. However at this point we only accept critical issues and regressions for the 2.1 release, so the fix will most likely land after 2.1 has shipped.

[maumar]

currently blocked by #15711 - SelectMany translation

[maumar]

verified this issue has been fixed in 3.0