代码之家 › 专栏 › 技术社区 › Alex

C#Linq重复值上的完全外部联接

outer-join linq c#

Alex · 技术社区 · 7 年前

我有两个IQueryable集合有这种类型

public class Property  
{  
   public string Name {get; set;}  
}

集合1,具有以下名称值:

A  
A  
A  
B

集合2,具有以下名称值:

A  
B  
B

我想得到的是第三个集合,其中集合1和集合2的名称值匹配,如果不匹配,则 null (empty) ,如下所示:

Result Collection:  

A     A
A     null  
A     null  
B     B  
null  B

如何使用C#,LINQ实现这一点?

5 回复 | 直到 7 年前

Aleks Andreev Md. Suman Kabir 7 年前

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

namespace Testing
{
    public class Property
    {
        public string Name { get; set; }

        public override bool Equals(object obj)
        {
            var item = obj as Property;

            if (item == null)
            {
                return false;
            }
            return item.Name == Name;
        }

        public override int GetHashCode()
        {
            return Name.GetHashCode();
        }
    }

    public class JoinedProperty
    {
        public Property Name1 { get; set; }
        public Property Name2 { get; set; }

        public override string ToString()
        {
            return (Name1 == null ? "" : Name1.Name)
                + (Name2 == null ? "" : Name2.Name);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var list1 = new List<Property>
            {
                new Property{ Name = "A" },
                new Property{ Name = "A" },
                new Property{ Name = "A" },
                new Property{ Name = "B" }
            };

            var list2 = new List<Property>
            {
                new Property{ Name = "A" },
                new Property{ Name = "B" },
                new Property{ Name = "B" }
            };

            var allLetters = list1.Union(list2).Distinct().ToList();

            var result = new List<JoinedProperty>();

            foreach (var letter in allLetters)
            {
                var list1Count = list1.Count(l => l.Name == letter.Name);
                var list2Count = list2.Count(l => l.Name == letter.Name);

                var matchCount = Math.Min(list1Count, list2Count);

                addValuesToResult(result, letter, letter, matchCount);

                var difference = list1Count - list2Count;

                if(difference > 0)
                {
                    addValuesToResult(result, letter, null, difference);                   
                }
                else
                {
                    difference = difference * -1;
                    addValuesToResult(result,null, letter, difference);                   
                }
            }
            foreach(var res in result)
            {
                Console.WriteLine(res.ToString());
            }
            Console.ReadLine();                
        }

        private static void addValuesToResult(List<JoinedProperty> result, Property letter1, Property letter2, int count)
        {
            for (int i = 0; i < count; i++)
            {
                result.Add(new JoinedProperty
                {
                    Name1 = letter1,
                    Name2 = letter2
                });
            }
        }
    }
}

运行此命令,就可以得到结果

AA
A
A
BB
B

结果列表的内容就是你想要的。

编辑:已更新我的答案以使用指定的属性。

Dave Barnett 7 年前

似乎对这个问题很感兴趣,所以我试图提出一个更普遍的解决方案。我从这个链接中获得了灵感 https://www.codeproject.com/Articles/488643/LinQ-Extended-Joins .

我创建了一个fullouterjoin扩展方法,它可以满足op的要求。但不确定fullouterjoin是否是正确的名称。

我用我的扩展方法来解决ops问题。

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


namespace Testing
{


    public class Property
    {
        public string Name { get; set; }
    }

    public class JoinedProperty
    {
        public Property Name1 { get; set; }
        public Property Name2 { get; set; }

        public override string ToString()
        {
            return (Name1 == null ? "" : Name1.Name)
                + (Name2 == null ? "" : Name2.Name);
        }  
    }

    class Program
    {
        static void Main(string[] args)
        {
            var list1 = new List<Property>
        {
            new Property{ Name = "A" },
            new Property{ Name = "A" },
            new Property{ Name = "A" },
            new Property{ Name = "B" }
        };

            var list2 = new List<Property>
        {
            new Property{ Name = "A" },
            new Property{ Name = "B" },
            new Property{ Name = "B" }
        };



            var result = list1.FullOuterJoin(
                list2,
                p1 => p1.Name,
                p2 => p2.Name,
                (p1, p2) => new JoinedProperty
                {
                    Name1 = p1,
                    Name2 = p2
                }).ToList();


            foreach (var res in result)
            {
                Console.WriteLine(res.ToString());
            }
            Console.ReadLine();

        }

    }

