实体框架6自定义多对多的子进行隐式插入和删除 [英] Entity Framework 6 custom many-to-many child with implicit insert and delete
问题描述
我有一个父对象（LoanApplication）与一个小孩（LoanApplicationQualificationTypes），这是一个自定义的多对多表。我有一个习惯的原因是它有两个审计列需要填充（ModifiedBy，ModifiedDate）。
要获取从小孩收集要在数据库中保持正确，我不得不明确处理。
以下是代码（通过删除与问题密切相关的其他属性进行简化）。
父母（多对多的一部分）：
[Serializable]
[Table（LoanApplication）]
public class LoanApplication：BaseDomainModelWithId，ILoanApplication
{
[Key]
[Column（LoanApplicationId）]
public override int？ Id {get;组; }
[ForeignKey（LoanApplicationId）]
public virtual ICollection< LoanApplicationQualificationTypes> LoanApplicationQualificationTypes {get;组; }
IReadOnlyCollection< ILoanApplicationQualificationTypes> ILoanApplication.LoanApplicationQualificationTypes
{
get
{
var loanApplicationQualificationTypes = new List< ILoanApplicationQualificationTypes>（）;
if（LoanApplicationQualificationTypes == null）return loanApplicationQualificationTypes;
loanApplicationQualificationTypes.AddRange（LoanApplicationQualificationTypes）;
return loanApplicationQualificationTypes.AsReadOnly（）;
}
set
{
foreach（var item in value）
{
LoanApplicationQualificationTypes.Add（（LoanApplicationQualificationTypes）项目）;
$
$ b public LoanApplication（）：base（）
{
LoanApplicationQualificationTypes = new List< LoanApplicationQualificationTypes>（）;
}
public interface ILoanApplication：IDomainModel，ILoanApplicationBase，IKeyIntId
{
IReadOnlyCollection< ILoanApplicationQualificationTypes> LoanApplicationQualificationTypes {get;组; }
}
多对多的对象部分：
[Serializable]
[Table（QualificationType）]
public class QualificationType：IQualificationType
{
[Key]
[Column（QualificationTypeId）]
public override int？ Id {get;组;
[必需]
public string TypeName {get;组; }
[必需]
public bool IsActive {get;组; }
public virtual string ModifiedBy {get;组; }
public virtual DateTimeOffset？ ModifiedDate {get;组; }
public QualificationType（）：{}
}
自定义多对多：
[Serializable]
[表（LoanApplicationQualificationTypes）]
public class LoanApplicationQualificationTypes：ILoanApplicationQualificationTypes
{
[Key]
[Column（Order = 1）]
public int？ LoanApplicationId {get;组; }
[ForeignKey（LoanApplicationId）]
public virtual LoanApplication LoanApplication {get;组;
ILoanApplication ILoanApplicationQualificationTypes.LoanApplication
{
get
{
return this.LoanApplication;
}
set
{
this.LoanApplication =（LoanApplication）value;
}
}
[必需]
[Key]
[列（Order = 2）]
public int QualificationTypeId {get;组; }
[ForeignKey（QualificationTypeId）]
public virtual QualificationType QualificationType {get;组; }
IQualificationType ILoanApplicationQualificationTypes.QualificationType
{
get
{
return this.QualificationType;
}
set
{
this.QualificationType =（QualificationType）value;
}
}
public virtual string ModifiedBy {get;组; }
public virtual DateTimeOffset？ ModifiedDate {get;组; }
public LoanApplicationQualificationTypes（）{}
}
更新方法在LoanApplication Repository中：
public bool Update（ILoanApplication entity）
{
using（var db = new MainContext（））
{
entity.ModifiedDate = DateTime.UtcNow;
entity.ModifiedBy = UserOrProcessName;
//添加/删除LoanApplicationQualificationTypes并填充审核列
if（entity.LoanApplicationQualificationTypes？.Count> 0）
{
var existingItems = db.LoanApplicationQualificationTypes。其中（q => q.LoanApplicationId == entity.Id.Value）.ToList（）;
var newItems = entity.LoanApplicationQualificationTypes.Where（q => existingItems.All（e => e.QualificationTypeId！= q.QualificationTypeId））;
var deletedItems = existingItems.Where（q => entity.LoanApplicationQualificationTypes.All（e => e.QualificationTypeId！= q.QualificationTypeId））;
foreach（newItems中的var newItem）
{
newItem.ModifiedBy = UserOrProcessName;
newItem.ModifiedDate = DateTime.UtcNow;
db.LoanApplicationQualificationTypes.Add（（LoanApplicationQualificationTypes）newItem）;
}
foreach（delete deletedItem in deletedItems）
{
db.LoanApplicationQualificationTypes.Remove（（LoanApplicationQualificationTypes）deletedItem）;
}
//需要清除以避免重复对象
（（LoanApplication）entity）.LoanApplicationQualificationTypes.Clear（）;
}
db.Entry（entity）.State = EntityState.Modified;
db.SaveChanges（）;
}
返回true;
}
有没有办法实现更新而不显式处理添加/更新？ / p>
我理解的方式，问题是如何将（潜在）修改应用于链接表，而不显式检测添加/删除链接。另外我假定链接的另一部分必须存在。
可以通过以下操作顺序：
首先将实际的实体从数据库加载到上下文中，包括链接：
var dbEntity = db.LoanApplication
。包含（e => e.LoanApplicationQualificationTypes）
.FirstOrDefault（e => e.Id == entity.Id）;
这将允许更改跟踪器以后为您确定正确的添加/更新/删除链接操作。 / p>
然后应用原始主数据更改：
db.Entry dbEntity）.CurrentValues.SetValues（实体）;
dbEntity.ModifiedDate = DateTime.UtcNow;
dbEntity.ModifiedBy = UserOrProcessName;
最后，将链接替换为传入实体的链接。为了避免指向不同对象的导航属性引用（特别是防止EF尝试为关系的其他对象创建新记录），请勿直接使用传入对象，而是仅创建FK属性设置的存根对象：
dbEntity.LoanApplicationQualificationTypes = entity.LoanApplicationQualificationTypes
.Select（e => new LoanApplicationQualificationTypes
{
LoanApplicationId = e.LoanApplicationId，
QualificationTypeId = e.QualificationTypeId，
ModifiedDate = DateTime.UtcNow，
ModifiedBy = UserOrProcessName，
}）
.ToList（ ）;
就是这样。此时，当您调用 db.SaveChanges（）时，更改跟踪器具有生成正确命令的所有必需信息。
有一件事要提及如果您现在查看 db.ChangeTracker.Entries ，您可能会注意到所有旧链接都标记为已删除，所有输入为添加，并且没有修改条目。别担心EF足够聪明，将转换已删除 + 将相同的PK添加到单个更新命令。
整个方法：
public bool Update使用（var db = new MainContext（））
{
var dbEntity = db.LoanApplication
.Include（e => e。 LoanApplicationQualificationTypes）
.FirstOrDefault（e => e.Id == entity.Id）;
if（dbEntity == null）return false;
db.Entry（dbEntity）.CurrentValues.SetValues（entity）;
dbEntity.ModifiedDate = DateTime.UtcNow;
dbEntity.ModifiedBy = UserOrProcessName;
dbEntity.LoanApplicationQualificationTypes = entity.LoanApplicationQualificationTypes
.Select（e => new LoanApplicationQualificationTypes
{
LoanApplicationId = e.LoanApplicationId，
QualificationTypeId = e .QualificationTypeId，
ModifiedDate = DateTime.UtcNow，
ModifiedBy = UserOrProcessName，
}）
.ToList（）;
db.SaveChanges（）;
返回true;
}
}
I have parent object (LoanApplication) with a child (LoanApplicationQualificationTypes) that is a custom many-to-many table. The reason I have a custom is that it has two audit columns that need to be populated (ModifiedBy, ModifiedDate).
To get the children that were added or removed from the child collection to be persisted in the database correctly, I had to explicitly handle.
Below is the code (simplified by removing other properties that were germane to the question).
Parent (part of many-to-many):
[Serializable]
[Table("LoanApplication")]
public class LoanApplication : BaseDomainModelWithId, ILoanApplication
{
[Key]
[Column("LoanApplicationId")]
public override int? Id { get; set; }
[ForeignKey("LoanApplicationId")]
public virtual ICollection<LoanApplicationQualificationTypes> LoanApplicationQualificationTypes { get; set; }
IReadOnlyCollection<ILoanApplicationQualificationTypes> ILoanApplication.LoanApplicationQualificationTypes
{
get
{
var loanApplicationQualificationTypes = new List<ILoanApplicationQualificationTypes>();
if (LoanApplicationQualificationTypes == null) return loanApplicationQualificationTypes;
loanApplicationQualificationTypes.AddRange(LoanApplicationQualificationTypes);
return loanApplicationQualificationTypes.AsReadOnly();
}
set
{
foreach (var item in value)
{
LoanApplicationQualificationTypes.Add((LoanApplicationQualificationTypes)item);
}
}
}
public LoanApplication() : base()
{
LoanApplicationQualificationTypes = new List<LoanApplicationQualificationTypes>();
}
}
public interface ILoanApplication : IDomainModel, ILoanApplicationBase, IKeyIntId
{
IReadOnlyCollection<ILoanApplicationQualificationTypes> LoanApplicationQualificationTypes { get; set; }
}
Object part of many-to-many:
[Serializable]
[Table("QualificationType")]
public class QualificationType : IQualificationType
{
[Key]
[Column("QualificationTypeId")]
public override int? Id { get; set; }
[Required]
public string TypeName { get; set; }
[Required]
public bool IsActive { get; set; }
public virtual string ModifiedBy { get; set; }
public virtual DateTimeOffset? ModifiedDate { get; set; }
public QualificationType() : { }
}
Custom Many-to-Many:
[Serializable]
[Table("LoanApplicationQualificationTypes")]
public class LoanApplicationQualificationTypes : ILoanApplicationQualificationTypes
{
[Key]
[Column(Order = 1)]
public int? LoanApplicationId { get; set; }
[ForeignKey("LoanApplicationId")]
public virtual LoanApplication LoanApplication { get; set; }
ILoanApplication ILoanApplicationQualificationTypes.LoanApplication
{
get
{
return this.LoanApplication;
}
set
{
this.LoanApplication = (LoanApplication)value;
}
}
[Required]
[Key]
[Column(Order = 2)]
public int QualificationTypeId { get; set; }
[ForeignKey("QualificationTypeId")]
public virtual QualificationType QualificationType { get; set; }
IQualificationType ILoanApplicationQualificationTypes.QualificationType
{
get
{
return this.QualificationType;
}
set
{
this.QualificationType = (QualificationType)value;
}
}
public virtual string ModifiedBy { get; set; }
public virtual DateTimeOffset? ModifiedDate { get; set; }
public LoanApplicationQualificationTypes() { }
}
Update method in LoanApplication Repository:
public bool Update(ILoanApplication entity)
{
using (var db = new MainContext())
{
entity.ModifiedDate = DateTime.UtcNow;
entity.ModifiedBy = UserOrProcessName;
// Add / Remove LoanApplicationQualificationTypes and populate audit columns
if (entity.LoanApplicationQualificationTypes?.Count > 0)
{
var existingItems = db.LoanApplicationQualificationTypes.Where(q => q.LoanApplicationId == entity.Id.Value).ToList();
var newItems = entity.LoanApplicationQualificationTypes.Where(q => existingItems.All(e => e.QualificationTypeId != q.QualificationTypeId));
var deletedItems = existingItems.Where(q => entity.LoanApplicationQualificationTypes.All(e => e.QualificationTypeId != q.QualificationTypeId));
foreach (var newItem in newItems)
{
newItem.ModifiedBy = UserOrProcessName;
newItem.ModifiedDate = DateTime.UtcNow;
db.LoanApplicationQualificationTypes.Add((LoanApplicationQualificationTypes)newItem);
}
foreach (var deletedItem in deletedItems)
{
db.LoanApplicationQualificationTypes.Remove((LoanApplicationQualificationTypes)deletedItem);
}
// Need to clear to avoid duplicate objects
((LoanApplication)entity).LoanApplicationQualificationTypes.Clear();
}
db.Entry(entity).State = EntityState.Modified;
db.SaveChanges();
}
return true;
}
Is there a way implement the Update without the explicitly handling adds/updates?
The way I understand it, the question is how to apply the (potential) modifications to the link table without explicitly detecting added/removed links. Also I assume the other part of the link must exist.
It's possible with the following sequence of operations:
First load the actual entity from the database into context, including the links:
var dbEntity = db.LoanApplication
.Include(e => e.LoanApplicationQualificationTypes)
.FirstOrDefault(e => e.Id == entity.Id);
This will allow change tracker to determine the correct add/update/delete link operations for you later.
Then apply the primitive master data changes:
db.Entry(dbEntity).CurrentValues.SetValues(entity);
dbEntity.ModifiedDate = DateTime.UtcNow;
dbEntity.ModifiedBy = UserOrProcessName;
Finally, replace the links with the ones from the incoming entity. To avoid navigation property references pointing to different objects (and in particular to prevent EF trying to create the new records for the other side objects of the relation), do not use directly the incoming objects, but create stub objects with only FK properties set:
dbEntity.LoanApplicationQualificationTypes = entity.LoanApplicationQualificationTypes
.Select(e => new LoanApplicationQualificationTypes
{
LoanApplicationId = e.LoanApplicationId,
QualificationTypeId = e.QualificationTypeId,
ModifiedDate = DateTime.UtcNow,
ModifiedBy = UserOrProcessName,
})
.ToList();
And that's it. At this point the change tracker has all the necessary information to produce the correct commands when you call db.SaveChanges().
One thing to mention. If you look at db.ChangeTracker.Entries at this point, you'll probably notice that all the old links are marked as Deleted, all the incoming as Added and there are no Modified entries. Don't worry. EF is smart enough and will convert Deleted + Added pairs with the same PK to single update commands.
The whole method:
public bool Update(ILoanApplication entity)
{
using (var db = new MainContext())
{
var dbEntity = db.LoanApplication
.Include(e => e.LoanApplicationQualificationTypes)
.FirstOrDefault(e => e.Id == entity.Id);
if (dbEntity == null) return false;
db.Entry(dbEntity).CurrentValues.SetValues(entity);
dbEntity.ModifiedDate = DateTime.UtcNow;
dbEntity.ModifiedBy = UserOrProcessName;
dbEntity.LoanApplicationQualificationTypes = entity.LoanApplicationQualificationTypes
.Select(e => new LoanApplicationQualificationTypes
{
LoanApplicationId = e.LoanApplicationId,
QualificationTypeId = e.QualificationTypeId,
ModifiedDate = DateTime.UtcNow,
ModifiedBy = UserOrProcessName,
})
.ToList();
db.SaveChanges();
return true;
}
}
这篇关于实体框架6自定义多对多的子进行隐式插入和删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
