How to Perform a Full Outer Join in LINQ?

linq -outer outer connection

Question:

How to perform a full -connection between the two objects based on the public key field to ensure that all the records of the two lists are included in the results. Even if they do not have corresponding matching items in another list?

Answer:

1. Custom extension method connected to the whole outside

In order to achieve an outer connection in Linq, we can define a custom expansion method, as shown below:

2. Implement

In the example provided, we have two lists:

public static IEnumerable<TResult> FullOuterJoin<TA, TB, TKey, TResult>(
    this IEnumerable<TA> a,
    IEnumerable<TB> b,
    Func<TA, TKey> selectKeyA,
    Func<TB, TKey> selectKeyB,
    Func<TA, TB, TKey, TResult> projection,
    TA defaultA = default(TA),
    TB defaultB = default(TB),
    IEqualityComparer<TKey> cmp = null)
{
    cmp = cmp ?? EqualityComparer<TKey>.Default;
    var alookup = a.ToLookup(selectKeyA, cmp);
    var blookup = b.ToLookup(selectKeyB, cmp);

    var keys = new HashSet<TKey>(alookup.Select(p => p.Key), cmp);
    keys.UnionWith(blookup.Select(p => p.Key));

    var join = from key in keys
               from xa in alookup[key].DefaultIfEmpty(defaultA)
               from xb in blookup[key].DefaultIfEmpty(defaultB)
               select projection(xa, xb, key);

    return join;
}

and

. To execute the outer connection, we can use the following code:

firstNames 3. Explanation <3> lastNames This code uses a left outside connection, and each record in

is connected to the first match record in

var outerJoin = from first in firstNames
                join last in lastNames
                on first.ID equals last.ID
                into temp
                from last in temp.DefaultIfEmpty()
                select new
                {
                    id = first != null ? first.ID : last.ID,
                    firstname = first != null ? first.Name : string.Empty,
                    surname = last != null ? last.Name : string.Empty
                };

. If the matching item is not found, the connection is connected to the default value.

The statement creates a temporary collection of

matching records, allowing us to apply

to ensure that it contains unsatising records. This actually realizes the left external connection, not a full -connection in the complete sense. To achieve an outer connection, you need to use the customized firstNames expansion method above. lastNames into temp 4. Expecting output lastNames DefaultIfEmpty() The expected output of the outer connection is: FullOuterJoin

5. The defined default value

The extension method provides allows you to specify the default value for unsatisfactory records. In the example, we specify

for the unsatisfactory name, and we specify the negative integer for the non -matching ID.

<code>ID  FirstName  LastName
--  ---------  --------
1   John       Doe
2   Sue        
3             Smith</code>

6. Performance precautions

The running time of the whole outside connection is O (n m), where n and m are the length of the two input lists. This is because string.Empty operation requires O (n) time, and subsequent operations require O (n m) time.

7. LINQ implementation

This implementation of the whole outer connection is currently not part of the Linq standard, but it is recommended to include it in the future version. Standard Linq does not directly support the outer connection and needs to be customized to achieve it. ToLookup()

The above is the detailed content of How to Perform a Full Outer Join in LINQ?. For more information, please follow other related articles on the PHP Chinese website!