Linq lambda joining a column in one table to three columns in another-CodePudding

I need to write a LINQ query joining one DataTable column to a value that is in one of three possible columns in another DataTable.

The below works but doesn't seem quite right. Is there a better way?

        var query = dtDescriptions.AsEnumerable()
            .GroupJoin(dtProducts.AsEnumerable(),
                idA => idA.Field<string>("idA"),
                productid => productid.Field<string>("Product ID"),
                (idA, productid) => new { IDA = idA, PRODUCTID = productid })
            .GroupJoin(dtProducts.AsEnumerable(),
                idB => idB.IDA.Field<string>("idB"),
                productid => productid.Field<string>("Product ID"),
                (idB, productid) => new { IDB = idB, PRODUCTID = productid })
            .GroupJoin(dtProducts.AsEnumerable(),
                idC => idC.idB.IDA.Field<string>("idC"),
                productid => productid.Field<string>("Product ID"),
                (idC, productid) => new { IDC = idC, PRODUCTID = productid });

CodePudding user response：

This seems much better:

        var query = dtdescriptions.AsEnumerable()
            .SelectMany(x =>
                    new[] { x.Field<string>("idA"), x.Field<string>("idB"), x.Field<string>("idC") }.Distinct(),
                (x, a) => new
                {
                    id = a,
                    bringanotherfieldifneeded = x.Field<string>("Other Field")
                }
            ).Join(dtProducts.AsEnumerable(),
                descriptions => descriptions.id,
                products => products.Field<string>("Product ID"),
                (descriptions, products) => new { DESCRIPTIONS = descriptions, PRODUCTS = products });

CodePudding user response：

You could improve your chain of Join methods by flattening the intermediate results, but that wouldn't really lessen the redundancy. Sometimes it is better to use a cartesian product and than filter the results.

Using query syntax, you could have:

var q = from desc in dtDescriptions.AsEnumerable()
        from prod in dtProducts.AsEnumerable()
        where new[] {
                    desc.Field<string>("idA"),
                    desc.Field<string>("idB"),
                    desc.Field<string>("idC")
                }
                .Contains(prod.Field<string>("Product ID"))
        select new { desc, prod };

Or using fluent syntax:

var ql = dtDescriptions.AsEnumerable()
            .SelectMany(desc => dtProducts.AsEnumerable().Select(prod => new { desc, prod }))
            .Where(dp => new[] {
                       dp.desc.Field<string>("idA"),
                       dp.desc.Field<string>("idB"),
                       dp.desc.Field<string>("idC")
                   }
                   .Contains(dp.prod.Field<string>("Product ID")));

NOTE: If you want to keep track of which field matched, that would be a little more involved.