C#、LINQ 批处理项目 - 执行此操作的最佳方法是什么？

Question

我有 2 个数据库 - (1) Feed，(2) Production。提要数据库由我们每天获取的客户端文件提供，在我看来，它是只读数据源。当收到提要文件时，提要应用程序会执行其操作，然后最终调用生产站点上的 Web 服务。然后，该 Web 服务在 feed DB 和 Prod DB 之间进行同步。本质上，这是伪代码：

• 获取客户端的所有 Feed 项目
• 获取同一客户端的所有产品项目
• 使用 LINQ for Objects，获取 (1) 导致更新的所有项目，(2) 导致删除的所有项目和 ( 3) 所有导致 INSERT 的项目。
• 处理更新
• 处理删除
• 处理插入

对于分离插入、更新和删除的核心代码：

List<model.AutoWithImage> feedProductList = _dbFeed.AutoWithImage.Where(feedProduct => feedProduct.ClientID == ClientID).ToList();
List<model.vwCompanyDetails> companyDetailList = _dbRiv.vwCompanyDetails.Where(feedProduct => feedProduct.ClientID == ClientID).ToList();
foreach (model.vwCompanyDetails companyDetail in companyDetailList)
{
    List<model.Product> rivProductList = _dbRiv.Product.Include("Company").Where(feedProduct => feedProduct.Company.CompanyId == companyDetail.CompanyId).ToList();

    foreach (model.AutoWithImage feedProduct in feedProductList)
    {
        bool alreadyExists = false;
        model.Company company = null;
        foreach (model.Product rivProduct in rivProductList)
        {
            if (feedProduct.StockNumber == rivProduct.SKU)
            {
                alreadyExists = true;

                // Active feed items...
                if (feedProduct.Active)
                {
                    // Changed since last sync...
                    if (feedProduct.Updated > rivProduct.LastFeedUpdate)
                    {
                        model.Product updateProduct = new model.Product();
                        updateProduct.ProductId = rivProduct.ProductId;
                        // removed for brevity
                        updateProductList.Add(updateProduct);
                    }
                    // Not changed since last sync...
                    else if (feedProduct.Updated <= rivProduct.LastFeedUpdate)
                    {
                        //nop
                    }
                }
                // No longer active feed products...
                else if (!feedProduct.Active)
                {
                    model.Product deleteProduct = new model.Product();
                    deleteProduct = rivProduct;
                    // removed for brevity
                    deleteProductList.Add(deleteProduct);
                }
            }

            if (company == null)
                company = rivProduct.Company;
        }

        // Found feedProduct new product...
        if (!alreadyExists)
        {
            model.Product insertProduct = new Product();
            insertProduct.ProductId = Guid.NewGuid();
            // removed for brevity
            insertProductList.Add(insertProduct);
        }
    }
}

是的，我知道有更有效的方法可以做到这一点，并且我开始使用它们。然而，上面的代码工作得相对较快，并将我的数据分成 3 个 List<> 。套。

我的问题更多是关于处理 _dbRiv.SaveChanges() 方法。当我发出它时，它似乎会触发所有 3 组（上图）。我正在尝试追踪唯一的密钥违规，并且在批量中我没有找到违反约束的一两个记录。我确信我在思考 LINQ for SQL 的真正工作原理时遗漏了一些东西。

我想做的是：

• 仅对更新执行保存。 然后做一些其他事情，
• 仅对删除执行保存。 做一些其他事情然后，
• 执行保存，一项一项（暂时） 在插入件上。

有没有办法一次批量发出 SaveChanges？
有没有一种方法可以 foreach InsertProductList 对象并一次执行一行 SaveChanges？ 我是不是找错了树？

---

编辑： 虽然我知道我可以从 EF 调用存储过程，但我的目的是学习如何将存储过程转换为 EF。

