我遇到IDENTITY_INSERT被设置为OFF，或者只有一个对象将被保存到数据库中

Question

我有一种方法，在进行一些评估后，将数据保存到数据库中。在foreach迭代中，我检查在为对象赋值然后进一步插入数据库之前是否满足的条件。当该方法运行时，它只会将一个eligibleSupplier实例保存到数据库中，或者抛出IDENTITY_INSERT异常

public void EvaluationTenderBids(int tenderId)
{
var tender = _databaseContext.Tenders.Find(tenderId);
var submittedTenders = _databaseContext.TenderBidSubmissions.Where(t => t.TenderId == tenderId).ToList();
EligibleSupplier eligibleSupplier = new EligibleSupplier();
tender.EligibleSuppliers = new List<EligibleSupplier>();

var eligibleSuppliers = new List<EligibleSupplier>();
decimal totProductQtAmt = 0;
decimal totProductRecAmt = 0;
decimal priceDifference = 0;



foreach (var tenderBid in submittedTenders)
{
var tenderBidProducts = _databaseContext.TenderBidSubmissionProducts.Where(t => t.TenderBidSubmissionId == tenderBid.TenderBidSubmissionId).ToList();
foreach(var product in tenderBidProducts)
{
var prod = _databaseContext.Products.Find(product.ProductId);
product.RecommendedPrice = prod.ProductPrice;
totProductQtAmt = product.QuotedPrice * product.Quantity;
totProductRecAmt = product.RecommendedPrice * product.Quantity;
priceDifference = totProductQtAmt - totProductRecAmt;
}
var company = _databaseContext.TenderBidSubmissions.Where(c => c.RegistrationNumber == tenderBid.RegistrationNumber).FirstOrDefault();
decimal percentage = 0;
if (priceDifference > 0)
{
percentage = ((priceDifference / tenderBid.TotalQuotation) * 100);
}

if (percentage > 27 && percentage <= 30)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 1;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;
tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else if (percentage > 21 && percentage <= 24)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 2;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;
tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else if (percentage > 18 && percentage < 21)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 3;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;
tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else if (percentage > 15 && percentage <= 18)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 4;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;
tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else if (percentage > 10 && percentage <= 15)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 5;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;

tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else
continue;

}
}

Answer 1

好的，您的代码有很多问题需要处理：

#1.对于实体，永远不要这样做：

var tender = _databaseContext.Tenders.Find(tenderId);
tender.EligibleSuppliers = new List<EligibleSupplier>();

EF加载实体时，希望它管理子集合。这意味着避免试图"；重置"；具有新实例的集合。加载现有投标书时，您应该急于加载供应商集合，然后在添加新行之前确定是否要删除/替换或编辑现有行。

只有当您知道只需要实体中的数据而不需要任何相关实体时，Find才真正有用，除非您想按需手动加载或依赖于延迟加载。相反，您应该使用：

var tender = _databaseContext.Tenders
.Include(x => x.EligibleSuppliers)
.Single(x => x.TenderId == tenderId);

这将通过ID加载单个投标，并热切加载当前记录的任何供应商。从那里，您需要决定是要更新任何现有EligibleSuppliers的值，还是删除它们(即使用tender.EligibleSuppliers.Clear()(并重新添加新实体。

#2.EF尊重参考文献。不要对多个实体使用相同的引用。此处的代码：

// Outside of a loop...
EligibleSupplier eligibleSupplier = new EligibleSupplier();
// Inside a loop...
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;
tender.EligibleSuppliers.Add(eligibleSupplier);

这99.9%肯定是你眼前问题的原因。当您添加对供应商的引用，然后重用该引用时，您正在更新已添加到供应商的实例。

在循环中，如果你想创建/添加一个新的供应商，你需要声明一个新实例，而不是重复使用同一个实例。

同样的问题也会影响您的priceDifference计算。它只会尊重产品循环中最后一个产品的值，因为循环中的行：

priceDifference = totProductQtAmt - totProductRecAmt;

这应该类似于：

priceDifference += totProductQtAmt - totProductRecAmt;

如果你想知道所有订购产品的总价是否不同。

#3.过度重复的条件代码。你的整个if / else if / else if都在做完全相同的事情，只是改变了分数。

所有这些：

if (percentage > 27 && percentage <= 30)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 1;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;
tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else if (percentage > 21 && percentage <= 24)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 2;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;
tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else if (percentage > 18 && percentage < 21)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 3;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;
tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else if (percentage > 15 && percentage <= 18)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 4;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;
tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else if (percentage > 10 && percentage <= 15)
{
eligibleSupplier.RegistrationNumber = company.RegistrationNumber;
eligibleSupplier.CompanyName = company.CompanyName;
eligibleSupplier.Tender = tender;
eligibleSupplier.TenderId = tender.TenderId;
eligibleSupplier.Score = 5;
eligibleSupplier.InflationRate = percentage;
eligibleSupplier.DateEvaluated = DateTime.Now;

tender.EligibleSuppliers.Add(eligibleSupplier);
_databaseContext.SaveChanges();
}
else
continue;

可以用helper方法代替：(请参阅：C#中是否有"between"函数？(

public static bool Between(this int num, int lower, int upper, bool inclusive = false)
{
return inclusive
? lower <= num && num <= upper
: lower < num && num < upper;
}

然后：(假设你的逻辑有一个拼写错误，第一盘是27-30，而第二盘是21-24，我怀疑你是否有意忽略25-26…

int score = percentage.Between(24, 30) ? 1
: percentage.Between(21, 24) ? 2
: percentage.Between(18, 21) ? 3
: percentage.Between(15, 18) ? 4
: percentage.Between(10, 15) ? 5
: 0;
if (score == 0)
continue;
var eligibleSupplier = new EligibleSupplier
{
RegistrationNumber = company.RegistrationNumber;
CompanyName = company.CompanyName;
Score = score;
InflationRate = percentage;
DateEvaluated = DateTime.Now;
};
tender.EligibleSuppliers.Add(eligibleSupplier);

当将供应商添加到投标书的集合中时，您不需要设置EligibleSupplier。Tender或.TenderId，EF将自动处理。这样做通常是无害的，只是没有必要。在播放导航属性时，应始终避免设置FK字段(即TenderId(。这可能会导致状态不一致，具体取决于当时是否加载了导航属性。