Entity Framework won't persist data in SQL Exp

Question

I was developing an application using Entity Framework and storing data in a .mdf database. My code can read the data, apparently it can save too, but only apparently. It get no erros, while the program is running it act like the data was saved, I can for example save an object, dispose the context, create a new one, and then when I search my object it's there! But when I query the database to see the stored data there's nothing there. If I close the app and run it again, all data is gone. Here's a example code I wrote just to test:

        using (DatabaseEntities e = new DatabaseEntities())
        {
            for (int i = 0; i < 50; i++)
            {
                User u = new User();
                u.Nome = "User" + i.ToString();
                e.AddToUser(u);
            }
            int c = e.SaveChanges(true);

            List<User> us = e.User.Where<User>(x => x.ID < 50).ToList<User>();

            foreach (User u in us)
                Console.WriteLine("ID: " + u.ID + " Hello from " + u.Nome);

            Console.ReadKey();
        }

When I run this I get the 50 outputs, if I look the content of the c variable in the debug, there's 50 changes, everythings seems to be fine, but when I start my query browser and look in the content of my MDF database, there's nothing there.

Probably it's something very simple but I can't see what it is, I need your help.

Answer 1

This situation can arise due to the way you attach an MDF database file to LocalDB, using the Visual Studio Server Explorer.

When you attach an MDF file, VS asks if you want create a copy of the database in your project. If you say "No" then a connection string is generated in the config file which includes an "attachdbfilename" entry which points to the absolute path to the MDF file on disk (e.g. "C:\..."). However, if you say "Yes" then it makes a copy of the MDF file, adds it to the project, and the "attachdbfilename" entry in the generated connection string becomes relative to |DataDirectory|.

When you hit F5 to run the application, VS copies all of the files including the database MDF file to the bin folder. All changes are made to that database. When you close and restart the application, a new clone of the original database is copied to the bin folder, overwriting anything that was there, so appearing to overwrite your previous changes. This is covered in How to: Manage Local Data Files in Your Project

Answer 2

What's the type of ID ? A GUID? How do you set the ID? With NewID() as default? If that's the case, an O/R mapper can't read the ID back and will likely flag the entity as wrongly saved. Also check with SQL Profiler if you get any queries executed on the db.

Answer 3

A few things leap to mind:

• double/treble-check your connection string; are you really talking to the file that you think you are?
• are you using transactions anywhere and not committing them?
• do you have the mdf file set to "Copy Always" (or whatever it is).... i.e. are you constantly overwriting the mdf whenever you hit build/play?

Also - I don't think it'll matter in this case, but to verify data you should use a different entities/data-context:

using (DatabaseEntities e = new DatabaseEntities()) {
    for (int i = 0; i < 50; i++) {
        User u = new User();
        u.Nome = "User" + i.ToString();
        e.AddToUser(u);
    }
    int c = e.SaveChanges(true);
}
using (DatabaseEntities e = new DatabaseEntities()) {
    List<User> us = e.User.Where<User>(x => x.ID < 50).ToList<User>();
    foreach (User u in us)
        Console.WriteLine("ID: " + u.ID + " Hello from " + u.Nome);
}
Console.ReadKey();

Like I said - I doubt it'll matter here, but worth checking...

Answer 4

I just found what was going wrong, and I guess there's nothing wrong actually. I forced an error, and then I could see the database that I was accessing was in the bin directory, the Visual Studio copied my database to the bin directory everytime I run the app, so that's why if I add some data manually I could see it, but the saves in the runtime aren't persisted. I always worked with database in servers, it's my first time with local databases, so I messed up it, but thankz a lot for the help!

---

Edit: Providing a Solution

If you wish to disable copying the database on build, then you have to acces the Copy to Output Directoy from the .mdf file from your Solution Explorer.
Simply change it to Copy if newer or Do not copy.

Be aware that Copy if newer holds some risks when accesing a .mdf Database.