Entity Framework - Is there a best practice way to accomplish this?

Question

I have 2 databases and need to transfer data from the 1st database (Read Only) into the 2nd database (Read Write) and am wondering if there is a better way than what I am doing. Still new to EF so don't know all the tricks and would like to learn how to keep my round trips to the databases to a minimum.

The code basically responds to a request for a product, executes a sync, or not, on that product based on the LastUpdatedDate in both databases:

public static void SyncProduct(string SKU, string feedConnString, string rivConnString)
{
    using (FeedsEntities _dbFeed = new FeedStoreReadOnly(feedConnString).ReadOnlyEntities())
    {
        using (RivEntities _dbRiv = new RivWorksStore(rivConnString).NegotiationEntities())
        {
            IList<AutoWithImage> efProductList = _dbFeed.AutoWithImage.Where(a => a.StockNumber == SKU).ToList();
            foreach (AutoWithImage product in efProductList)
            {
                IList<vwCompanyDetails> efCompanyList = _dbRiv.vwCompanyDetails.Where(a => a.ClientID == product.ClientID).ToList();
                foreach (vwCompanyDetails company in efCompanyList)
                {
                    IList<Product> updateCheck = _dbRiv.Product.Include("Company").Where(a => a.SKU == SKU && a.Company.CompanyId == company.CompanyId && a.LastFeedUpdate < product.Updated).ToList();
                    IList<Product> insertCheck = _dbRiv.Product.Include("Company").Where(a => a.SKU == SKU && a.Company.CompanyId == company.CompanyId && (a.LastFeedUpdate == null || a.LastFeedUpdate >= product.Updated)).ToList();

                    // UPDATE products...
                    if (updateCheck.Count > 0)
                    {
                        // Now, update all products that have been updated in the feed...
                        var efUpdateProductList = _dbRiv.Product.Where(a => a.SKU == SKU && a.Company.CompanyId == company.CompanyId && a.LastFeedUpdate < product.Updated);
                        foreach (Product updateProduct in efUpdateProductList)
                        {
                            updateProduct.LastFeedUpdate = product.Updated;
                            updateProduct.BackgroundColor = null;    
                            updateProduct.ButtonConfig = null;    
                            updateProduct.buttonPositionCSS = null;    
                            updateProduct.CharacterId = String.Empty;
                            updateProduct.Description = null;    
                            updateProduct.FontColor = null;    
                            updateProduct.Image = null;    
                            updateProduct.isDeleted = !product.Active;
                            updateProduct.isFromFeed = true;
                            updateProduct.LowestPrice = null;
                            updateProduct.RetailPrice = null;
                            updateProduct.Title = null;    
                            updateProduct.URLDomain = null;    
                        }
                        _dbRiv.SaveChanges();
                    }
                    // INSERT products...
                    else if (insertCheck.Count <= 0)
                    {
                        Product insertProduct = new Product();
                        Company insertCompany = _dbRiv.Company.Where(a => a.CompanyId == company.CompanyId).FirstOrDefault();
                        insertProduct.ProductId = Guid.NewGuid();
                        insertProduct.alternateProductID = product.AutoID;
                        insertProduct.LastFeedUpdate = product.Updated;
                        insertProduct.SKU = SKU;
                        insertProduct.isDeleted = !product.Active;
                        insertProduct.isFromFeed = true;
                        insertProduct.BackgroundColor = null;    
                        insertProduct.ButtonConfig = null;    
                        insertProduct.buttonPositionCSS = null;    
                        insertProduct.CharacterId = String.Empty;
                        insertProduct.Description = null;    
                        insertProduct.FontColor = null;    
                        insertProduct.Image = null;    
                        insertProduct.LowestPrice = null;
                        insertProduct.RetailPrice = null;
                        insertProduct.Title = null;    
                        insertProduct.URLDomain = null;    

                        insertCompany.Product.Add(insertProduct);
                        _dbRiv.SaveChanges();
                    }
                }
            }
        }
    }
}

I think I am doing things in a logical manner but am open to tweaking this code. I am also writing a sync that is client based (a client may have several hundred products) and that is turning out to be several hundred round trips! Ughh!

An abbreviated Company Sync:

public static void SyncCompanyProducts(long ClientID, string feedConnString, string rivConnString)
{
    using (FeedsEntities _dbFeed = new FeedStoreReadOnly(feedConnString).ReadOnlyEntities())
    {
        using (RivEntities _dbRiv = new RivWorksStore(rivConnString).NegotiationEntities())
        {
            vwCompanyDetails company = _dbRiv.vwCompanyDetails.Where(a => a.ClientID == ClientID).FirstOrDefault();
            if (company != null)
            {
                IList<AutoWithImage> sourceProductList = _dbFeed.AutoWithImage.Where(a => a.ClientID == ClientID).ToList();
                foreach (AutoWithImage sourceProduct in sourceProductList)
                {
                    IList<Product> updateCheck = _dbRiv.Product.Where(a => a.alternateProductID == sourceProduct.AutoID && a.LastFeedUpdate < sourceProduct.Updated).ToList();
                    IList<Product> insertCheck = _dbRiv.Product.Where(a => a.alternateProductID == sourceProduct.AutoID && a.LastFeedUpdate >= sourceProduct.Updated).ToList();
                    // UPDATE products...
                    if (updateCheck.Count > 0)
                    {
                    }
                    // INSERT products...
                    else if (insertCheck.Count <= 0)
                    {
                    }
                }
            }
        }
    }
}

Each time a sync is run these are the possible cases:

• New product in feed - does not exist in working DB - Insert
• Updated product in feed - does not exist in working DB - Insert
• Updated product in feed - exists in working DB - Overwrite
• Deleted product in feed - does not exist in working DB - Ignore
• Deleted product in feed - exists in working DB - Overwrite

TIA

Answer 1

I am assuming with the low view rate and no solid answers that this was not a good question. Am closing this and will try another tactic...