使用实体框架在ASP.NET Core中添加和更新 [英] Add and Update in ASP.NET Core with Entity Framework
问题描述
我正在将JSON数据添加和更新到SQL表中.
I am doing add and update JSON data into the SQL table.
下面的代码用于添加和更新一组新记录
Below Code working for adding and updating a new set of records
List<CatalogProduct> ListKp = new List<CatalogProduct>();
using (var transaction = _context.Database.BeginTransaction())
{
try
{
int numP = 0;
var catalogProducts = _context.CatalogProducts.ToList();
foreach (var kp in ListKp)
{
if (!catalogProducts.Any(x => x.Name == kp.Name))
{
_context.CatalogProducts.Add(kp);
}
else
{
//Use AutoMapper automatically do the mapping
var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>().ForMember(c => c.Id, opt => opt.Ignore()));
var oldone = catalogProducts.FirstOrDefault(c => c.Name == kp.Name);
var mapper = config.CreateMapper();
oldone = mapper.Map<CatalogProduct, CatalogProduct>(kp, oldone);
_context.CatalogProducts.Update(oldone);
}
}
numP = _context.SaveChanges();
transaction.Commit();
return Json("No conflicts. " + numP + " product details saved.");
}
catch (Exception ex)
{
transaction.Rollback();
return Json("Error occurred." + ex.Message);
throw new Exception();
}
}
示例JSON数据
{
"title": "Brown eggs",
"type": "dairy",
"description": "Raw organic brown eggs in a basket",
"filename": "0.jpg",
"height": 600,
"width": 400,
"price": 28.1,
"rating": 4
},
{
"title": "Sweet fresh stawberry",
"type": "fruit",
"description": "Sweet fresh stawberry on the wooden table",
"filename": "1.jpg",
"height": 450,
"width": 299,
"price": 29.45,
"rating": 4
},
首先，我将上述JSON中的[type]键对象值添加到产品表[NAME]字段中.它将添加一组新记录.
First I will add [type] key object values from the above JSON into the products table [NAME] field. It will add a new set of records.
当我尝试使用[title]键对象值更新产品表[NAME]字段时，它将再次添加一组新记录.
When I try to update the products table [NAME] field with [title] key object values, again it will add a new set of records.
无需更新即可更新产品表[NAME]字段.
Need to update products table [NAME] field without adding again.
我不知道如何使用模型列表值检查表中已经存在的记录.已经花了很多时间在此上.我是EF Core的新手，请任何人都可以帮助我
I don't know how to check already existing records in a table with model list values. Already spent much time on this. I am new to EF Core, please anybody can help me
完整代码
[HttpPost]
public IActionResult InsertProductDetails()
{
using WebClient wc = new WebClient();
string contentString = wc.DownloadString(baseurl);
List<Dictionary<string, string>> ListJsonProductContent = new List<Dictionary<string, string>>();
var token = JToken.Parse(contentString);
if (token.Type == JTokenType.Array) // "["
{
ListJsonProductContent = JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(contentString);
}
else if (token.Type == JTokenType.Object) // "{"
{
var ObjectResponse = JsonConvert.DeserializeObject<Dictionary<string, object>>(contentString);
foreach (var x in ObjectResponse)
{
string key = x.Key.ToString();
string val = x.Value.ToString();
foreach (var dicItemML in JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(val))
{
ListJsonProductContent.Add(dicItemML);
}
}
}
List <K360MappingMaster> ListMappedDataDb = new List<K360MappingMaster>();
var VLinqQuery = from KMM in _context.K360MappingMasters
where KMM.ThirdPartyBaseUrlName != null && KMM.ThirdPartyBaseUrlName == baseurl
select KMM;
ListMappedDataDb = VLinqQuery.ToList();
foreach (var dicItemML in ListJsonProductContent)
{
Dictionary<string, string> updItem = new Dictionary<string, string>();
foreach (var itemMl in dicItemML)
{
if (ListMappedDataDb.Select(s => s.ApiCatalog).ToList().Contains(itemMl.Key))
{
if (updItem.ContainsKey(ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault()))
{
if (ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault() == "Specification")
{
updItem[ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault()] += "<p>" + itemMl.Key + " :" + itemMl.Value + "<p>";
}
else
{
updItem[ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault()] += " " + itemMl.Value;
}
}
else
{
if (ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault() == "Specification")
{
updItem.Add(ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault(), "<p>" + itemMl.Key + " :" + itemMl.Value + "<p>");
}
else
{
updItem.Add(ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault(), itemMl.Value);
}
}
}
dicItemML.Remove(itemMl.Key);
}
foreach (var itemM2 in updItem)
{
dicItemML.Add(itemM2.Key, itemM2.Value);
}
}
List<CatalogProduct> ListKp = new List<CatalogProduct>();
foreach (var dicItem in ListJsonProductContent)
{
CatalogProduct Ctgkp = new CatalogProduct
{
Name = dicItem.ContainsKey("Name") ? dicItem["Name"] : "No Product",
Slug = dicItem.ContainsKey("Name") ? string.Concat(dicItem["Name"].Where(c => !char.IsWhiteSpace(c))).ToLower() : "No Slug",
Price = dicItem.ContainsKey("Price") ? decimal.Parse(dicItem["Price"], CultureInfo.InvariantCulture) : default,
ShortDescription = dicItem.ContainsKey("ShortDescription") ? dicItem["ShortDescription"] : null,
Description = dicItem.ContainsKey("Description") ? dicItem["Description"] : null,
Specification = dicItem.ContainsKey("Specification") ? dicItem["Specification"] : null,
RatingAverage = dicItem.ContainsKey("RatingAverage") ? double.Parse(dicItem["RatingAverage"], CultureInfo.InvariantCulture) : null};
ListKp.Add(Ctgkp);
}
using (var transaction = _context.Database.BeginTransaction())
{
try
{
int numP = 0;
var catalogProducts = _context.CatalogProducts.ToList();
foreach (var kp in ListKp)
{
if (!catalogProducts.Any(x => x.Name == kp.Name))
{
_context.CatalogProducts.Add(kp);
}
else
{
//Use AutoMapper automatically do the mapping
var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>().ForMember(c => c.Id, opt => opt.Ignore()));
var oldone = catalogProducts.FirstOrDefault(c => c.Name == kp.Name);
var mapper = config.CreateMapper();
oldone = mapper.Map<CatalogProduct, CatalogProduct>(kp, oldone);
_context.CatalogProducts.Update(oldone);
}
}
numP = _context.SaveChanges();
(from q in _context.K360MappingMasters
where q.ThirdPartyBaseUrlName == baseurl
select q).ToList().ForEach(x => x.InsertStatusFlag = true);
_context.SaveChanges();
transaction.Commit();
return Json("No conflicts. " + numP + " product details saved.");
}
catch (Exception ex)
{
transaction.Rollback();
return Json("Error occurred." + ex.Message);
throw new Exception();
}
}
推荐答案
对于核心代码:
var catalogProducts = _context.CatalogProducts.ToList();
foreach (var kp in ListKp)
{
if (!catalogProducts.Any(x => x.Name == kp.Name))
_context.CatalogProducts.Add(kp);
else
{
//Use AutoMapper automatically do the mapping
var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>().ForMember(c => c.Id, opt => opt.Ignore()));
var oldone = catalogProducts.FirstOrDefault(c => c.Name == kp.Name);
var mapper = config.CreateMapper();
oldone = mapper.Map<CatalogProduct, CatalogProduct>(kp, oldone);
_context.CatalogProducts.Update(oldone);
}
}
第一个细节将是您的DbContext是否设置为禁用更改跟踪代理.例如，如果以下代码在您的DbContext构造函数中或创建DbContext时:
The first detail will be whether your DbContext was set up to disable change tracking proxies. For instance if the following code is in your DbContext constructor or when the DbContext is created:
Configuration.AutoDetectChangesEnabled = false;
Configuration.ProxyCreationEnabled = false;
许多团队在遇到由于延迟加载而导致的性能问题时会关闭代理.(而不是固定数据访问方法以避免延迟加载)禁用代理和更改跟踪会使更新方案变得更难看.如果默认情况下这些功能处于关闭状态，那么我建议您为该操作暂时启用它们:
Many teams turn off proxies when they encounter performance problems tripping over lazy loading. (instead of fixing the data access approach to avoid the lazy loading) Disabling proxies and change tracking makes update scenarios uglier. If these are off by default then I would recommend enabling them temporarily for this operation:
_context.Configuration.AutoDetectChangesEnabled = true;
_context.Configuration.ProxyCreationEnabled = true;
var catalogProducts = _context.CatalogProducts.ToList(); 将从数据库中获取所有目录产品.根据您的数据大小，这可能不切实际.我建议一一阅读这些内容，或将XML中的产品名称合并到一个列表中，然后使用 Contains 获取所需的名称.通过名称/w启用更改跟踪来获取每个项目可以简化为:
var catalogProducts = _context.CatalogProducts.ToList(); is going to fetch all catalog products from your DB. Depending on your data size this may not be practical. I'd suggest reading these one by one or consolidating the product names from the XML into a list and fetch the desired ones using Contains. Fetching each item by name /w change tracking enabled can be simplified to:
// Avoid re-initializing automapper EVERY loop iteration.
var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>()
.ForMember(c => c.Id, opt => opt.Ignore()));
var mapper = config.CreateMapper();
foreach (var kp in ListKp)
{
var existingCatalogProduct = _context.CatalogProducts.SingleOrDefault(x => x.Name == kp.Name);
if (existingCatalogProduct == null)
_context.CatalogProducts.Add(kp);
else
mapper.Map<CatalogProduct, CatalogProduct>(kp, existingCatalogProduct);
}
// ...
_context.SaveChanges();
如果产品"实体具有任何导航属性，我建议也将其设置为"<代码>忽略".
If the Product entity has any navigation properties I would recommend setting those to Ignore as well.
使用更改跟踪时，我们不需要调用 Update .EF将自动为记录中的更改值编写 UPDATE 语句，如果这些值实际上是由于映射复制新值而实际更改的.对于 Mapper.Map< src，dest> ，我不确定返回值引用的是"dest"还是返回值所引用的内容.或新的对象引用.您可以忽略返回值，该方法将更新目标"字段中的值.对象参考.如果返回值是与"dest"不同的对象引用，则返回"dest".然后可以解释为什么将记录视为新行，因为它将作为单独的引用.
We don't need to call Update when using change tracking. EF will automatically compose UPDATE statements for changed values on records if those values actually change as a result of the map copying over new values. With Mapper.Map<src,dest> I'm not sure what the return value references, if it passes back "dest" or a new object reference. You can ignore the return value, the method will update the values in the "dest" object reference. If the return value is a different object reference than "dest" then that could explain why it is treating the record as a new row as it would be a separate reference.
当期望单个记录时，请使用 Single / SingleOrDefault ，而不是 First 变体方法. First 仅在期望可能的倍数时使用，并且应始终与 OrderBy 子句一起使用.
When you are expecting a single record, use the Single / SingleOrDefault rather than the First variant methods. First should only be used if you expect possible multiples and should always be used with an OrderBy clause.
更新:
如果名称"不足以唯一地标识要更新的记录还是插入的记录，则可以更改过滤器以提供足够的详细信息，以便在确定是否需要插入之前尝试标识该行:
If Name is not enough to uniquely identify a record to update vs. insert then you can change the filter to provide enough detail to try and identify the row before determining if an insert is required:
var existingCatalogProduct = _context.CatalogProducts
.SingleOrDefault(x => x.Name == kp.Name
&& x.ProductType = kp.ProductType
&& x.Category = kp.Category /*etc*/ );
这样，如果找到现有的目录产品行，则可以通过Map调用来更新其余详细信息，如果没有返回任何行，则可以插入新条目.
This way if you find an existing catalog product row you can update the remaining details based with the Map call, or insert a new entry if no row is returned.
这篇关于使用实体框架在ASP.NET Core中添加和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
