问题描述
我有一些代码,节省了许多在代码中的许多关系。它是工作的罚款与实体框架4.1,但更新到实体框架5后,它的失败
我收到以下错误:
I'm using POCO entities with custom mappings. Standard field and many-to-one relationship mappings seem to be working fine.
UPDATE
Ok, so I've got SQL Profiler installed and the plot has thickened...
exec sp_executesql N'insert [dbo].[WebUserFavouriteEvent]([WebUserId], [EventId])
values (@0, @1)
',N'@0 int,@1 int',@0=1820,@1=14
Which means:
WebUserId = @0 = 1820
EventId = @1 = 14
The interesting thing is is that EF5 seems to have flipped the foreign keys around... the WebUserId should be 14 and the EventId should be 1820, not the other way around like it is now.
I reviewed the mapping code and I'm 99% I've set it all up correctly. See Entity Framework Fluent API - Relationships MSDN article for more information.
NOTE: I have also found that this isn't restricted to saving either, SELECTs are also broken.
Here's all the relevant code:
Service Layer
public void AddFavEvent(WebUser webUser, Event @event)
{
webUser.FavouriteEvents.Add(@event);
_webUserRepo.Update(webUser);
}
Repository
public void Update<T>(params T[] entities)
where T : DbTable
{
foreach (var entity in entities)
{
entity.UpdatedOn = DateTime.UtcNow;
}
_dbContext.SaveChanges();
}
NOTE: I'm using a 1 DataContext per request approach, so webUser
and @event
would have been loaded from the same context as the one in the _webUserRepo
.
Entities (don't worry about DbTable stuff)
public class Event : DbTable
{
//BLAH
public virtual ICollection<WebUser> FavouriteOf { get; set; }
//BLAH
}
public class WebUser : DbTable
{
//BLAH
public virtual ICollection<Event> FavouriteEvents { get; set; }
//BLAH
}
Mappings
public class EventMapping : DbTableMapping<Event>
{
public EventMapping()
{
ToTable("Event");
//BLAH
HasMany(x => x.FavouriteOf)
.WithMany(x => x.FavouriteEvents)
.Map(x =>
{
x.MapLeftKey("EventId");
x.MapRightKey("WebUserId");
x.ToTable("WebUserFavouriteEvent");
});
}
}
public class WebUserMapping : DbTableMapping<WebUser>
{
public WebUserMapping ()
{
HasMany(x => x.FavouriteEvents)
.WithMany(x => x.FavouriteOf)
.Map(m =>
{
m.MapLeftKey("WebUserId");
m.MapRightKey("EventId");
m.ToTable("WebUserFavouriteEvent");
});
}
}
Looking at this I suspect that the problem might be caused by the fact that you map the same relationship twice. And you map it in different order.
I made a simple test where I first mapped the relationship once:
class Program
{
static void Main(string[] args)
{
Database.SetInitializer(new DropCreateDatabaseAlways<Context>());
var p = new Parent();
var c = new Child();
using (var db = new Context())
{
db.Parents.Add(new Parent());
db.Parents.Add(p);
db.Children.Add(c);
db.SaveChanges();
}
using (var db = new Context())
{
var reloadedP = db.Parents.Find(p.ParentId);
var reloadedC = db.Children.Find(c.ChildId);
reloadedP.Children = new List<Child>();
reloadedP.Children.Add(reloadedC);
db.SaveChanges();
}
using (var db = new Context())
{
Console.WriteLine(db.Children.Count());
Console.WriteLine(db.Children.Where(ch => ch.ChildId == c.ChildId).Select(ch => ch.Parents.Count).First());
Console.WriteLine(db.Parents.Where(pa => pa.ParentId == p.ParentId).Select(pa => pa.Children.Count).First());
}
}
}
public class Parent
{
public int ParentId { get; set; }
public ICollection<Child> Children { get; set; }
}
public class Child
{
public int ChildId { get; set; }
public ICollection<Parent> Parents { get; set; }
}
public class Context : DbContext
{
public Context() : base("data source=Mikael-PC;Integrated Security=SSPI;Initial Catalog=EFTest")
{
}
public IDbSet<Child> Children { get; set; }
public IDbSet<Parent> Parents { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Child>()
.HasMany(x => x.Parents)
.WithMany(x => x.Children)
.Map(c =>
{
c.MapLeftKey("ChildId");
c.MapRightKey("ParentId");
c.ToTable("ChildToParentMapping");
});
}
}
And then I changed the OnModelCreating to be:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Child>()
.HasMany(x => x.Parents)
.WithMany(x => x.Children)
.Map(c =>
{
c.MapLeftKey("ChildId");
c.MapRightKey("ParentId");
c.ToTable("ChildToParentMapping");
});
modelBuilder.Entity<Parent>()
.HasMany(x => x.Children)
.WithMany(x => x.Parents)
.Map(c =>
{
c.MapLeftKey("ParentId");
c.MapRightKey("ChildId");
c.ToTable("ChildToParentMapping");
});
}
What I found and suspected is that the first run generates this sql:
exec sp_executesql N'insert [dbo].[ChildToParentMapping]([ChildId], [ParentId])
values (@0, @1)
',N'@0 int,@1 int',@0=1,@1=2
In constrast to the second which generates:
exec sp_executesql N'insert [dbo].[ChildToParentMapping]([ParentId], [ChildId])
values (@0, @1)
',N'@0 int,@1 int',@0=1,@1=2
You see the values flipped? Here it actually count the ChildId column as ParentId. Now this doesn't crash for me but I let EF create the database which means it probably just switch the column names and if I would look at the foreign keys they would be switched too. If you created the database manually that probably won't be the case.
So in short: You mappings aren't equal and I expect one of them to be used and that one is probably wrong. In earlier verions I guess EF picked them up in different order.
UPDATE:I got a bit curious about the foreign keys and checked the sql.
From the first code:
ALTER TABLE [dbo].[ChildToParentMapping] ADD CONSTRAINT [FK_dbo.ChildToParentMapping_dbo.Children_ChildId] FOREIGN KEY ([ChildId]) REFERENCES [dbo].[Children] ([ChildId]) ON DELETE CASCADE
And from the second code:
ALTER TABLE [dbo].[ChildToParentMapping] ADD CONSTRAINT [FK_dbo.ChildToParentMapping_dbo.Children_ParentId] FOREIGN KEY ([ParentId]) REFERENCES [dbo].[Children] ([ChildId]) ON DELETE CASCADE
Now that is not nice. ParentId mapped against Children is certainly not what we want.
So is the second mapping wrong? Not really because see what happend when I removed the first one:
ALTER TABLE [dbo].[ChildToParentMapping] ADD CONSTRAINT [FK_dbo.ChildToParentMapping_dbo.Parents_ParentId] FOREIGN KEY ([ParentId]) REFERENCES [dbo].[Parents] ([ParentId]) ON DELETE CASCADE
Somehow having two mappings seems to mess things up. Bug or not I don't know.
这篇关于许多一对多的关系,左右键后,实体框架5的升级翻转的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!