我在 SQL 中编写了我想要的内容，它就在这里（这正是我们需要的方式）：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[ExecuteSync] @ClientID AS BIGINT AS
BEGIN
    DECLARE @cid UNIQUEIDENTIFIER

    DECLARE c1 CURSOR FOR
    SELECT CompanyID FROM CompanyDetails WHERE ClientID = @ClientID
    OPEN c1
    FETCH NEXT FROM c1 INTO @cid
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET NOCOUNT ON
        SELECT 'Syncing feed data for ' + CompanyName FROM Company WHERE CompanyId = @cid
        SET NOCOUNT OFF
        -- n/a --------------------------------------------------------------------------------------------------------------------------------------------------------------------
        --SELECT a.*
        --     , p.*
        --  FROM RIVFeeds..AutoWithImage a
        -- INNER JOIN Product p ON a.StockNumber = p.SKU
        -- WHERE ClientID = @ClientID
        --   AND a.Active = 1
        --   AND a.Updated <= p.LastFeedUpdate

        -- Needs UPDATE -----------------------------------------------------------------------------------------------------------------------------------------------------------
        PRINT '--[ UPDATE ]--'
        UPDATE Product
           SET [Description] = ''
             , [Image] = a.ImageURL
             , isDeleted = a.Active ^ 1
             , isFromFeed = 1
             , LastFeedUpdate = a.Updated
             , LowestPrice = a.GuaranteedSalePrice
             , RetailPrice = a.ListPrice
             , [Title] = ''
             , Updated = GETUTCDATE()
             , UpdatedBy = 'Feed Sync Process'
          FROM RIVFeeds..AutoWithImage a
         INNER JOIN Product p ON a.StockNumber = p.SKU AND a.AutoID = p.alternateProductID
         WHERE ClientID = @ClientID
           AND p.CompanyID = @cid
           AND a.Updated > p.LastFeedUpdate

        -- Needs BACKUP -----------------------------------------------------------------------------------------------------------------------------------------------------------
        PRINT '--[ BACKUP #1 ]--'
        INSERT INTO ProductDeleted(ProductId, alternateProductID, CompanyID, CharacterId, URLDomain, SKU, Title, Description, ButtonConfig, RetailPrice, LowestPrice, Image
                  , BackgroundColor, FontColor, buttonPositionCSS, isFromFeed, isDeleted, LastFeedUpdate, Created, CreatedBy, Updated, UpdatedBy)
        SELECT p.ProductId, p.alternateProductID, p.CompanyID, p.CharacterId, p.URLDomain, p.SKU, p.Title, p.Description, p.ButtonConfig, p.RetailPrice, p.LowestPrice, p.Image
                  , p.BackgroundColor, p.FontColor, p.buttonPositionCSS, p.isFromFeed, p.isDeleted, p.LastFeedUpdate, p.Created, p.CreatedBy, GETUTCDATE(), 'Feed Sync Process'
          FROM Product p
         WHERE p.isDeleted = 1
           AND p.CompanyID = @cid

        -- Needs DELETE -----------------------------------------------------------------------------------------------------------------------------------------------------------
        PRINT '--[ DELETE #1 ]--'
        DELETE FROM Product
         WHERE CompanyID = @cid
           AND isDeleted = 1

        -- Needs INSERT -----------------------------------------------------------------------------------------------------------------------------------------------------------
        PRINT '--[ INSERT ]--'
        INSERT INTO Product(ProductId, alternateProductID, CompanyID, CharacterId, URLDomain, SKU, Title, Description, ButtonConfig, RetailPrice, LowestPrice, Image
                  , BackgroundColor, FontColor, buttonPositionCSS, isFromFeed, isDeleted, LastFeedUpdate, Created, CreatedBy)
        SELECT NEWID()
             , a.AutoID
             , @cid
             , ''
             , ''
             , a.StockNumber
             , ''
             , ''
             , ''
             , a.ListPrice
             , a.GuaranteedSalePrice
             , COALESCE(a.ImageURL, '')
             , ''
             , ''
             , ''
             , 1
             , 0
             , a.Updated
             , GETUTCDATE()
             , 'Feed Sync Process'
          FROM RIVFeeds..AutoWithImage a
         WHERE a.ClientID = @ClientID
           AND a.StockNumber NOT IN (SELECT p.sku FROM Product p WHERE CompanyID = @cid AND isFromFeed = 1)
           AND a.AutoID NOT IN (SELECT p.alternateProductID FROM Product p WHERE CompanyID = @cid AND isFromFeed = 1)
           AND a.Active = 1

        --PRINT @cid

        FETCH NEXT FROM c1 INTO @cid
    END
    CLOSE c1
    DEALLOCATE c1
