1

For what I searched there are 2 ways to insert an already present record into a ICollection list:

  1. group.Users.Add(db.Users.FirstOrDefault(x=> x.Id = 1));
  2. var to_add = new User{Id: 1}; db.Users.Attach(to_add); group.Users.Add(to_add);

The problem with both the above approach is it makes a db call every time we want to add a record. While we already know the user's Id and the group's id and that's all it needs to create a relationship.

Imagine a long list to be added, both the above methods would make multiple calls to db.

Shyamal Parikh
  • 2,988
  • 4
  • 37
  • 78
  • Option 1 => Only one call to the DB because of `db.Users.FirstOrDefault(x=> x.Id = 1)` Option 2 => No call to the DB. Side Note : Attach not needed there. – CodeNotFound Jun 11 '18 at 08:10
  • @CodeNotFound Note, the user model doesn't only include `Id` column it also includes `Name`,`Description`, etc. If I don't add them wouldn't EF give a validity error? – Shyamal Parikh Jun 11 '18 at 09:01
  • The best way to understand what going on is to have a sample code that represents what you really have in your actual project. Also you can profile and validate what you're saying. – CodeNotFound Jun 11 '18 at 09:02
  • Yes, since the Name is required, on `group.Users.Add(to_add)` without `attach` ef gives validation error. – Shyamal Parikh Jun 11 '18 at 09:22
  • `Add` implementation already call `Attach` so your error might be something else. – CodeNotFound Jun 11 '18 at 09:23

1 Answers1

0

So you have Groups and Users. Every Group has zero or more Users; every User has zero or more Groups. A traditional many-to-many relationship.

Normally one would add a User to a Group, or a Group to a User. However you don't have a Group, nor a User, you only have a GroupId and a UserId. and because of the large number of insertions you don't want to fetch the Users and the Groups of which you want to create relations

The problem is, if you could add the GroupId-UserId combination directly to your junction table, how would you know that you wouldn't be adding a Group-User relation that already exists? If you wouldn't care, you'd end up with twice the relation. This would lead to problems: Would you want them to be shown twice if you'd ask the Users of a Group? Which one should be removed if the relation ends, or should they all be removed?

If you really want to implement the possibility of double relation, then you'd need to Implement a a Custom Junction Table as described here The extra field would be the number of relations.

This would not help you with your large batch, because you would still need to fetch the field from the custom junction table to increment the NrOfRelations value.

On the other hand, if you don't want double relations, you'd have to check whether the value already exists, and you didn't want to fetch data before inserting.

Usually the number of additions to a database is far less then the number of queries. If you have a large batch of data to be inserted, then it is usually only during the initialization phase of the database. I wouldn't bother optimizing initialization too much.

Consider remembering already fetched Groups and Users in a dictionary, preventing them to be fetched twice. However, if your list is really huge, this is not a practical solution.

If you really need this functionality for a prolonged period of time consider creating a Stored Procedure that checks if the GroupId / UserId already exists in the junction table, and if not, add it.

Harald Coppoolse
  • 28,834
  • 7
  • 67
  • 116
  • Ok, thanks btw the Primary key on the junction table ensures there is no duplication. However, I seriously believe there should be a better option than to fetch records from database just to insert primary keys in the junction table. – Shyamal Parikh Jun 12 '18 at 06:46