CODEWITHSUNDEEP
c#linq
Skalis
Skalis

Reputation: 197

SelectMany() after a GroupJoin()

Basically, what I want to do is left outer join two tables and present them in a flat result. For simplicity, my two tables look like this:

tot["nameA", "nameB", "nameC"]
critItg["nameA", "nameB"]

I want the result after the left outer join to look like this:

leftName, rightName
"nameA", "nameA"
"nameB", "nameB"
"nameC", empty/null

I have managed to perform a left outer join by:

var res = tot.GroupJoin
                (
                    critITG,
                    left => left.totName,
                    right => right.critITGName,
                    (left, right) => new
                    {
                        tot = left,
                        critITG = right.FirstOrDefault()
                    }
                );

However, the result is grouped like this:

{ tot = { totName = "nameA" }, { critITG = "nameA"} }
{ tot = { totName = "nameB" }, { critITG = "nameB"} }
{ tot = { totName = "nameC" }, { critITG = null} }

I would like the result to look like something more like this:

{ totName = "nameA", critITG = "nameA" }

I have read that the solution for flattening my left outer join result is SelectMany(), but I am having trouble implementing it on the above result set. The following result set is "object reference not set to an instance of an object:

var res = tot.GroupJoin
                (
                    critITG,
                    left => left.totName,
                    right => right.critITGName,
                    (left, right) => new
                    {
                        tot = left,
                        critITG = right.FirstOrDefault()
                    }
                )
                .SelectMany
                (
                    right => right.critITG.critITGName.DefaultIfEmpty(),
                    (left, right) => 
                        new 
                        { 
                            leftName = left.tot.totName, 
                            rightName = right 
                        }
                );

Appreciate your help!

Upvotes: 2

Views: 10230

Answers (4)

Maz H
Maz H

Reputation: 151

Hi Similar to Lee's example but somewhat different, here I have used a select statement in the final anonymous type to be returned:

var res = tot.GroupJoin(critItg,
            left => left.Name,
            right => right.Name,
            (left, right) => new
            {
                tot = left.Name,
                critItg = right.Select(x => x.Name).FirstOrDefault()
            });

Upvotes: 1

Yumei De Armas
Yumei De Armas

Reputation: 396

Not sure if that's what you need... but have you tried selecting the Name for each field?

var res = tot.GroupJoin
            (
                critITG,
                left => left.totName,
                right => right.critITGName,
                (left, right) => new
                {
                    tot = left.totName,
                    critITG = right.FirstOrDefault() == null ? null : right.critITGName
                }
            );

EDIT: I also agree with @Jim Wooley, sometimes it's easier to not use the lambda expressions. Just a note, remember you will need to add your totName and critITGName to his code..

  var res = from totElement in tot
  join critItgItem in critItgon totElement.Name equals critItgItem.Name into joined
  from row in joined.DefaultIfEmpty()
  select new {totName = tot.Name, critItg = (row == null ? String.Empty : row.Name) };

Upvotes: 3

Lee
Lee

Reputation: 144206

You don't need SelectMany here. You could use Select, but you can just select the data you need in the delegate passed to GroupJoin:

var res = tot.GroupJoin
                (
                    critITG,
                    left => left.totName,
                    right => right.critITGName,
                    (left, right) => {
                        var mr = right.FirstOrDefault();
                        return new
                        {
                            totName = left.totName,
                            critITG = mr == null ? null : mr.critITGName
                        };
                    }
                );

Upvotes: 1

Jim Wooley
Jim Wooley

Reputation: 10418

It looks like you are getting caught up in technical details of the lambda constructs which may lead to reduced code maintainability over time. Often I find the query syntax to be simpler and more maintainable than lambda syntax for joins. Consider the following:

var tots = new string[] {"nameA", "nameB", "nameC"};
var critItgs = new string[] {"nameA", "nameB"};

var query = from tot in tots
      join critItg in critItgs on tot equals critItg into joined
      from row in joined.DefaultIfEmpty()
      select new {totName = tot, critItg = row};
query.Dump();

Upvotes: 3

Related Questions