Include() not working when filtered with Contains() and aggregating to list

[olivierr91]

Given the following query in EF Core 2.1:

var inventorySummary = myContext.InventoryEntries
    .Include(ie => ie.PurchaseOrderItem).ThenInclude(poi => poi.PurchaseOrder)
    .Where(ie => new HashSet<Guid>() { new Guid("cdd40f38-d3ff-4d2f-be66-ace7f2090745") }.Contains(ie.PurchaseOrderItem.PurchaseOrderId.Value))
    .GroupBy(ie => new { ie.Item, ie.Warehouse })
    .Select(g => new InventorySummaryEntry() {
        Entries = g.ToList()
    })
    .ToListAsync();

One would expect that Entries contains a list of InventoryEntry with their included PurchaseOrderItem, but they do not (PurchaseOrderItem is null):

Assert.NotNull(inventorySummary.Entries.First().PurchaseOrderItem) //Fails

• Removing GroupBy() and Select() clause produces the expected result
• Keeping GroupBy(...) but replacing Where(...) by simple equality (.Where(ie => ie == new Guid("cdd40f38-d3ff-4d2f-be66-ace7f2090745")) also produces the expected result.
• The combination of GroupBy() and Contains() does not produce the expected result.

[ajcvickers]

@smitpatel to reference in 3.0 Include issue and respond to customer.

[smitpatel]

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

namespace EFSampleApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new MyContext())
            {
                // Recreate database
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                // Seed database
                db.Add(
                    new InventoryEntry
                    {
                        Item = 1,
                        Warehouse = 1,
                        PurchaseOrderItem = new PurchaseOrderItem
                        {
                            PurchaseOrder = new PurchaseOrder
                            {
                                Id = new Guid("cdd40f38-d3ff-4d2f-be66-ace7f2090745")
                            }
                        }
                    }
                    );

                db.SaveChanges();
            }

            using (var db = new MyContext())
            {
                // Run queries
                var inventorySummary = db.InventoryEntries
                    .Include(ie => ie.PurchaseOrderItem)
                        .ThenInclude(poi => poi.PurchaseOrder)
                    .Where(ie => new HashSet<Guid>()
                        {
                            new Guid("cdd40f38-d3ff-4d2f-be66-ace7f2090745")
                        }
                        .Contains(ie.PurchaseOrderItem.PurchaseOrderId.Value))
                    .GroupBy(ie => new { ie.Item, ie.Warehouse })
                    .Select(g => new
                    {
                        Entries = g//.ToList()
                    })
                    .ToList();

                Console.WriteLine(inventorySummary[0].Entries.First().PurchaseOrderItem.PurchaseOrder);
            }

            Console.WriteLine("Program finished.");
        }
    }


    public class MyContext : DbContext
    {
        private static ILoggerFactory LoggerFactory => new LoggerFactory().AddConsole(LogLevel.Trace);

        // Declare DBSets
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<InventoryEntry> InventoryEntries { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Select 1 provider
            optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
                //.UseSqlite("filename=_modelApp.db")
                //.UseInMemoryDatabase(databaseName: "_modelApp")
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(LoggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure model
        }
    }

    public class Blog
    {
        public int Id { get; set; }
    }

    public class InventoryEntry
    {
        public int Id { get; set; }
        public int Item { get; set; }
        public int Warehouse { get; set; }
        public PurchaseOrderItem PurchaseOrderItem { get; set; }
    }

    public class PurchaseOrderItem
    {
        public int Id { get; set; }
        public Guid? PurchaseOrderId { get; set; }
        public PurchaseOrder PurchaseOrder { get; set; }
    }

    public class PurchaseOrder
    {
        public Guid Id { get; set; }
    }
}

Apparently we fail to identify where to put Include in QueryModel.

[smitpatel]

Related #12795

[Menighin]

Just hit a similar problem. On the following query, the included ScheduledStopActivity and ScheduledStopActivityTranslations are null if I filter with the Contains. If not, they are defined:

var entities = _context.DbScheduledStop.Include(o => o.ScheduledStopActivity).ThenInclude(s => s.ScheduledStopActivityTranslations)
                 .Include(o => o.Vehicle).Include(o => o.Location)
                 .Where(o => idList.Contains(o.VehicleId))
                 .ToList();

Checking the query, there is no join for those entities.

Is it the same problem? Is there a workaround for this?

[smitpatel]

@Menighin - Can you file a new issue with repro code? Based on initial investigation done on this issue, it was caused because of GroupBy + Contains combination. Since you query does not contain GroupBy, it may be similar issue but with different root cause.

[dracan]

I've just hit the same issue. I'm using GroupJoin. Works as expected if my query doesn't include Contains, however if I use contains, then navigation property is null.

[smitpatel]

Won't fix as per #17068

[kot-pilot]

Just hit a similar problem. On the following query, the included ScheduledStopActivity and ScheduledStopActivityTranslations are null if I filter with the Contains. If not, they are defined:

var entities = _context.DbScheduledStop.Include(o => o.ScheduledStopActivity).ThenInclude(s => s.ScheduledStopActivityTranslations)
                 .Include(o => o.Vehicle).Include(o => o.Location)
                 .Where(o => idList.Contains(o.VehicleId))
                 .ToList();

Checking the query, there is no join for those entities.

Is it the same problem? Is there a workaround for this?

i have the same situation, include is ignored when filtering with 'contains' of some array/list
like:

var entities=context.Items.Include(x=>x.SubItem).Where(x => statuses.Contains(x.Status)).ToList()
and i don't use any GroupBy statements
EF core 2.2.6
Maybe it's already fixed? Or some workaround for that?

[zuckerthoben]

Did you find a solution for the Include with contains problem? @kot-pilot @dracan @Menighin

[dracan]

@zuckerthoben I'm afraid I can't remember what I did for this. I'm no longer working with the company I was with at the time, so I can't even check source control to see what changes I made at that time 😞