In EF Core 5, how can I insert an entity with a many to many relation by setting only the foreigns keys IDs, without querying first?

user_5410589 0
Viewed 9047

The other table contain references data with well know ID. The use case is to read data from file, create entities then insert them in batch. I don't need to query anything first, so all entities are "disconnected" from context.

Simple exemple:

public class Post
{
    public int ID { get; set; }
    public string Text { get; set; }
    public virtual ICollection<Tag> Tags { get; set; } 
}

public class Tag
{
    public int ID { get; set; }
    [Required]
    public string Label { get; set;}
    public virtual ICollection<Post> Posts { get; set; } 
}

First try

List<Post> posts = new List<Post>();

loop
  var post = new Post { Text = "some text"});
  post.Tags.Add(new Tag {ID = 1});  
  post.Tags.Add(new Tag {ID = 2});
  posts.Add(post);
...

context.Posts.AddRange(posts);
context.SaveChange();

Error because EF try to update the tags record by setting the other tag column to null. I don't want EF to update the tag table anyway, only the join table.

Second try
After reading Long story short: Use Foreign key and it will save your day, I did not find a way to make it work with a collection of FKs because in my case it's a many to many relation.

Third try
Instead of using context.Post.AddRange(posts);, I attach only the parent entity:

var post = new Post { Text = "some text"});
post.Tags.Add(new Tag {ID = 1});
post.Tags.Add(new Tag {ID = 2});
context.Posts.Attach(post).State = EntityState.Added;
context.SaveChanges();

That worked. Post is inserted, and the joining table PostsTags contain the relation data, with the Tags table left untouched. BUT that will not work in batch (same context) because I can't then create another post with the same tag. The context "track" the tags by their ID, so I can't insert a "new" one with the same ID.

Fourth try
What I'm doing right now is instead of adding a new Tag post.Tags.Add(new Tag {ID = 1});, I add the tag from the db post.Tags.Add(context.Tags.Find(1)); That means many trips to database, for information that is already knows.

Others options I think of is to keep a local dictionnary of tag that are already attached to context, change context between each post, find a way to insert data directly into the entity type that represent the join table, query all references beforehand (but some references tables contains thousand of elements) or simply juste use raw sql query.

I can't imagine that there is no simple way to insert a model with Fk ids, like it work for a one to many by using a Foreign Key property.

Thank you

entity-framework-core .net-core c#
2 Answers

The issue will be due to the tracking, or lack of tracking on the Tags. Since you don't want to query the database, then you can opt to Attach tag instances that you can guarantee are legal tag rows. If you have a reasonable # of Tag IDs to use you could create and attach the full set to reference. Otherwise you could derive it from the data IDs coming in.

I.e. if we have 20 Tags to select from, ID 1-20:

for (int tagId = 1; tagId <= 20; tagId++)
{
    var tag = new Tag { Id = tagId };
    context.Tags.Attach(tag);
}

We don't need to track these tags separately in a list. Once associated with the DbContext we can use context.Tags, or to be extra cautious about reads, context.Tags.Local then when populating your Posts:

var post = new Post { Text = "some text"});
post.Tags.Add(context.Tags.Local.Single(x => x.Id == 1));  
post.Tags.Add(context.Tags.Local.Single(x => x.Id == 2));  
posts.Add(post);
//...

context.Posts.AddRange(posts);

If you have a large # of tags and pass a structure in for the posts that nominate the Tag IDs you want to associate with each new post, then you can build a list from that:

var tags = postViewModels.SelectMany(x => x.TagIds)
    .Distinct()
    .Select(t => new Tag { Id == t)).ToList();

Such as the case where a provided set of post ViewModels contains a list of TagIds. We select all of the distinct Tag IDs, then build Tags to associate.

The caveat here is if the DbContext might already by tracking a Tag with any of the desired IDs. Calling Attach for a Tag that the DbContext might already have loaded will result in an exception. Whether you build a complete set of tags or build a set from the provided post, the solution should check the DbContext for any locally cached/tracked tags and only attach ones that aren't already tracked.

var tags = postViewModels.SelectMany(x => x.TagIds)
    .Distinct()
    .Select(t => new Tag { Id == t))
    .ToList();
foreach(var tag in tags)
{
    if (!context.Tags.Local.Any(x => x.TagId == tag.Id))
        context.Tags.Attach(tag);
}

There may be a better way to build the Tags to attach to exclude existing tracked tags (such as using Except, though that requires an EqualityComparer) but we guard against attaching a Tag that is already tracked. From there we create the Posts and associate the desired tags as per the first example using context.Tags.Local. Every tag referenced in each post should have been attached or already tracked and available.

The remaining caveat here is that this assumes that the provided Tag actually exists in the database. We don't want to set the attached Tag's EntityState to anything like Added or Modified to avoid creating incomplete/invalid or replacing data in the Tags table.

user_4234970

When you have Many-to-Many Relationship between Post and Tag, EFCore Automatically adds a table to store that relationship. You will need to add that table manually using a POCO and then use that table to add your relationship. As shown here

Here is the relevant code:

Create a class that contains the relationship.

public class PostTag
{

    public int PostId { get; set; }
    public Post Post { get; set; }

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

Then in OnModelCreate method, Add the relationship like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PostTag>()
            .HasKey(t => new { t.PostId, t.TagId });

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Post)
            .WithMany(p => p.PostTags)
            .HasForeignKey(pt => pt.PostId);

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Tag)
            .WithMany(t => t.PostTags)
            .HasForeignKey(pt => pt.TagId);
    }

Alternatively, you can use annotations to set the relationship using [ForeignKey(nameof())] attribute

Then you can manually add the PostTag entity with the required data.

user_37854400
Related