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

[orobert91]

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 thatEntries contains a list of InventoryEntry with their includedPurchaseOrderItem, 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]

```C#
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 😞