MVC inserting multiple records instead of expected one

Question

Let's say I have two models:

public class User
{
    [Key]
    public int UserId { get; set; }
    public string Name { get; set; }
}

and

public class Friend
{
    [Key]
    public int FriendId { get; set; }
    public User A { get; set; }
    public User B { get; set; }
}

Let's say I only have 2 users in my database (ids: 1 (Jon) and 2 (Sam)). Now I insert into table friend like this:

db.Friends.Add(new Friend()
{
    A = db.Users.Find(1),
    B = db.Users.Where(u => u.UserId == 2).First()
});

db.SaveChanges();

Suddenly, I find a user (3, Sam) in a table user. What is the reasoning behind this? Not completely sure if relevant or not, but note that even if I make A and B fields virtual, nothing changes.

UPDATE

Finally found how to reproduce my problem. Apparently the problem isn't exactly the same as I described.

User a, b;

using (var db = new DbConnection())
{
    a = db.Users.First(u => u.UserId == 1);
    b = db.Users.First(u => u.UserId == 2);
}

using (var db = new DbConnection())
{
    db.Friends.Add(new Friend()
    {
        A = a,
        B = b
    });

    db.SaveChanges();
}

Now users will have 4 users. Does it mean that if I step out of transaction, I can no longer access the entities as if they were exactly the same items in the current transaction? Or maybe there is a way to make the program know that I am referring to the same item (because the ID is the same)?

score 1 · Accepted Answer · answered Nov 14 '13 at 01:04

1

Honestly tried the same steps as you described and everything work well.. Anyway my steps

Created a db context class derived from `DbContext'

public class EFContext : DbContext
{
    public DbSet<Friend> Friends { get; set; }
    public DbSet<User> Users { get; set; }

    public EFContext(string connectionString)
        : base(connectionString)
    {

    }

}

I use MSQL2008 Express with win auth so I created the Users table

using (var db = new EFContext(@"Data Source=yourMachineName\SQLEXPRESS2008;Initial Catalog=DBName;Integrated Security=True;MultipleActiveResultSets=True"))
{
    db.Users.Add(new User()
    {
        UserId = 1,
        Name = "John"
    });

    db.Users.Add(new User()
    {
        UserId = 2,
        Name = "Sam"
    });

    db.SaveChanges();
}

I checked my db and found 2 records

After I created the Friends table

using(var db = new EFContext(@"Data Source=yourMachineName\SQLEXPRESS2008;Initial Catalog=DBName;Integrated Security=True;MultipleActiveResultSets=True"))
{
    db.Friends.Add(new Friend()
    {
        A = db.Users.Find(1),
        B = db.Users.Where(u => u.UserId == 2).First()
    });

    db.SaveChanges();
}

Again I got 1 record in the Friends table with columns FriendId=1, A_UserId=1, B_UserId=2. I checked the Users table and I still have 2 records.

If I were you I would try my code in a separate app. If it works then please post here all steps which led you to this problem.

answered Nov 14 '13 at 01:04

Anton

731
4
5

It's funny how I am now trying this on a new project and everything seems to be working. However on a two bigger projects I have been experiencing this all the time. Will try to dig deeper in sql profiler or something this weekend. – Andrius Naruševičius Nov 15 '13 at 06:48
Yes, I finally found how to reproduce. Please take a look at the updated question :) – Andrius Naruševičius Nov 20 '13 at 22:13
Look what you are doing is trying to save the DETACHED entitites! I haven't tried your modified example, but I know what happens. EF just can see them as two NEW entities, not as the entities which are already in a db.So I believe it works only because it's smart enough to think that you want to save new ones! Honestly, it will fail in Hibernate(if you use persist method).You need to reattach them and after I think you don't even need Save Method, because they are already saved in a db. After you can just modify properties and changes will be saved in the db. – Anton Nov 20 '13 at 22:37
I see what is happening. The question that remains is this: how do I reattach them? – Andrius Naruševičius Nov 20 '13 at 22:41
Ah, just a simple `db.Users.Attach(A)` did the trick. Well, you deserve all the rewards I can possibly give to you. This has been bugging me since the start of learning mvc and everything was going pretty smoothly except I never got to solve this. Thanks a lot :) P.S. I will apply bounty as soon as 24 hour period passes :) – Andrius Naruševičius Nov 20 '13 at 22:46
http://www.entityframeworktutorial.net/update-entity-in-entity-framework.aspx... its an example.. I would read it all if I were you. It's a really nice tutorial and it;s short enough. – Anton Nov 20 '13 at 22:49
I really recommend to read this tutorial, because EF has some hidden stones like state update when you reattach a detached entity! Please have a look at the above provided link! – Anton Nov 20 '13 at 22:52
Yes, thanks, I have read it and now understand the way this works. Well, today I learned a lot. Also learned new method which goes like this: `db.Entry(a).State`. A lot of useful stuff. I will definitely take time reading this website as it seems only practice (I almost never read any tutorials about mvc except a very basic one, most things came through googling to help to deal with certain issues) without theory is not good enough :) – Andrius Naruševičius Nov 20 '13 at 23:06

MVC inserting multiple records instead of expected one

1 Answers1