C#, LINQ batch items - what is the best way to do this?

I have 2 databases - (1) Feeds, (2) Production. The feeds database is fed from client files that we get on a daily basis and is, from my viewpoint, a read only source of data. When a feed file is received the feed app does its thing then finally calls a web service on the production site. This web service then does a sync between the feeds DB and the Prod DB. In essence this is the pseudo code:

• Get all Feed items for a client
• Get all prod items for the same client
• Using LINQ for Objects, get (1) All items that cause an UPDATE, (2) All items that cause a DELETE and (3) All items that cause an INSERT.
• Process UPDATES
• Process DELETES
• Process INSERTS

For the core piece of code that separates INSERTS, UPDATES and DELETES:

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);
        }
    }
}

Yes, I know there are more efficient ways of doing this and I am starting to use them. However, the code above works, relatively fast and breaks my data into 3 List<> sets.

My question is more on handling the _dbRiv.SaveChanges() method. When I issue it it appears to fire off all 3 sets (above). I am trying to track down a unique key violation and with this in a batch I am not finding the one or two records that are guilty of violating the constraint. I am sure I missed something somewhere in my thinking of how LINQ for SQL really works.

What I'd like to do is:

• Execute a Save on just the UPDATES. Do some other stuff then,
• Execute a Save on just the DELETES. Do some other stuff then,
• Execute a Save, one by one (for now) on the INSERTS.

Is there some way to issue a SaveChanges on one batch at a time?

Is there a way to foreach the InsertProductList object and do a SaveChanges one row at a time? Am I barking up the wrong tree?

EDIT: While I know I can call a stored proc from EF, my intention is to learn how to convert a stored proc to EF.

I wrote what I want in SQL and here it is (and this works exactly how we need it to):

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, alternat开发者_C百科eProductID, 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

Now I am writing it in code utilizing Entity Frameworks (not completed yet):

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
    }
}

I know it's a bit messy right now. I am including this so if anyone has suggestions on how to better clean this up, better ways to utilize EF to do this, etc, I would appreciate it. I know there are some very slick ways of doing joins across entities and would like to learn rather than shooting myself in the foot.

Where are you actually inserting or deleting records? I see you add them to insertProductList and deleteProductList, but you never call Insert or Delete on your table _dbRiv.Product.

I think what you're trying to accomplish is something like the following:

//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();
}

However, it's not clear how you're expecting to perform updates. It seems that instead of creating a new instance of model.Product and setting its properties, you should be updating the properties of rivProduct. Otherwise, with the code you have, I believe you will need to attach updateProduct using _dbRiv.Product.Attach(updateProduct, rivProduct) so that L2S will know which properties have changed.

I've got running code in place now. Since no one else is answering I have to assume that I am heading in the right direction. Thanks.