Use DefaultIfEmpty in Dynamic Expressions and Queries in LINQ

Question

I want to use Dynamic Expressions and Queries in LINQ with a DefaultIfEmpty clause to get all of the rows from the two sets that don't match, or don't have a match. The end goal is to get the delta of the join of the two tables.

In table A I have:

Name | Description       | Type
A    | This is something | 1
B    | Something else    | 2
C    | Where have I gone | 1

In table B I have:

Name | Description        | Type
A    | This is something  | 1
B    | Life is wonderful  | 2
D    | What happened to C | 2

I would like to get the results as:

Column      | Table A        | Table B
Name        | B              | B
Description | Something else | Life is wonderful
Type        | 2              | 2
---
Column      | Table A           | Table B
Name        | C                 | null
Description | Where have I gone | null
Type        | 2                 | null
---
Column      | Table A | Table B
Name        | null    | D
Description | null    | What happened to C
Type        | null    | 2

What does your result represent? Three tables? One table with repeating `Column` values? (Then how do you plan to match `Name` with `Description`?) — NetMage, Apr 14 '20 at 21:42

BermudaLamb · Answer 1 · 2020-04-16T12:50:43.733

This answer doesn't use Dynamic Expressions. However, it is the solution that I came up with, that gives me the answer I'm looking for.

    var sql = table.SqlSelect;
    var dataLegacy = GetData(connections.Legacy, sql, table.TableName);
    var dataUpdate = GetData(connections.Update, sql, table.TableName);

    var compare = new List<CompareSet>();
    var jsonColumns = table.Json.ToList() ?? null;
    dataLegacy.AsEnumerable().ToList()
    .ForEach(legacyRow =>
    {
        DataRow updateRow = default(DataRow);
        if (table.KeyColumn.Any())
        {
            var keys = string.Join("|", table.KeyColumn.Select(kc => $"{legacyRow.Field<object>(kc)}"));
            updateRow = dataUpdate.AsEnumerable()
            .FirstOrDefault(y => keys == string.Join("|", table.KeyColumn.Select(kc => $"{y.Field<object>(kc)}")));
        }
        if (updateRow == null || JsonConvert.SerializeObject(legacyRow.ItemArray) != JsonConvert.SerializeObject(updateRow.ItemArray))
            compare.Add(new CompareSet(compare.Count, legacyRow, updateRow, jsonColumns));
    });
    dataUpdate.AsEnumerable().ToList()
    .ForEach(updateRow =>
    {
        DataRow legacyRow = default(DataRow);
        if (table.KeyColumn.Any())
        {
            var keys = string.Join("|", table.KeyColumn.Select(kc => $"{updateRow.Field<object>(kc)}"));
            legacyRow = dataLegacy.AsEnumerable()
            .FirstOrDefault(y => keys == string.Join("|", table.KeyColumn.Select(kc => $"{y.Field<object>(kc)}")));
        }
        if (legacyRow == null)
            compare.Add(new CompareSet(compare.Count, legacyRow, updateRow, jsonColumns));
    });
    if (compare.Any())
    {
        compare.OrderBy(c => c.OrderBy)
        .Dump($"{table.SchemaTable} ( {compare.Count()} / {dataLegacy.Rows.Count} / {dataUpdate.Rows.Count} rows )", TableDumpDepth);
    }
    else
    {
        "No differences found in data".Dump($"{table.SchemaTable} ( 0 / {dataLegacy.Rows.Count} / {dataUpdate.Rows.Count} rows )");
    }

Use DefaultIfEmpty in Dynamic Expressions and Queries in LINQ

1 Answers1