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

Question

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

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.

TIA

Answer 1

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.

Answer 2

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.