    public static class MyExtensions
    {



        public static IEnumerable<TResult>
            FullOuterJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
                                IEnumerable<TInner> inner,
                                Func<TSource, TKey> pk,
                                Func<TInner, TKey> fk,
                                Func<TSource, TInner, TResult> result)
            where TSource : class where TInner : class
        {

            var fullList = source.Select(s => new Tuple<TSource, TInner>(s, null))
                .Concat(inner.Select(i => new Tuple<TSource, TInner>(null, i)));


            var joinedList = new List<Tuple<TSource, TInner>>();

            foreach (var item in fullList)
            {
                var matchingItem = joinedList.FirstOrDefault
                    (
                        i => matches(i, item, pk, fk)
                    );

                if(matchingItem != null)
                {
                    joinedList.Remove(matchingItem);
                    joinedList.Add(combinedMatchingItems(item, matchingItem));
                }
                else
                {
                    joinedList.Add(item);
                }
            }
            return joinedList.Select(jl => result(jl.Item1, jl.Item2)).ToList();

        }

        private static Tuple<TSource, TInner> combinedMatchingItems<TSource, TInner>(Tuple<TSource, TInner> item1, Tuple<TSource, TInner> item2)
            where TSource : class
            where TInner : class
        {
            if(item1.Item1 == null && item2.Item2 == null && item1.Item2 != null && item2.Item1 !=null)
            {
                return new Tuple<TSource, TInner>(item2.Item1, item1.Item2);
            }

            if(item1.Item2 == null && item2.Item1 == null && item1.Item1 != null && item2.Item2 != null)
            {
                return new Tuple<TSource, TInner>(item1.Item1, item2.Item2);
            }

            throw new InvalidOperationException("2 items cannot be combined");
        }

        public static bool matches<TSource, TInner, TKey>(Tuple<TSource, TInner> item1, Tuple<TSource, TInner> item2, Func<TSource, TKey> pk, Func<TInner, TKey> fk)
            where TSource : class
            where TInner : class
        {          

            if (item1.Item1 != null && item1.Item2 == null && item2.Item2 != null && item2.Item1 == null && pk(item1.Item1).Equals(fk(item2.Item2)))
            {
                return true;
            }

            if (item1.Item2 != null && item1.Item1 == null && item2.Item1 != null && item2.Item2 == null && fk(item1.Item2).Equals(pk(item2.Item1)))
            {
                return true;
            }

            return false;

        }

    }
}

Emre Kabaoglu 7 年前

我认为,最好的选择就是使用 loop ;

        var listA = new List<Property>
        {
            new Property{ Name = "A" },
            new Property{ Name = "A" },
            new Property{ Name = "A" },
            new Property{ Name = "B" }
        };
        var listB = new List<Property>
        {
            new Property{ Name = "A" },
            new Property{ Name = "B" },
            new Property{ Name = "B" }
        };
        var joinedList = new List<JoinedProperty>();
        for (int i = 0; i < listA.Count; i++)
        {
            var property = new JoinedProperty
            {
                AName = listA[i].Name,
                BName = null
            };
            if (listB.Count < i + 1)
            {
                continue;
            }
            if (listA[i].Name == listB[i].Name)
            {
                property.BName = listA[i].Name;
            }
            joinedList.Add(property);
        }
        for (int i = 0; i < listB.Count; i++)
        {
            var property = new JoinedProperty
            {
                AName = null,
                BName = listB[i].Name
            };
            if (listA.Count < i + 1)
            {
                continue;
            }
            if (listB[i].Name == listA[i].Name)
            {
                property.AName = listB[i].Name;
            }
            joinedList.Add(property);
        }

        public class JoinedProperty
        {
             public string AName { get; set; }
             public string BName { get; set; }
        }

此外,我认为,您的输出示例缺少一个元素;

null B

输出

A     A
A     null  
A     null  
B     B  
null  B
null  B

JohnyL 7 年前

public class Property
{
    public string Name { get; set; }
}

var list1 = new List<Property>
{
    new Property { Name ="A" },
    new Property { Name ="A" },
    new Property { Name ="A" },
    new Property { Name ="B" }
};

var list2 = new List<Property>
{
    new Property { Name ="A" },
    new Property { Name ="B" },
    new Property { Name ="B" }
};

