比较两个 LINQ 查询并获取 datagridview 中的差异 （C#）

以下可能是一种可能的方法（不是声称这是最好的方法）。

假设您的类是这样实现的：

public class Item
{
    public int Id { get; set; }
    public int Weight { get; set; }
    public int Phase { get; set; }
}

您可以实现一个比较类，该类将要比较的前一项（可能是）和当前项（可能是）进行比较：nullnull

public class ItemComparison
{
    public int Id { get; init; }
    
    public string Weight => GetComparisonString(_weightPrevious, _weightCurrent);
    public string Phase => GetComparisonString(_phasePrevious, _phaseCurrent);
    
    public ItemComparison(Item previousItem, Item currentItem)
    {
        // TODO Error handling: previousItem and currentItem may
        //  - both be null
        //  - both be not null and have different Id values
        
        Id = (previousItem ?? currentItem).Id;
        
        _weightPrevious = previousItem?.Weight;
        _weightCurrent = currentItem?.Weight;
        _phasePrevious = previousItem?.Phase;
        _phaseCurrent = currentItem?.Phase;
    }
    
    private int? _weightPrevious;
    private int? _weightCurrent;
    private int? _phasePrevious;
    private int? _phaseCurrent;
    
    private string GetComparisonString(int? previousValue, int? currentValue)
    {
        if (previousValue == currentValue)
        {
            return "not changed";
        }
        
        return $"{GetStringOrUnknown(previousValue)} --> {GetStringOrUnknown(currentValue)}"; 
    }
    
    private string GetStringOrUnknown(int? value)
    {
        return value.HasValue ? value.ToString() : "?";
    }
}

并按如下方式计算更新的项目、删除的项目和添加的项目：

//using System.Collections.Generic;
//using System.Linq;

var updatedItems = queryResult1
    .Join(queryResult2,
        previous => previous.Id,
        current => current.Id,
        (previous, current) => new ItemComparison(previous, current));

var removedItems = queryResult1
    .ExceptBy(queryResult2.Select(qr2 => qr2.Id), qr1 => qr1.Id)
    .Select(removed => new ItemComparison(removed, null));

var addedItems = queryResult2
    .ExceptBy(queryResult1.Select(qr1 => qr1.Id), qr2 => qr2.Id)
    .Select(added => new ItemComparison(null, added));

最后，可以将项目比较串联起来以产生最终的比较结果：

List<ItemComparison> comparisonResult = updatedItems
    .Concat(removedItems)
    .Concat(addedItems)
    .OrderBy(item => item.Id)
    .ToList();

comparisonResult然后可用于填充您的 .DataGridView

示例小提琴在这里。

假设 ID 永远不会更改，我的建议是在 ID 上做一个完整的外部联接

• 添加的元素是不在查询 1 中但位于查询 2 中的元素
• 删除的元素是查询 1 中但不再位于查询 2 中的元素
• 更改的元素是同时存在于查询 1 和 2 中的元素，但值不相等。

唉，类 Enumerable 没有用于完全外部连接的扩展方法。幸运的是，创建一个相当容易。如果您不熟悉扩展方法，请考虑阅读扩展方法揭秘（使用方法语法而不是查询语法的另一个很好的理由）

FullOuter加入

public static IEnumerable<TResult> FullOuterJoin<T1, T2, TKey, TResult>(
    this IEnumerable<T1> sequence1,
    IEnumerable<T2> sequence2,
    Func<T1, TKey> joinKey1Selector,
    Func<T2, TKey> joinKey2Selector,
    Func<T1, T2, TKey, TResult> resultSelector,
    IEqualityComparer<TKey> keyComparer)
{
    // TODO: implement
}

就像许多 LINQ 方法一样，您可以通过编写多个重载来帮助方法的用户，例如，一个没有 ：keyComparer

public static IEnumerable<TResult> FullOuterJoin<T1, T2, TKey, TResult>(
    this IEnumerable<T1> sequence1,
    IEnumerable<T2> sequence2,
    Func<T1, TKey> joinKey1Selector,
    Func<T2, TKey> joinKey2Selector,
    Func<T1, T2, Tkey, TResult> resultSelector)
{
    return FullOuterJoin(sequence1, sequence2,
                         joinKey1Selector, joinKey2Selector,
                         resultSelector, null);
}

