I'm having lots of trouble with codefirst Entity Framework.
I have a database with two tables - Users and Countries. Users has a field that has a foreign key of CountryId. When a user is first added to the database the FK is updated fine. However, if I try to update the FK on a user that I attach to a new DbContext, no changes happens.
I can update the FK in the context that I originally created the user in. Also, I can update the FK in a context where I retrieve the user from the database. However, in a context where I just Attach the user, the FK field does not update, even though normal nvarchar or int fields do update.
Here is my DbContext:
class DataContext : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Country> Countries { get; set; }
}
And here is my model:
public class User
{
public int UserId { get; private set; }
public string Name { get; set; }
public virtual Country Country { get; set; }
}
public class Country
{
public int CountryId { get; private set; }
public string Name { get; set; }
}
This method demonstrates the problem. I add 3 users to my database, and then try to modify them in 3 different ways - in the original context the user was added in, in a new context with Attach, and and in new context where the user is retrieved back from the database to modify:
static void Main(string[] args)
{
Country[] countries;
using (var dc = new DataContext())
{
countries = dc.Countries.ToArray();
}
var u = new User();
// FIRST ROW
using (var dc = new DataContext())
{
u.Name = "First";
u.Country = countries[0];
dc.Users.Attach(u);
dc.Entry(u).State = System.Data.EntityState.Added;
dc.SaveChanges();
u.Name = "FirstChanged";
u.Country = countries[1];
// defaults to 'added' once u is tracked.
dc.Entry(countries[1]).State = EntityState.Unchanged;
dc.SaveChanges();
}
// SECOND ROW
u = new User();
using (var dc = new DataContext())
{
u.Name = "Second";
u.Country = countries[0];
dc.Users.Attach(u);
dc.Entry(u).State = System.Data.EntityState.Added;
dc.SaveChanges();
}
using (var dc = new DataContext())
{
u.Name = "SecondChanged";
u.Country = countries[1];
dc.Users.Attach(u);
dc.Entry(u).State = System.Data.EntityState.Modified;
dc.SaveChanges();
}
// THIRD ROW
u = new User();
using (var dc = new DataContext())
{
u.Name = "Third";
u.Country = countries[0];
dc.Users.Attach(u);
dc.Entry(u).State = System.Data.EntityState.Added;
dc.SaveChanges();
}
using (var dc = new DataContext())
{
u = dc.Users.Find(u.UserId);
u.Name = "ThirdChanged";
u.Country = countries[1];
dc.Entry(countries[1]).State = EntityState.Modified;
dc.SaveChanges();
}
}
This application produces the following results in the database:
UserId Name Country_CountryId
------------------------------------------------------------------------
1 FirstChanged 2
2 SecondChanged 1
3 ThirdChanged 2
As you can see, In all cases Name is successfully updated. In the first case and third case, the CountryId is updated. However, in the second case the CountryId is not updated. Unfortunately, this is case is most relevant for me, as I want to be able to persist the Model for editing by a user outside of a DataContext.
I don't understand why this is happening - if the Name field did not update I could understand, but as its just CountryId I can't work out why. In both cases the Country object is tracked and marked as Unchanged. I can't understand why only the second case doesn't work.
Please help me work out what is up here. The only solution I can think of is to use the third case when I need to modify, so that when I save I copy across data from my persisted object to a new object retrieved from the database which is saved... but obviously this is a silly solution.
Below, I include a console application that demonstrates this problem. To run it you will need to retrieve EntityFramework using NuGet (or similar) and a connection string like this:
<add name="DataContext" connectionString="DataSource=.\DataContext.sdf" providerName="System.Data.SqlServerCe.4.0" />
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
Country[] countries;
using (var dc = new DataContext())
{
countries = dc.Countries.ToArray();
}
var u = new User();
// FIRST ROW
using (var dc = new DataContext())
{
u.Name = "First";
u.Country = countries[0];
dc.Users.Attach(u);
dc.Entry(u).State = System.Data.EntityState.Added;
dc.SaveChanges();
u.Name = "FirstChanged";
u.Country = countries[1];
// defaults to 'added' once u is tracked.
dc.Entry(countries[1]).State = EntityState.Unchanged;
dc.SaveChanges();
}
// SECOND ROW
u = new User();
using (var dc = new DataContext())
{
u.Name = "Second";
u.Country = countries[0];
dc.Users.Attach(u);
dc.Entry(u).State = System.Data.EntityState.Added;
dc.SaveChanges();
}
using (var dc = new DataContext())
{
u.Name = "SecondChanged";
u.Country = countries[1];
dc.Users.Attach(u);
dc.Entry(u).State = System.Data.EntityState.Modified;
dc.SaveChanges();
}
// THIRD ROW
u = new User();
using (var dc = new DataContext())
{
u.Name = "Third";
u.Country = countries[0];
dc.Users.Attach(u);
dc.Entry(u).State = System.Data.EntityState.Added;
dc.SaveChanges();
}
using (var dc = new DataContext())
{
u = dc.Users.Find(u.UserId);
u.Name = "ThirdChanged";
u.Country = countries[1];
dc.Entry(countries[1]).State = EntityState.Modified;
dc.SaveChanges();
}
}
}
class DataContext : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Country> Countries { get; set; }
public DataContext()
{
Database.SetInitializer<DataContext>(new ModelInitializer());
Database.Initialize(false);
}
}
class ModelInitializer : DropCreateDatabaseAlways<DataContext>
{
protected override void Seed(DataContext context)
{
var jsds = new List<Country>
{
new Country("United Kingdom"),
new Country("United States Of America")
};
jsds.ForEach(jsd => context.Countries.Add(jsd));
}
}
public class User
{
public int UserId { get; private set; }
public string Name { get; set; }
public virtual Country Country { get; set; }
}
public class Country
{
public int CountryId { get; private set; }
public string Name { get; set; }
public Country() { }
public Country(string name)
{
Name = name;
}
}
}
Thanks for looking at this question - any help would be appreciated. I'm on the second day of trying to fix it and I am slowly going crazy...
