Left/Right/Inner joins using C# and LINQ

Question

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:

1. Find all SKUs in the feed that are not in the target
2. Find all SKUs that are in both, that are active feed products and update the target
3. 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

Answer 1

The LINQ-to-objects approach would be something like demonstration. Here I have two lists of strings and I want to pull matches, elements from alphas not in betas, and elements from betas not in alphas. The LINQ syntax is fairly simple.

List<string> alphas = new List<string>() { "a", "b", "c", "d", "e" };
List<string> betas = new List<string>() { "a", "c", "e", "g", "i" };

var matches = from alpha in alphas
                join beta in betas
                on alpha equals beta
                select alpha;

var noBetas = from alpha in alphas
                join beta in betas
                on alpha equals beta
                into gj
                from b in gj.DefaultIfEmpty()
                where b == null
                select alpha;

var noAlphas = from beta in betas
                join alpha in alphas
                on beta equals alpha
                into gj
                from a in gj.DefaultIfEmpty()
                where a == null
                select beta;

The results of each is an IEnumerable<string>, and iterating over matches would reveal a, c, and e. noBetas would yield b and d. noAlphas would yield g and i.

I believe that's what you were asking for. Apply that to your lists of objects by joining on key fields instead of my simplistic scenario of one string equaling another.