CODEWITHSUNDEEP
linqentity-frameworkef-code-firstmany-to-many
Ray
Ray

Reputation: 12441

Entity Framework Many to Many works but Include does not

I have a typical many-to-many relationship with these 3 tables

[Post] (
  [PostId] int, (PK)
  [Content] nvarchar(max)
   ...
)

[Tag] (
  [TagId] int, (PK)
  [Name] nvarchar
  ...
)

[TagPost] (
  [TagId] int, (PK, FK)
  [PostId] int (PK, FK)
)

And, TagId and PostId are the PK and FK set on the tables accordingly etc. Then I have these classes and mapping in c#

public class Post {
    public Post()
    {
        this.Tags = new HashSet<Tag>();
    }

    [Key]
    public int PostId { get; set; }
    ...

    public virtual ICollection<Tag> Tags { get; private set; }
}

public class Tag {
    public Tag()
    {
        this.Posts = new HashSet<Post>();
    }

    [Key]
    public int TagId { get; set; }
    ...

    public virtual ICollection<Post> Posts { get; private set; }
}

internal class MyDbContext : DbContext
{
    public DbSet<Post> Posts { get; set; }
    public DbSet<Tag> Tags { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Post>().ToTable("Post");
        modelBuilder.Entity<Tag>().ToTable("Tag");

        modelBuilder.Entity<Post>()
            .HasMany(x => x.Tags)
            .WithMany(x => x.Posts)
        .Map(x =>
        {
            x.ToTable("TagPost");
            x.MapLeftKey("PostId");
            x.MapRightKey("TagId");
        });
    }

Then I have this code to query them

var list = (from p in ctx.Posts.Include(p => p.Tags)
            from t in p.Tags
            where ... // some of my filter conditions
            select p).ToList();

This join does return the posts I was looking for, however the returned posts don't their associated tags filled in even though I have the Include there. Could someone help point out what I'm missing so that I could have the tags also return with the posts?

Thanks a lot.

Upvotes: 4

Views: 7823

Answers (2)

Slauma
Slauma

Reputation: 177133

The double from is a manual Join which causes the Include to be ignored as mentioned here and here. Include is also ignored for other LINQ methods like grouping and projections.

Relationship fixup generally does not work for many-to-many relationships, only for relationships which have at least one single reference at one of the ends - one-to-many or one-to-one. If you project the Posts and related Tags into another type (anonymous or named) the data will be loaded correctly but because the relationship is many-to-many EF won't create the relationship in memory automatically so that the post.Tags collection will stay empty.

To get the Include working you must remove the second from from your query and apply the where clause directly to the Post entity parameter, for example like so:

var list = (from p in ctx.Posts.Include(p => p.Tags)
            where p.Tags.Any(t => t.TagId == 1)
            select p).ToList();

The filter by a Tag property is specified in the expression passed into .Any which is an expression with a Tag (t) as parameter.

Upvotes: 3

qujck
qujck

Reputation: 14580

try selecting everything into an anonymous object (something like this)

var list = (
        from p in ctx.Posts
        from t in p.Tags
        where ... // some of my filter conditions
        select new {
            Posts = p,
            Tags = p.Tags
        })
    .ToList();

Based on the feedback to my initial answer and the fact that EF can find the related entities but it is failing to populate the Tags collection I believe the issue lies in the definition of the Tags entity in the Post class.

Try removing the Hashset<> initialiser from the constructors and private from the set declaration:

public virtual ICollection<Tag> Tags { get; set; }

Upvotes: 0

Related Questions