Linq left outer join with custom comparator

Question

I am trying to make a left outer join query that also has a custom comparator.

I have the following lists:

List<ColumnInformation> list1;
List<ColumnInformation> list2;

These hold information about an SQL column (datatype, name, table, etc). I have overrided Equals for the class, and made a operator == and operator !=.

I understand how to make a left outer join:

var leftOuterJoin = from l1 in list1
                    join l2 in list2 on l1.objectID equals l2.objectID into temp
                    from l2 in temp.DefaultIfEmpty(new { l1.ID, Name = default(string) })
                    select new
                    {
                        l1.ID,
                        ColumnName1 = l1.Name,
                        ColumnName2 = l2.Name,
                    };

And I understand how to make and use a custom IEqualityComparer:

public class ColumnComparer : IEqualityComparer<ColumnInformation>
{
    public bool Equals(ColumnInformation x, ColumnInformation y)
    {
        return x == y; //this uses my defined == operator
    }

    public int GetHashCode(ColumnInformation obj)
    {
        return 1; //forcing the join to use Equals, just trust me on this
    }
}

ColumnComparer cc = new ColumnComparer();
var joinedList = list1.Join(list2,
    x => x,
    y => y,
    (x, y) => new {x, y},
    cc);

My question is: How can I do both a left outer join and use my comparator at the same time?

As far as I know, the query syntax does not have a keyword for a comparator, and the extension method does not have anything for the into keyword.

I do not care if the result is in query syntax or extension methods.

You can override `ColumnInformations` Equals and GetHashCode as implemented in ColumnComparer. — Hamlet Hakobyan, Sep 11 '13 at 16:10
@HamletHakobyan, I have overridden the `Equals` in `ColumnInformation`. Right now my code does not touch it. What benefit would this do (also for the GetHashCode)? — gunr2171, Sep 11 '13 at 16:11
I have to say that **you can do all the things you want in LINQ** with extension methods but **you can't do some things in LINQ with the query syntax**. In fact the equivalent of `join into` in `extension methods` is `GroupJoin`. — King King, Sep 11 '13 at 16:12
@KingKing, `GroupJoin`, would not have guess that from the name. I would be very happy if you could provide an example. — gunr2171, Sep 11 '13 at 16:14
Sorry for confusion. Try implement [IEquatable](http://msdn.microsoft.com/en-us/library/ms131187.aspx) in `ColumnInformation`. — Hamlet Hakobyan, Sep 11 '13 at 16:27

score 5 · Accepted Answer · edited Feb 02 '17 at 11:00

5

The way you do that is with a GroupJoin and SelectMany (to flatten the results):

ColumnComparer cc = new ColumnComparer();
var joinedList = list1
    .GroupJoin(list2,
        x => x,
        y => y,
        (x, g) => new {x, g},
        cc)
    .SelectMany(
        z => z.g.DefaultIfEmpty(),
        (z, b) => new  { x = z.x, y = b } 
    );

edited Feb 02 '17 at 11:00

mikeagg

712
3
17

answered Sep 11 '13 at 16:25

D Stanley

149,601
11
178
240

The OP said 'My question is: How can I do both a left outer join and use my comparator at the same time?'. I wonder how this answer is a response to the actual need of the OP !? – Alireza Sep 11 '13 at 19:09
1

@Alireza, this does a left outer join (using the `DefaultIfEmpty()`), and uses my comparer (`cc`). This is a correct answer. – gunr2171 Sep 11 '13 at 19:11
@gunr2171 Oops! Sorry, I didn't notice **'cc'**. Thanks for clarification. – Alireza Sep 11 '13 at 19:13

score 0 · Answer 2 · answered Sep 11 '13 at 16:22

0

You can take advantage of Sub-Query in combination with Where:

var leftOuterJoin = from l1 in list1
                    select new 
                   {
                      jointList = (from l2 in list2 
                                   where l2.objectID == l1.ObjectID // <-- and you can do any other comparing logic in where clause
                                   select l2).DefaultIfEmpty(),                        
                      l1id = l1.ID,
                      ColumnName1 = l1.Name,
                      ColumnName2 = l2.Name
};

answered Sep 11 '13 at 16:22

Alireza

10,237
6
43
59

In the end, the `where` clause would be `l2 == l1`. I have my comparing logic in that operator method. – gunr2171 Sep 11 '13 at 16:24
@gunr2171 You asked for how to involve your customized comparator to form a Join query, right? You can call whatever method returning a bool to feed the 'Where' clause. Doesn't this satisfy you? – Alireza Sep 11 '13 at 19:07
Don't get me wrong, your answer was fine, and I was only adding a bit of clarification, but it was not wrong. I just happened to go with the other answer because it made sense for me. – gunr2171 Sep 11 '13 at 19:09
@gunr2171 Ok. Thanks for clarification on the accepted answer. I didn't completely get that. Now I know it :D – Alireza Sep 11 '13 at 19:15

Linq left outer join with custom comparator

2 Answers2