Left/Right/Inner joins using C# and LINQ
- by Keith Barrows
I am trying to figure out how to do a series of queries to get the updates, deletes and inserts segregated into their own calls. I have 2 tables, one in each of 2 databases. One is a Read Only feeds database and the other is the T-SQL R/W Production source. There are a few key columns in common between the two.
What I am doing to setup is this:
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)
{
List<model.Product> productList = _dbRiv.Product.Include("Company").Where(a => a.Company.CompanyId == companyDetail.CompanyId).ToList();
}
Now that I have a (source) list of products from the feed, and an existing (target) list of products from my prod DB I'd like to do 3 things:
Find all SKUs in the feed that are not in the target
Find all SKUs that are in both, that are active feed products and update the target
Find all SKUs that are in both, that are inactive and soft delete from the target
What are the best practices for doing this without running a double loop? Would prefer a LINQ 4 Objects solution as I already have my objects.
EDIT: BTW, I will need to transfer info from feed rows to target rows in the first 2 instances, just set a flag in the last instance.
TIA
