2

Entity Framework Core has yet to implement many-to-many relationships, as tracked in GitHub issue #1368; however, when I follow the navigation examples in that issue or similar answers here at Stack Overflow, my enumeration fails to yield results.

I have a many-to-many relationship between Photos and Tags.

After implementing the join table, examples show I should be able to:

var tags = photo.PhotoTags.Select(p => p.Tag);

While that yields no results, I am able to to load via:

var tags = _context.Photos
    .Where(p => p.Id == 1)
    .SelectMany(p => p.PhotoTags)
    .Select(j => j.Tag)
    .ToList();

Relevant code:

public class Photo
{
    public int Id { get; set; }
    public virtual ICollection<PhotoTag> PhotoTags { get; set; }
}

public class Tag
{
    public int Id { get; set; }
    public virtual ICollection<PhotoTag> PhotoTags { get; set; }
}

public class PhotoTag
{
    public int PhotoId { get; set; }
    public Photo Photo { get; set; }

    public int TagId { get; set; }
    public Tag Tag { get; set; }
}

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

    builder.Entity<PhotoTag>().HasKey(x => new { x.PhotoId, x.TagId });
}

What am I missing from other examples?

Community
  • 1
  • 1
Jason Sturges
  • 15,855
  • 14
  • 59
  • 80
  • Maybe try removing the call to base.OnModelCreating ? I have never made that call when overriding OnModelCreating – Camilo Terevinto May 02 '17 at 17:51
  • Did you tried including `.Include(p => p.PhotoTags)`? EF Core doesn't have lazy loading, so you have to explicit do eager loading via include or or `.Load()` – Tseng May 02 '17 at 18:00
  • @Tseng if you use `PhotoTags.Select(p => p.Tags)`, EF knows that it has to Include PhotoTags, that is not the same as lazy loading – Camilo Terevinto May 02 '17 at 18:01
  • @CamiloTerevinto: I know it does it on select (since it's a projection), not sure it does on `SelectMany` though – Tseng May 02 '17 at 18:02
  • @Tseng `SelectMany` is a shortcut for a double `Select` statement, so yes. I have very complex queries with `Select` and `SelectMany` without `Include` calls – Camilo Terevinto May 02 '17 at 18:04
  • `Select()` fails, whereas `SelectMany()` example works. Using `Include()` followed by a `ThenInclude()` works, which is ghastly. Other examples don't show that syntax, instead citing the `Select()` statement. – Jason Sturges May 02 '17 at 18:13
  • @Tseng is right, it depends how you retrieve the `photo` variable. If you retrieve it this way `var photo = _context.Photos.Include(e => e.PhotoTags).ThenInclude(e => e.Tag).FirstOrDefault(e => e.Id == 1);`, then later `var tags = photo.PhotoTags.Select(p => p.Tag);` will also work. – Ivan Stoev May 02 '17 at 18:28
  • @Ivan - that makes sense, and works - I wonder why such an important note is left out of other citations. I'll accept that answer. – Jason Sturges May 02 '17 at 18:32
  • @CamiloTerevinto: I guess you have the navigation property inside the select though, like `.Select(new { Tags = p.PhotoTags.Select(pt => pt.Tag).ToList())` or something like that. Now `PhotoTags` is within the select clause, which makes Include unnecessary, since it's a projection. – Tseng May 02 '17 at 18:34

1 Answers1

5

In fact this is not a specific for many-to-many relationship, but in general to the lack of lazy loading support in EF Core. So in order to have Tag property populated, it has to be eager (or explicitly) loaded. All this is (sort of) explained in the Loading Related Data section of the EF Core documentation. If you take a look at Including multiple levels section, you'll see the following explanation

You can drill down thru relationships to include multiple levels of related data using the ThenInclude method. The following example loads all blogs, their related posts, and the author of each post.

and example for loading the Post.Author which is pretty much the same as yours:

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
            .ThenInclude(post => post.Author)
        .ToList();
}

So to make this working

var tags = photo.PhotoTags.Select(p => p.Tag);

the photo variable should have been be retrieved using something like this:

var photo = _context.Photos
   .Include(e => e.PhotoTags)
       .ThenInclude(e => e.Tag)
   .FirstOrDefault(e => e.Id == 1);
Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343