在您的问题中使用 FullOuterJoin

用法可能如下：

class QueryResult
{
    public int Id {get; set;}
    public decimal Weight {get; set;}           // maybe other type
    public int Phase {get; set;}
}

IEnumerable<QueryResult> query1 = ...
IEnumerable<QueryResult> query2 = ...

// full outer join query1 and query 2 on Id:
var fullOuterJoin = query1.FullOuterJoin(query2,

    queryResult => queryResult.Id,    // from every element from query1 take the Id
    queryResult => queryResult.Id,    // from every element from query2 take the Id

    // parameter resultSelector: from every T1 and its matching T2 make one new
    // note: T1 or T2 can be null (but not both)
    (x, y, key) => new
    {
        Id = key,
        Added = T1 == null,
        Removed = T2 == null,
        Changed = T1 != T2,

        Original = T1,
        Current = T2,
    };

Detect Changes

I think it would be neater to change the properties Added / Removed / Changed into an enum. Create a method for this:

enum ChangeState {Unchanged, Added, Removed, Changed};

ChangeState DetectChange<T>(T x, T y)
{
    return DetectChange(x, y, null); // call the overload with comparer
}

ChangeState DetectChange<T>(T x, T y, IEqualityComparer<T> comparer)
{
    if (comparer == null) comparer = EqualityComparer<T>.Default;

    if (comparer.Equals(x, y)) return ChangeState.Unchanged;
    if (x == null) return ChangeState.Added;   // because y not null
    if (y == null) return ChangeState.Removed; // because x not null
    return ChangeState.Changed;
}

Parameter resultSelector will be like this:

(x, y, key) => new
{
    Id = key,
    ChangeState = DetectChange(x, y),
    ...

Implementation of Full Outer Join

The implementation is fairly simple:

• Create Dictionaries for sequence1 and sequence2, using TKey as key.
• Get all distinct Keys from both LookupTables
• For every Key, get the element from lookup X and lookup Y. One of these two might be null.
• Use resultSelector to calculate the result
• Yield return the result

.

public static IEnumerable<TResult> FullOuterJoin<Tx, Ty, TKey, TResult>(
    this IEnumerable<Tx> sequenceX,
    IEnumerable<Ty> sequenceY,
    Func<Tx, TKey> xKeySelector,
    Func<Ty, TKey> yKeySelector,
    Func<Tx, Ty, TKey, TResult> resultSelector,
    IEqualityComparer<TKey> keyComparer)
{
    // TODO: throw exception if any of sequenceX, sequenceY,
    // KeySelectors or resultSelector equal null

    // if keyComparer equals null, use default comparison technique
    if (keyComparer == null) keyComparer = EqualityComparer<TKey>.Default;

    // create two lookupTables:
    IDictionary<TKey, Tx> dictX = sequence1.ToDictionary(x => joinKey1Selector(x), keyComparer);
    IDictionary<TKey, Ty> dictY = sequence1.ToDictionary(y => joinKey2Selector(y), keyComparer);

    // get all used Tkey:
    IEnumerable<TKey> keysX= dictX.Select(x => x.Key);
    IEnumerable<TKey> keysY= dictY.Select(y => y.Key);
    IEnumerable<TKey> allUsedKeys = keysX.Union(keyY, keyComparer);

    // for every used key, get the x and the y and return a result
    foreach(TKey key in allUsedKeys)
    {
        dictX.TryGetValue(key, out Tx foundX);  // null if not found
        dictY.TryGetValue(key, out Ty foundY);
        TResult result = resultSelector(foundX, foundY, key);
        yield return result;
    }
}

Problem if IQueryable

Problem: the simple extension method for full outer join does not work with IQueryable, only with IEnumerable.

If you really want that your Database Management System does the full outer join as Queryable, before returning the data to your local process, you'll have to create an extension method that has as input. Maybe this article about Left Outer Join as IQueryable is a good starting point.IQueryable<...>

If I look at the length of the proposed Left Outer Join, I think that it won't be very efficient. Consider to add a method that does the full outer join as SQL statement to your DbContext.