将车辆信息从JSON列表复制到关系数据库

Question

我正在寻找建议，使我的for循环迭代更快。大约有2000行数据。下面的代码需要大量的时间来运行。到目前为止，我已经跟踪了各种堆栈溢出的答案。例如，减少db查询并使用大容量插入来提高EF性能。只是略有改善。

List<VehicleApplication> vehicleApplicationList = new List<VehicleApplication>();
List<ProductVehicleApplication> productVehicleApplicationList = new List<ProductVehicleApplication>();

if (inventoryjson["InventoryItems"][0]["VehicleApplications"] != null)
{
    for (int i = 0; i < inventoryjson["InventoryItems"][0]["VehicleApplications"].Count(); i++)
    {
        //database vehDb = new database();
        VehicleApplication vehicleApplication = new VehicleApplication();
        vehicleApplication.CategoryName = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["CategoryName"].ToString();
        vehicleApplication.MakeName = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["MakeName"].ToString();
        vehicleApplication.MfrLabel = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["MfrLabel"].ToString();
        vehicleApplication.ModelName = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["ModelName"].ToString();
        vehicleApplication.Note = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["Note"].ToString();
        vehicleApplication.PartTypeName = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["PartTypeName"].ToString();
        vehicleApplication.PositionName = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["PositionName"].ToString();
        vehicleApplication.Qty = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["Qty"].ToString();
        vehicleApplication.SubCategoryName = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["SubCategoryName"].ToString();
        vehicleApplication.YearID = inventoryjson["InventoryItems"][0]["VehicleApplications"][i]["YearID"].ToString();

        var existingVehicleApplication = dbVehiclesList.Where(x => x.MakeName == vehicleApplication.MakeName && x.ModelName == vehicleApplication.ModelName
            && x.CategoryName == vehicleApplication.CategoryName
            && x.MfrLabel == vehicleApplication.MfrLabel
            && x.PartTypeName == vehicleApplication.PartTypeName
            && x.PositionName == vehicleApplication.PositionName
            && x.Qty == vehicleApplication.Qty
            && x.YearID == vehicleApplication.YearID
            && x.SubCategoryName == vehicleApplication.SubCategoryName
            ).SingleOrDefault();

        if (existingVehicleApplication == null && vehicleApplicationList.Any(x => x.MakeName == vehicleApplication.MakeName && x.ModelName == vehicleApplication.ModelName && x.CategoryName == vehicleApplication.CategoryName && x.MfrLabel == vehicleApplication.MfrLabel && x.PartTypeName == vehicleApplication.PartTypeName && x.PositionName == vehicleApplication.PositionName && x.Qty == vehicleApplication.Qty && x.YearID == vehicleApplication.YearID && x.SubCategoryName == vehicleApplication.SubCategoryName) == false)
        {
            vehicleApplicationList.Add(vehicleApplication);
        }
        else
        {
            vehicleApplication = dbObj.VehicleApplication.SingleOrDefault(
            x => x.MakeName == vehicleApplication.MakeName
            && x.ModelName == vehicleApplication.ModelName
            && x.CategoryName == vehicleApplication.CategoryName
            && x.MfrLabel == vehicleApplication.MfrLabel
            && x.PartTypeName == vehicleApplication.PartTypeName
            && x.PositionName == vehicleApplication.PositionName
            && x.Qty == vehicleApplication.Qty
            && x.SubCategoryName == vehicleApplication.SubCategoryName
            && x.YearID == vehicleApplication.YearID);
        }

        ProductVehicleApplication productVehicleApplication = new ProductVehicleApplication();
        productVehicleApplication.Product = dbObj.Product.Single(x => x.Id == product.Id);
        productVehicleApplication.VehicleApplication = vehicleApplication;
        productVehicleApplicationList.Add(productVehicleApplication);
    }
}

dbObj.BulkInsert(productVehicleApplicationList);
dbObj.BulkInsert(vehicleApplicationList);
dbObj.SaveChanges();

更新:1来自评论，我尝试了以下。但它仍然是缓慢的。

