实体框架插入与另一个外键冲突 [英] Entity Framework insert conflicts with another foreign key
问题描述
我很难插入带有其相关实体的数据.
I am having difficulty inserting data with its related entities.
public class Status : Entity, IAggregateRoot
{
//other properties
public readonly List<Video> _videos;
public readonly List<Photo> _photos;
}
-
public class Log : Entity, IAggregateRoot
{
//other properties
public readonly List<Video> _videos;
public readonly List<Photo> _photos;
public readonly List<Sound> _audios;
}
-
public class Photo : Entity, IAggregateRoot
{
//other properties
public string Type { get; set; }
public int TypeId { get; set; }
}
基本上，一个状态对象可以包含零个或多个视频或图片.日志对象还可以包含零个或多个视频，声音或图片.以下是用于实现此目的的流利的api代码:
Basically, A status object can have zero or more videos or pictures. A log object can also have zero or more videos, sound or pictures. Below is the fluent api code used to achieve this:
class LogEntityTypeConfiguration : IEntityTypeConfiguration<Log>
{
public void Configure(EntityTypeBuilder<Log> logConfiguration)
{
logConfiguration.HasMany(b => b.Videos)
.WithOne()
.HasForeignKey("TypeId")
.OnDelete(DeleteBehavior.Cascade);
logConfiguration.HasMany(b => b.Photos)
.WithOne()
.HasForeignKey("TypeId")
.OnDelete(DeleteBehavior.Cascade);
logConfiguration.HasMany(b => b.Audios)
.WithOne()
.HasForeignKey("TypeId")
.OnDelete(DeleteBehavior.Cascade);
}
}
-
public void Configure(EntityTypeBuilder<Status> statusConfiguration)
{
statusConfiguration.HasMany(b => b.Videos)
.WithOne()
.HasForeignKey("TypeId")
.OnDelete(DeleteBehavior.Cascade);
statusConfiguration.HasMany(b => b.Photos)
.WithOne()
.HasForeignKey("TypeId")
.OnDelete(DeleteBehavior.Cascade);
}
这构建得很好，下图显示了生成的外键.
This builds just fine, the image below shows the generated foreign keys.
我有一个日志存储库类，当尝试插入日志对象时，出现以下错误:
I have got a log repository class, when trying to insert a log object, i get the following error:
System.Data.SqlClient.SqlException(0x80131904):插入 语句与FOREIGN KEY约束冲突
"FK_Photos_Statuses_TypeId".数据库"xxxx"中发生了冲突， 表"dbo.Statuses"的"Id"列
System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Photos_Statuses_TypeId". The conflict occurred in database "xxxx", table "dbo.Statuses", column 'Id'
public async Task<Log> AddAsync(Log log, LogFiles files)
{
var strategy = _context.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () => {
using (var txn = _context.Database.BeginTransaction())
{
try
{
if (log.IsTransient())
{
_context.Logs.Add(log);
_context.SaveChanges();
if (files.Video != null)
{
Video vid = new Video();
vid = files.Video;
log._videos.Add(vid);
}
if(files.Picture != null)
{
Photo ph = new Photo();
ph = files.Picture;
log._photos.Add(ph);
}
if(files.Audio != null)
{
Sound aud = new Sound();
aud = files.Audio;
log._audios.Add(aud);
}
_context.SaveChanges();
txn.Commit();
}
}
catch (Exception ex)
{
txn.Rollback();
}
}
});
return log;
}
当我尝试插入日志对象时，我也不明白为什么错误列表中会显示状态对象的外键?
I also don't understand why the foreign key of the status object is showing up among the error list, when i'm trying to insert a log object??
p.s，如果您有更好的方法可以为这种关系建模，请分享.
p.s if you have a better way i could model the relationship please share.
推荐答案
这很可能是由于实体分离所致.日志和日志的来源是什么LogFiles属性?我的猜测是这些来自网络客户端吗?
This is most likely due to the disassociated entities. What is the source of the Log & LogFiles properties? My guess is that these are coming from a web client?
要概述传递实体的问题: 让我们看一下具有状态参考的Photo实体.
To outline the problem with passing entities: Lets look at a Photo entity which has a Status reference.
public class Status
{
public int StatusId { get; set; }
public string Name { get; set; }
}
public class Photo
{
public int PhotoId { get; set; }
public virtual Status Status { get; set; }
}
现在，如果我从DbContext加载一组照片，我可能会带回2张状态为新建"的照片.
Now if I go and load a set of photos from a DbContext I might bring back 2 photos with a status of "New".
就实例"而言，我将:
Photo (ID: 1) \
==> Status (ID: 1 [New])
Photo (ID: 2) /
问题是，当我将那些断开连接的实体发送回Controller之类的东西时，它们会反序列化，并且看起来像下面的样子:
The problem is when I send those disconnected entities back to something like Controller, they are de-serialized and would look like the following:
Photo (ID: 1) ==> Status (ID: 1 [New])
Photo (ID: 2) ==> Status (ID: 1 [New])
在您的情况下，您要传回新照片(精细)，但应将其与现有状态相关联.您的Photo实体可能会设置为生成PK，但不会显示类似Status的查找.无论哪种方式，如果EF不了解"状态，它将与照片一起被视为新实体".当EF尝试插入状态ID 1时，这会导致FK约束.
In your case you are passing back a new Photo (fine) but it should be associated to an existing Status. Your Photo entity will probably be set up to generate a PK, but a lookup like Status would not be. Either way, if EF doesn't "know" about the Status, it will be treated as a New entity along with the photo. This leads to a FK constraint as EF tries to insert Status ID 1.
将实体传递回控制器会导致各种问题.例如，如果您正在执行照片编辑，将照片ID 1传递回去，您将发现您需要以某种方式告知EF关于照片1(例如，使用Attach并将状态设置为已修改")，然后在照片的任何关联实体周围也会遇到FK错误.附加相关的实体(例如Status)将首先解决您的问题，但随后会导致进一步的复杂性，如上述，对同一Status的多个引用实际上是Status对象的单独实例.在第一个实例上调用attach是可以的，但是一旦在该上下文中以相同状态保存某些内容，您将立即获得异常.如果尝试附加第二个引用，则不同的引用和EF会抱怨具有相同ID的实例与上下文相关联.
Passing Entities back to Controllers leads to all kinds of problems. If you were performing an Edit of a Photo for instance, passing back Photo ID 1, you would find that you would need to somehow tell EF about Photo #1 (using Attach and setting the State to Modified for example), and then also face FK errors around any associated entities to the photo. Attaching related entities (like Status) will solve your problem initially, but then leads to further complications like above where multiple references to the same Status are actually separate instances of a Status object. Calling attach on the first instance will work, but then you will get an exception as soon as you save something in that context with the same status. Different references and EF will complain that an instance with the same ID is associated with the context if you try and attach the 2nd.
从客户端本身附加实体是一种危险的做法，因为您隐式地信任从客户端返回的数据.精明的恶意用户可以轻松地以超出网页允许范围的任何方式修改数据，从而破坏数据存储.
Attaching entities from a client itself is a dangerous practice because you are implicitly trusting the data coming back from the client. A savvy, malicious user could easily modify the data in any number of ways beyond what your web page allows, corrupting your data store.
我给围绕EF的开发人员的主要建议是不要绕过实体".没有什么好事了. :)如果您传递日志视图模型，照片视图模型等，那么它将减少在服务器和客户端之间来回传送的数据量(使系统更快且资源占用更少)，并迫使您考虑即将到来的数据背部.
The main advice I give to devs around EF is "don't pass entities around". Nothing good becomes of it. :) If you pass Log view models, photo view models, etc. then it reduces the amount of data being shipped back and forth between server and client (making the system faster and less resource intensive) and forces you to think about the data coming back.
例如，如果我取回LogInsertViewModel和一组关联的PhotoInsertViewModels
For instance, if I take back a LogInsertViewModel and a set of associated PhotoInsertViewModels
public async Task<Log> AddAsync(LogInsertViewModel logVm, ICollection<PhotoInsertViewModel> photoVms)
{
// TODO: Validate that the log & files are correct/complete and applicable to the current session user...
// If I need to lookup values from collections... (1 hit to DB to get all applicable)
statusIds = photoVms.Select(x => x.StatusId).ToList();
var statuses = context.Statuses.Where(x => statusIds.Contains(x.StatusId)).ToList();
// Alternatively if I know all new photos were going to be associated a "New" status...
var newStatus = context.Statuses.Single(x => x.Status = Statuses.New);
// Create a Log.
var log = new Log
{
//.. copy values.
Photos = photoVms.Select(x => new Photo
{
// copy values.
Status = statuses.Single(s => s.StatusId = x.StatusId); // or newStatus
}).ToList();
};
context.Logs.Add(log);
context.SaveChanges();
}
这篇关于实体框架插入与另一个外键冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