END
GO

现在我正在利用实体框架编写它（尚未完成）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RivWorks.Model;
using RivWorks.Model.Entities;
using RivWorks.Model.Feeds;
using RivWorks.Model.RivData;
using model = RivWorks.Model.Entities;

namespace RivWorks.Controller.Sync
{
    public static class Feeds
    {
        #region Public Methods
        public static bool Product(long ClientID)
        {
            bool retFlag = true;
            DateTime startTime = DateTime.Now;
            DateTime splitTime = startTime;
            Guid companyID;
            DateTime createdUpdated = DateTime.UtcNow;
            string createdUpdatedBy = "Feed Sync Process";
            List<SyncMessage> Activity = new List<SyncMessage>();
            List<model.Product> insertProductList = new List<Product>();
            List<model.Product> updateProductList = new List<Product>();
            List<model.Product> deleteProductList = new List<Product>();

            using (RivEntities _dbRiv = new RivWorksStore(Stores.RivConnString).NegotiationEntities())
            {
                using (FeedsEntities _dbFeed = new FeedStoreReadOnly(Stores.FeedConnString).ReadOnlyEntities())
                {
                    List<model.AutoWithImage> feedProductList = _dbFeed.AutoWithImage.Where(a => a.ClientID == ClientID).ToList();
                    List<model.vwCompanyDetails> companyDetailList = _dbRiv.vwCompanyDetails.Where(a => a.ClientID == ClientID).ToList();

                    foreach (model.vwCompanyDetails companyDetail in companyDetailList)
                    {
                        companyID = companyDetail.CompanyId;
                        List<model.Product> rivProductList = _dbRiv.Product.Include("Company").Where(a => a.Company.CompanyId == companyID).ToList();

                        #region Handle UPDATES...
                        var updateFeedProductList = from f in feedProductList
                                                    join r in rivProductList
                                                    on f.AutoID equals r.alternateProductID
                                                    where f.Updated > r.LastFeedUpdate.Value || f.Active == false
                                                    select f;
                        var updateRivProductList = from r in rivProductList
                                                   join f in feedProductList
                                                   on r.alternateProductID equals f.AutoID
                                                   where f.Updated > r.LastFeedUpdate.Value || f.Active == false
                                                   select r;

                        foreach (model.AutoWithImage feedProduct in updateFeedProductList)
                        {
                            bool alreadyExists = false;
                            foreach (model.Product rivProduct in updateRivProductList)
                            {
                                if (feedProduct.StockNumber == rivProduct.SKU && feedProduct.AutoID == rivProduct.alternateProductID)
                                {
                                    alreadyExists = true;

                                    // Active feed items...
                                    if (feedProduct.Active)
                                    {
                                        // Changed since last sync...
                                        if (feedProduct.Updated > rivProduct.LastFeedUpdate)
                                        {
                                            rivProduct.ProductId = rivProduct.ProductId;
                                            rivProduct.Company = rivProduct.Company;
                                            rivProduct.alternateProductID = feedProduct.AutoID;
                                            rivProduct.Description = String.Empty.EnforceNoNull();
                                            rivProduct.Image = feedProduct.ImageURL.EnforceNoNull();
                                            rivProduct.isDeleted = false;
                                            rivProduct.isFromFeed = true;
                                            rivProduct.LastFeedUpdate = feedProduct.Updated;
                                            rivProduct.LowestPrice = feedProduct.GuaranteedSalePrice;
                                            rivProduct.RetailPrice = feedProduct.ListPrice;
                                            rivProduct.Title = String.Empty.EnforceNoNull();
                                            rivProduct.Updated = createdUpdated;
                                            rivProduct.UpdatedBy = createdUpdatedBy;
                                        }
                                        // Not changed since last sync...
                                        else if (feedProduct.Updated <= rivProduct.LastFeedUpdate)
                                        {
                                            // nop
                                        }
                                    }
                                }
                            }
                        }
                        _dbRiv.SaveChanges();
                        #endregion

                        #region Handle DELETES...
                        List<model.Product> deleteRivProductList = _dbRiv.Product
                                                                         .Include("Company")
                                                                         .Where(a => a.Company.CompanyId == companyID
                                                                                  && a.isDeleted == true)
                                                                         .ToList();
                        // transfer to ProductDelete table...
                        foreach (model.Product delProduct in deleteRivProductList)
                        {
                            model.ProductDeleted productDeleted = new ProductDeleted();
                            productDeleted.alternateProductID = delProduct.alternateProductID;
                            productDeleted.BackgroundColor = delProduct.BackgroundColor;
                            productDeleted.ButtonConfig = delProduct.ButtonConfig;
                            productDeleted.buttonPositionCSS = delProduct.buttonPositionCSS;
                            productDeleted.CharacterId = delProduct.CharacterId;
                            productDeleted.CompanyID = companyID;
                            productDeleted.Created = delProduct.Created;
                            productDeleted.CreatedBy = delProduct.CreatedBy;
                            productDeleted.Description = delProduct.Description;
                            productDeleted.FontColor = delProduct.FontColor;
                            productDeleted.Image = delProduct.Image;
                            productDeleted.isDeleted = delProduct.isDeleted;
                            productDeleted.isFromFeed = delProduct.isFromFeed;
                            productDeleted.LastFeedUpdate = delProduct.LastFeedUpdate;
                            productDeleted.LowestPrice = delProduct.LowestPrice;
                            productDeleted.ProductId = delProduct.ProductId;
                            productDeleted.RetailPrice = delProduct.RetailPrice;
                            productDeleted.SKU = delProduct.SKU;
                            productDeleted.Title = delProduct.Title;
                            productDeleted.Updated = createdUpdated;
                            productDeleted.UpdatedBy = createdUpdatedBy;
                            productDeleted.URLDomain = delProduct.URLDomain;
                            _dbRiv.AddToProductDeleted(productDeleted);
                        }
                        int moves = _dbRiv.SaveChanges();

                        // delete the records...
                        foreach (model.Product delProduct in deleteRivProductList)
                        {
                            _dbRiv.DeleteObject(delProduct);
                        }
                        int deletes = _dbRiv.SaveChanges();
                        #endregion

                        #region Handle INSERTS...
                        // to be written...
                        #endregion
                    }
                }
            }
            return retFlag;  // remember to set this...
        }
        #endregion
    }
}