HashSet<VehicleApplication> vehicleApplicationList = new HashSet<VehicleApplication>();
                List<ProductVehicleApplication> productVehicleApplicationList = new List<ProductVehicleApplication>();
                if (jsonProduct["VehicleApplications"] != null)
                {
                 for (int i = 0; i < jsonProduct["VehicleApplications"].Count(); i++){
                            var vehicleApp = jsonProduct["VehicleApplications"][i];

                            VehicleApplication vehicleApplication = new VehicleApplication();
                            vehicleApplication.CategoryName = vehicleApp["CategoryName"].ToString();
                            vehicleApplication.MakeName = vehicleApp["MakeName"].ToString();
                            vehicleApplication.MfrLabel = vehicleApp["MfrLabel"].ToString();
                            vehicleApplication.ModelName = vehicleApp["ModelName"].ToString();
                            vehicleApplication.Note = vehicleApp["Note"].ToString();
                            vehicleApplication.PartTypeName = vehicleApp["PartTypeName"].ToString();
                            vehicleApplication.PositionName = vehicleApp["PositionName"].ToString();
                            vehicleApplication.Qty = vehicleApp["Qty"].ToString();
                            vehicleApplication.SubCategoryName = vehicleApp["SubCategoryName"].ToString();
                            vehicleApplication.YearID = vehicleApp["YearID"].ToString();


                            var existingVehicleApplication = dbVehiclesList.Where(x => x.MakeName == vehicleApplication.MakeName && x.ModelName == vehicleApplication.ModelName 
                                && x.CategoryName == vehicleApplication.CategoryName
                                && x.MfrLabel == vehicleApplication.MfrLabel
                                && x.PartTypeName == vehicleApplication.PartTypeName
                                && x.PositionName == vehicleApplication.PositionName
                                && x.Qty == vehicleApplication.Qty
                                && x.YearID == vehicleApplication.YearID
                                && x.SubCategoryName == vehicleApplication.SubCategoryName
                                ).SingleOrDefault(); 
                                
                    

                            if (existingVehicleApplication == null && vehicleApplicationList.Any(x => x.MakeName == vehicleApplication.MakeName && x.ModelName == vehicleApplication.ModelName && x.CategoryName == vehicleApplication.CategoryName && x.MfrLabel == vehicleApplication.MfrLabel && x.PartTypeName == vehicleApplication.PartTypeName && x.PositionName == vehicleApplication.PositionName && x.Qty == vehicleApplication.Qty && x.YearID == vehicleApplication.YearID && x.SubCategoryName == vehicleApplication.SubCategoryName) == false)
                            {
                                vehicleApplicationList.Add(vehicleApplication);
                            }
                            else
                            {
                                vehicleApplication = existingVehicleApplication;
                                //vehicleApplication = dbVehiclesList.SingleOrDefault(
                                //x => x.MakeName == vehicleApplication.MakeName
                                //&& x.ModelName == vehicleApplication.ModelName
                                //&& x.CategoryName == vehicleApplication.CategoryName
                                //&& x.MfrLabel == vehicleApplication.MfrLabel
                                //&& x.PartTypeName == vehicleApplication.PartTypeName
                                //&& x.PositionName == vehicleApplication.PositionName
                                //&& x.Qty == vehicleApplication.Qty
                                //&& x.SubCategoryName == vehicleApplication.SubCategoryName
                                //&& x.YearID == vehicleApplication.YearID);

                            }

                            ProductVehicleApplication productVehicleApplication = new ProductVehicleApplication();
                            productVehicleApplication.Product = dbProductList.Single(x=>x.Id==product.Id);
                            productVehicleApplication.VehicleApplication = vehicleApplication;


                            productVehicleApplicationList.Add(productVehicleApplication);
                        }
                    }

                    dbObj.BulkInsert(productVehicleApplicationList);
                    dbObj.BulkInsert(vehicleApplicationList);
                    dbObj.SaveChanges();
```

Answer 1

jsonProduct["VehicleApplications"].Count()应该在迭代之外，否则它将在影响性能的每个迭代上执行。所以，你应该做：

var count = jsonProduct["VehicleApplications"].Count();

for (int i = 0; i < count; i++)
{
    var vehicleApp = jsonProduct["VehicleApplications"][i];
    ///...... etc.
}

使用SingleOrDefault比FirstOrDefault慢，这是因为他们的行为。SingleOrDefault将根据已创建的项重新检查可枚举项，以查看是否存在重复项，而FirstOrDefault则不执行此检查。在您的示例中，您不需要检查可枚举项，您需要获得匹配条件的第一个项。因此，使用FirstOrDefault将是更好的选择。

另外，当您使用HashSet时，在添加项时并不需要重新检查集合中的重复项，因为HashSet会处理这个问题，并且总是存储唯一的项。因此，如果尝试添加HashSet中已经存在的项，则不会添加或更改现有项。

试试这个版本，看看它是如何执行的：

var vehicleApplications = jsonProduct["VehicleApplications"]; 

if(vehicleApplications != null)
{
    HashSet<VehicleApplication> vehicleApplicationHashSet = new HashSet<VehicleApplication>();
    
    List<ProductVehicleApplication> productVehicleApplicationList = new List<ProductVehicleApplication>();

    var count = vehicleApplications.Count();

    for (int i = 0; i < count; i++)
    {
        var vehicleApp = jsonProduct["VehicleApplications"][i];

        VehicleApplication vehicleApplication = new VehicleApplication
        {
            CategoryName = vehicleApp["CategoryName"].ToString(),
            MakeName = vehicleApp["MakeName"].ToString(),
            MfrLabel = vehicleApp["MfrLabel"].ToString(),
            ModelName = vehicleApp["ModelName"].ToString(),
            Note = vehicleApp["Note"].ToString(),
            PartTypeName = vehicleApp["PartTypeName"].ToString(),
            PositionName = vehicleApp["PositionName"].ToString(),
            Qty = vehicleApp["Qty"].ToString(),
            SubCategoryName = vehicleApp["SubCategoryName"].ToString(),
            YearID = vehicleApp["YearID"].ToString()
        };
        
        var existingVehicleApplication = dbVehiclesList.FirstOrDefault(x =>
               x.MakeName == vehicleApplication.MakeName 
            && x.ModelName == vehicleApplication.ModelName 
            && x.CategoryName == vehicleApplication.CategoryName
            && x.MfrLabel == vehicleApplication.MfrLabel
            && x.PartTypeName == vehicleApplication.PartTypeName
            && x.PositionName == vehicleApplication.PositionName
            && x.Qty == vehicleApplication.Qty
            && x.YearID == vehicleApplication.YearID
            && x.SubCategoryName == vehicleApplication.SubCategoryName); 
        
        if(existingVehicleApplication != null)
        {
            vehicleApplication = existingVehicleApplication;
        }
        else
        {
            // if the vehicleApp is already in the hashset, then it'll not be added.
            vehicleApplicationHashSet.Add(vehicleApplication);
        }
        
        ProductVehicleApplication productVehicleApplication = new ProductVehicleApplication
        {
            Product = dbProductList.FirstOrDefault(x=>x.Id==product.Id),
            VehicleApplication = vehicleApplication
        };
        
        productVehicleApplicationList.Add(productVehicleApplication);
    }

    dbObj.BulkInsert(productVehicleApplicationList);
    dbObj.BulkInsert(vehicleApplicationList);
    dbObj.SaveChanges();
}