var r = new List<string>();
int x1 = 0, x2 = 0;
int count1 = list1.Count, count2 = list2.Count;

while (true)
{
    if (x1 == count1 && x2 == count2) break;

    if (x1 < count1 && x2 == count2)
    {
        r.Add($"{list1[x1].Name}\tNULL");
        ++x1;
    }
    else if (x1 == count1 && x2 < count2)
    {
        r.Add($"NULL\t{list2[x2].Name}");
        ++x2;
    }
    else
    {
        if (list1[x1].Name == list2[x2].Name)
        {
            r.Add($"{list1[x1].Name}\t{list2[x2].Name}");
            ++x1; ++x2;
        }
        else
        {
            r.Add($"{list1[x1].Name}\tNULL");
            ++x1;
        }
    }
}

解释

这个想法是在管理列表中的职位,即我们是否应该提升职位。查找完所有位置后,循环即退出。

Harald Coppoolse 7 年前

您需要一个LINQ函数,其实没有,但您可以对其进行扩展,这样它就可以用于任何两个您想要使用此技巧的序列。

您所要做的就是编写一个IEnumerable的扩展函数,与所有其他LINQ函数类似。

看见 Extension Methods Demystified

public static class MyEnumerableExtensions
{
    public IEnumerable<System.Tuple<T, T>> EqualityZip<T>(this IEnumerable<T> sourceA,
        IEnumerable<T> sourceB)
    {
        // TODO: check for parameters null

        var enumeratorA = sourceA.GetEnumerator();
        var enumeratorB = sourceB.GetEnumerator();

        // enumerate as long as we have elements in A and in B:
        bool aAvailable = enumeratorA.MoveNext();
        bool bAvailable = enumeratorB.MoveNext();
        while (aAvailable && bAvailable)
        {   // we have an A element and a B element
            T a = enumeratorA.Current;
            T b = enumeratorB.Current;

            // compare the two elements:
            if (a == b)
            {   // equal: return tuple (a, b)
                yield return Tuple.Create(a, b)
            }
            else
            {   // not equal, return (a, null)
                yield return Tuple.Create(a, (T)null)
            }

            // move to the next element
            aAvailable = enumeratorA.MoveNext();
            bAvailable = enumeratorB.MoveNext();
        }
        // now either we are out of A or out of B

        while (aAvailable)
        {   // we still have A but no B, return (A, null)
            T A = enumeratorA.Current;
            yield return Tuple.Create(A, (T)null);
            aAvailable = enumeratorA.MoveNext();
        }
        while (bAvailable)
        {   // we don't have A, but there are still B, return (null, B)
            T B = enumeratorB.Current;
            yield return Tuple.Create((T)null, B);
            bAvailable = enumeratorB.MoveNext();
        }

        // if there are still A elements without B element: return (a, null)
        while (enumaratorA.Nex
    }
}

用法:

var sequenceA = ...
var sequenceB = ...
var result = sequenceA.EqualityZip(sequenceB);

TODO:使功能更加完善,可以比较两个不同的类,键选择器选择A和B的比较键,以及IEqualityCompare:

public static IEnumerable<Tuple<TA, TB> EqualityZip<TA, TB, TKey>(
    this IEnumerable<TA> sourceA,   // the first sequence
    this IEnumerable<TB> sourceB,   // the second sequence
    Func<TA, TKey> keySelectorA,    // the property of sourceA to take
    Func<TB, TKey> keySelectorB,    // the property of sourceB to take
    IEqualityComparer<TKey> comparer)
{
    // TODO: ArgumentNullException if arguments null
    if (comparer==null) comparer = EqualityCompare<TKey>.Default;

     var enumeratorA = sourceA.GetEnumerator();
        var enumeratorB = sourceB.GetEnumerator();

        // enumerate as long as we have elements in A and in B:
        bool aAvailable = enumeratorA.MoveNext();
        bool bAvailable = enumeratorB.MoveNext();
        while (aAvailable && bAvailable)
        {   // we have an A element and a B element
            TKey keyA = keySelectorA(enumeratorA.Current);
            TKey keyB = keySelectorB(enumeratorB.Current);
            if (comparer.Equals(keyA, keyB)
            {
                yield return Tuple.Create(Ta, Tb)
            }
            else

等