C# Join with dynamic columns

Question

I am using below query to join two tables and it works perfectly fine.

var joins = from filerow in dtfile.AsEnumerable()
            from dbrow in dtdb.AsEnumerable().Where(x =>
                filerow["PRODUCT_ID"] == x["PRODUCT_ID"]
                && filerow["COMPANY"] == x["COMPANY_NAME"]
                && filerow["BRAND"] == x["BRAND_ID"]
                && filerow["LOCATION"] == x["PLACE"]
              )
            select new { filerecord = filerow, db = dbrow };

I want to make column names as dynamic in dictionary and then use this dictionary to get join results.

Dictionary<string, string> dictcolumnMapping = new Dictionary<string, string>();
dictcolumnMapping.Add("PRODUCT_ID", "PRODUCT_ID");
dictcolumnMapping.Add("COMPANY", "COMPANY_NAME");
dictcolumnMapping.Add("BRAND", "BRAND_ID");
dictcolumnMapping.Add("LOCATION", "PLACE");

The reason is that , I want to implement this join for multiple tables and key columns are different for each table.

Answer 1

You can use this extension method. It allows you to add conditions dynamically by mapping dictionary.

public static IQueryable<DataRow> WhereByMapping(this IQueryable<DataRow> source, DataRow parentSource, Dictionary<string, string> dictcolumnMapping)
{
    foreach (var map in dictcolumnMapping)
    {
        source = source.Where(r => parentSource[map.Key] == r[map.Value]);
    }

    return source;
}

Then your query would be like:

Dictionary<string, string> dictcolumnMapping = new Dictionary<string, string>();
dictcolumnMapping.Add("PRODUCT_ID", "PRODUCT_ID");
dictcolumnMapping.Add("COMPANY", "COMPANY_NAME");
dictcolumnMapping.Add("BRAND", "BRAND_ID");
dictcolumnMapping.Add("LOCATION", "PLACE");


var joins = from filerow in dtfile.AsEnumerable().AsQueryable()
            from dbrow in dtdb.AsEnumerable().AsQueryable().WhereByMapping(filerow, dictcolumnMapping)
            select new { filerecord = filerow, db = dbrow };