Query: Include doesn't work with SelectMany

[Bartmax]

Steps to reproduce

return context.SiteBanners.Include(sb => sb.Layers).ThenInclude(l => l.SiteBannerFile)
    .SelectMany(b => b.Layers).Include(l=>l.SiteBannerFile).FirstOrDefaultAsync(l => l.FileId == id);

The issue

this does populate the SiteBannerLayer entity but not the SiteBannerFile.
SiteBannerFile is null.

In the example above I used Include everywhere just in case.

Of course this works:

var banners = await context.SiteBanners.Include(sb => sb.Layers).ThenInclude(l => l.SiteBannerFile).ToListAsync();
return banners.SelectMany(b => b.Layers).FirstOrDefault(l => l.FileId == id);

but that's an inefficient query

Further technical details

EF Core version: 1.0
Operating system: Windows 10
Visual Studio version: 2015

Other details about my project setup:
Here are the entities involved.

public class SiteBanner
{
    public SiteBanner()
    {
        Layers = new List<SiteBannerLayer>();
    }
    public int SiteBannerId { get; set; }

    public string Title { get; set; }

    public ICollection<SiteBannerLayer> Layers { get; set; }
}
public class SiteBannerLayer : ISortableFile
{
    SiteBannerLayer()
    {/*EF*/ }
    public SiteBannerLayer(FileData file, int order = 0)
    {
        if (file == null)
        {
            throw new ArgumentNullException(nameof(file));
        }
        SiteBannerFile = new SiteBannerFile(file);
        Order = order;
    }

    public int SiteBannerLayerId { get; set; }

    [Required]
    public SiteBannerFile SiteBannerFile { get; set; }

    public int Order { get; set; }

    public IFileData File => SiteBannerFile;

    public Guid FileId => SiteBannerFile.SiteBannerFileId;

}
public class SiteBannerFile : IFileData
{
    private const string folder = "SiteBanner";
    public SiteBannerFile() { } /*EF*/
    public SiteBannerFile(FileData fileData)
    {
        SiteBannerFileId = fileData.FileDataId;
        Filename = fileData.Filename;
        OriginalFilename = fileData.OriginalFilename;
        ContentType = fileData.ContentType;
    }

    [Key]
    public Guid SiteBannerFileId { get; private set; }

    [Required]
    public string ContentType { get; set; }

    [Required]
    public string Filename { get; set; }

    public static string Folder => folder;

    public string OriginalFilename { get; set; }

    string IFileData.Folder => folder;

    Guid IFileData.FileDataId => SiteBannerFileId;

}

[maumar]

@Bartmax could you also provide listing for DbContext (especially the OnModelCreating) and IFileData?

[Bartmax]

I have no code OnModelCreating code regarding any included entities.

Here's FileData and IFileData

public interface IFileData
{
    Guid FileDataId { get; }
    string Folder { get; }
    string Filename { get; }
    string ContentType { get; }
}

public class FileData : IFileData
{
    protected FileData() { }
    protected FileData(FileData file) : 
        this(file.FileDataId, file.Folder, file.Filename, file.OriginalFilename, file.ContentType)
    {

    }

    public FileData(Guid id, string folder, string filename, string originalFilename, string contentType)
    {
        if (id == default(Guid)) throw new ArgumentNullException(nameof(id));
        if (string.IsNullOrWhiteSpace(filename)) throw new ArgumentNullException(nameof(filename));
        if (folder == null) throw new ArgumentNullException(nameof(folder));
        FileDataId = id;
        Folder = folder;
        Filename = filename;
        OriginalFilename = originalFilename;
        ContentType = contentType;
    }
    public Guid FileDataId { get; }
    public string Folder { get; }
    public string Filename { get; }
    public string OriginalFilename { get;  }
    public string ContentType { get;  }
}

[maumar]

Verified this is a bug. Simplified repro:

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

                var bf11 = new SiteBannerFile();
                var bf12 = new SiteBannerFile();
                var l11 = new SiteBannerLayer { SiteBannerFile = bf11 };
                var l12 = new SiteBannerLayer { SiteBannerFile = bf12 };
                var b1 = new SiteBanner { Layers = new List<SiteBannerLayer> { l11, l12, } };

                ctx.SiteBanners.AddRange(b1);
                ctx.SiteBannerLayers.AddRange(l11, l12);
                ctx.SiteBannerFiles.AddRange(bf11, bf12);
                ctx.SaveChanges();
            }

            using (var ctx = new MyContext())
            {
                var query = ctx.SiteBanners.Include(sb => sb.Layers).ThenInclude(l => l.SiteBannerFile)
                    .SelectMany(b => b.Layers).ToList();
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<SiteBanner> SiteBanners { get; set; }
        public DbSet<SiteBannerLayer> SiteBannerLayers { get; set; }
        public DbSet<SiteBannerFile> SiteBannerFiles { get; set; }

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

    public class SiteBanner
    {
        public int SiteBannerId { get; set; }

        public ICollection<SiteBannerLayer> Layers { get; set; }
    }
    public class SiteBannerLayer
    {
        public int SiteBannerLayerId { get; set; }

        [Required]
        public SiteBannerFile SiteBannerFile { get; set; }
    }

    public class SiteBannerFile
    {
        public int SiteBannerFileId { get; set; }
    }

[maumar]

@Bartmax as a workaround I would suggest running two queries, one retrieves the entities you are interested in, and the second one does the proper include:

var query1 = context.SiteBanners.SelectMany(b => b.Layers).Where(l => l.FieldId == id).Select(l => l.SiteBannerLayerId).FirstOrDefault();

var query2 = context.SiteBannerLayers.Include(l=>l.SiteBannerFile).Where(l => l.SiteBannerLayerId == query1);

[divega]

Clearing the milestone because at this point the fix does not seem to fit in 1.1.0-preview1. We can discuss the appropriate milestone in triage.

[maumar]

Fixed in 93ecfec

[michaelaird]

Any chance this fix will get ported back to EF 6?

[ajcvickers]

@michaelaird EF Core is an entirely different codebase than EF6, so any change to fix a similar issue in EF6 would be a different change, not just a port of this fix. It's very unlikely that this will change in EF6.

[michaelaird]

I figured as much. We’re waiting on lifecycle hooks then we’ll be able to migrate.

…

On Fri, Dec 15, 2017 at 4:08 PM Arthur Vickers ***@***.***> wrote: @michaelaird <https://github.com/michaelaird> EF Core is an entirely different codebase than EF6, so any change to fix a similar issue in EF6 would be a different change, not just a port of this fix. It's very unlikely that this will change in EF6. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#6451 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AANX6XmKJQj-6MAYR9CBPmgnsztUfZ_Hks5tAt_AgaJpZM4JzCKD> .

[hieplam]

Hi, does this bug fixed, could you give me the version of ef core where this bug was fixed.

[ajcvickers]

@hieplam In the labels there is a "closed fixed" tag. This means that it has been fixed. The milestone indicates which release contains the fix--in this case 2.0.0-preview1.

[Tbd19]

Can this issue be reopened for version 2.2.4
In maumar reproduction please insert the code
Console.WriteLine(query.Count(s => s.SiteBannerFile != null) + " " + query.Count);
The two numbers should be equal but they are not. The first one is 0 and the second one is 2.

[smitpatel]

@Tbd19 - It's entirely different bug. Please file a new issue with repro code and version of EF core you are using.