我知道现在有点混乱。我将其包括在内，是为了如果有人对如何更好地清理此内容、更好地利用 EF 来执行此操作等提出建议，我将不胜感激。我知道有一些非常巧妙的方法可以跨实体进行连接，并且想学习而不是搬起石头砸自己的脚。

Answer 1

您实际上在哪里插入或删除记录？我看到您将它们添加到 insertProductList 和 deleteProductList 中，但您从未在表 _dbRiv.Product 上调用 Insert 或 Delete。

我认为您想要完成的事情类似于以下内容：

//perform all updates
_dbRiv.SubmitChanges();

//perform all deletes
_dbRiv.Product.DeleteAllOnSubmit(deleteProductList);
_dbRiv.SubmitChanges();

//perform inserts, one at a time
foreach(model.Product p in insertProductList)
{
    _dbRiv.Product.InsertOnSubmit(p);
    _dbRiv.SubmitChanges();
}

但是，尚不清楚您期望如何执行更新。看来您应该更新 rivProduct 的属性，而不是创建 model.Product 的新实例并设置其属性。否则，使用您拥有的代码，我相信您需要使用 _dbRiv.Product.Attach(updateProduct, rivProduct) 附加 updateProduct ，以便 L2S 知道哪些属性已更改。

Answer 2

我现在已经运行代码了。由于没有其他人回答，我必须假设我正朝着正确的方向前进。谢谢。