9

I have an entity that has a Many-to-Many self-relationship. As an example consider this entity:

public class User
{
    public int ID { get; set; }
    public string UserName { get; set; }

    public virtual ICollection<User> Friends { get; set; }
}

Here is how I configure the mapping:

HasMany(t => t.Friends).WithMany()
    .Map(m => { 
        m.MapLeftKey("UserID");
        m.MapRightKey("FriendID");
        m.ToTable("UserFriends");
        });

As this relationship is now managed by the EF, I don't really have access to the UserFriends DbSet in my code and cannot handle the concurrent access to it. In order for this composition to handle concurrent access(add/remove), do I need to handle the Many-to-Many relationship myself and then add a [Timestamp] column or is there a way to tell EF to handle this concurrently itself? Like a configuration in the model builder.

Edit: I'm using EF 6 and currently if there is a concurrent operation on the entity(e.g trying to remove a friend that currently doesn't exits on the database) I get the following error message and an DbUpdateException:

An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

Farhad Alizadeh Noori
  • 2,066
  • 14
  • 22
  • Could you elaborate a bit more on what kind of concurrent access you have and how conflicts could occur? Eg. do you only have multiple threads inside your application adding/removing or are there processes outside your application also changing the data? – Asad Saeeduddin Apr 22 '15 at 16:38
  • Well it's basically different clients changing these entities at the same time. They can add/remove friends for one of the users registered in the system at the same time. Let's say both client A and client B remove the same friend from a user. This would cause an exception. However, currently I'm only getting a `DBUpdateException` and not an `OptimisticConcurrencyException` which can be properly handled. – Farhad Alizadeh Noori Apr 22 '15 at 16:46
  • Is there a message you get for the `DBUpdateException`? Also, just to be clear, what version of EF are you using? – Asad Saeeduddin Apr 22 '15 at 16:57
  • @Asad please see my edit for the information you requested. – Farhad Alizadeh Noori Apr 22 '15 at 17:04
  • 1
    Unfortunately I think what you want is not possible. See https://efreversepoco.codeplex.com/workitem/53, which is still open. I think you'll have to explicitly manage the mapping yourself and add a timestamp field. – Asad Saeeduddin Apr 22 '15 at 17:18
  • @Asad I see. Throw that in into an answer and I will accept it. – Farhad Alizadeh Noori Apr 22 '15 at 18:31

2 Answers2

4

Optimistic concurrency does not apply here.

A junction table is never updated. Its records are either added or deleted. This means that there are no CRUD operations that need a rowversion.

So in fact, concurrency is fairly easy:

  • Two concurrent users can't add the same association, because the last one will bump into a unique key violation.
  • Two concurrent users can't delete the same association, because the last one will see an exception that an unexpected number of records (0) was affected.
  • As for foreign key problems (adding/removing an association to an entity that was deleted in the mean time). These will also raise exceptions.

So it boils down to handling exceptions and translating them to comprehensible user feedback. All these situations must also be dealt with in situations where updates (and optimistic concurrency) do play a role.

Gert Arnold
  • 93,904
  • 24
  • 179
  • 256
  • 2
    The second scenario is basically what optimistic concurrency is, so it is still applicable here. They should throw a concurrency error if the record you loaded has been deleted since you last communicated with the DB. – Asad Saeeduddin Apr 23 '15 at 00:59
  • @Asad Although in optimistic concurrency control row versions (or any concurrency token) are also compared in delete statements, this still doesn't apply here, because junction records are never updated. There will never be a situation where a delete is refused because of a modified concurrency token. – Gert Arnold Apr 23 '15 at 07:31
  • I think your argument is based on a false premise. The premise being that concurrency does not apply to junction tables. As Asad mentions too, your second scenario is basically a concurrency issue in and of itself. So how can it not apply? Also, `DbUpdateException` and `DbUpdateConcurrencyException` are two different things. Just because there was an update exception I cannot assume that it was because of concurrency issues and handle it that way. In the second scenario EF should throw a 'DbUpdateConcurrencyException' which would provide proper information in the `e` argument. – Farhad Alizadeh Noori Apr 23 '15 at 13:29
  • @GertArnold Actually the first scenario is also a concurrency issue. It isn't that users "can't" add/delete these records...they can. The issue is handling these exceptions which should be of type "DbUpdateConcurrencyException" and they are not. – Farhad Alizadeh Noori Apr 23 '15 at 13:47
  • The premise is that *optimistic* concurrency (using concurrency tokens to signal concurrent updates) does not apply. Of course there is concurrency. Not all concurrency is optimistic concurrency. I'll come back to this later. – Gert Arnold Apr 23 '15 at 15:07
  • @GertArnold Optimistic concurrency just means you let data contention happen and check for it every time you make a change. Rowversions are just one way of testing for contention (they also use comparison of all fields in some scenarios). In the case of a junction table, you don't need a rowversion to check for contention, but you can still have optimistic concurrency if you monitor the number of rows deleted and throw a concurrency error if it is 0. – Asad Saeeduddin Apr 23 '15 at 17:32
  • 1
    Anyway, I think it isn't too useful to argue semantics here. Your answer is basically correct, but what the OP really needs is to be able to tell when an update failed because of a concurrency issue and deal with it. – Asad Saeeduddin Apr 23 '15 at 17:34
  • @Asad OK, apparently we only just disagree on definitions. The fact is that EF chooses not to throw a `DbUpdateConcurrencyException` when a record without a concurrency token appears to have been deleted before before you try to do that. It simply throws this `DbUpdateException` with an inner exception stating "Entities may have been modified or deleted since entities were loaded". Yeah... – Gert Arnold Apr 23 '15 at 17:42
  • Ok, so that really seems to be the solution for @FarhadAlizadehNoori. What is the type of the inner exception? – Asad Saeeduddin Apr 23 '15 at 17:52
1

Although there is no rowversion column on UserFriends table EF is still able to recognize that the cause of DbContext.SaveChanges failure is concurrency problem in case of many-to-many relationship. In such case ef throws OptimisticConcurrencyException exception opaqued with DbUpdateException. Use the following code to catch it:

try
{
    context.SaveChanges();
}
catch (DbUpdateException ex)
{
    if(ex.InnerException is OptimisticConcurrencyException)
    {
        // If you are here then there was concurrency problem in many-to-many relationship
    }
}

You may not configure currently fluent api to automatically include in UserFriends table rowversion column, however after migration is generated you may manually add to UserFriends CreateTable statement definitione of rowversion column:

CreateTable(
    "dbo.UserFriends",
    c => new{
        RowVersion = c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"),
        // other columns definitions
    });

however this does not alter DbContext behaviour when concurrency problem in many-to-many relationship occurs.

mr100
  • 4,072
  • 2
  • 24
  • 34