# LINQ FULL OUT OUT JOIN在两个对象上[英] Linq Full Outer Join on Two Objects ### 问题描述

```public class CountryMobility
{
public string countryCode { get; set; }
public int inbound { get; set; }
public int outbound { get; set; }
}
```

```inboundStudents:
countryCode | inbound | outbound
EG |    2    |     0
CA |    3    |     0
CH |    5    |     0

outboundStudents:
countryCode | inbound | outbound
PE |    0    |     1
CA |    0    |     4
CH |    0    |     5

-
-
-
-
V

combinedStudents:
countryCode | inbound | outbound
PE |    0    |     1
CA |    3    |     4
CH |    5    |     5
EG |    2    |     0
```

```var leftOuterJoin =
from first in inboundActivities
join last in outboundActivities
on first.countryCode equals last.countryCode
into temp
from last in temp.DefaultIfEmpty
(new { first.countryCode, inbound = 0, outbound=0 })
select new CountryMobility
{
countryCode = first.countryCode,
inbound = first.inbound,
outbound = last.outbound,
};
var rightOuterJoin =
from last in outboundActivities
join first in inboundActivities
on last.countryCode equals first.countryCode
into temp
from first in temp.DefaultIfEmpty
(new { last.countryCode, inbound = 0, outbound = 0 })
select new CountryMobility
{
countryCode = last.countryCode,
inbound = first.inbound,
outbound = last.outbound,
};

var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);
```

## 推荐答案

```public class CountryMobility
{
public string countryCode { get; set; }
public int inbound { get; set; }
public int outbound { get; set; }
}

public static class JoinedMobilityQuery
{
static CountryMobility[] inbound = {
new CountryMobility() { countryCode = "EG", inbound = 2 },
new CountryMobility() { countryCode = "CA", inbound = 3 },
new CountryMobility() { countryCode = "CH", inbound = 5 },
};
static CountryMobility[] outbound = {
new CountryMobility() { countryCode = "PE", outbound = 1 },
new CountryMobility() { countryCode = "CA", outbound = 4 },
new CountryMobility() { countryCode = "CH", outbound = 6 },
};

static IQueryable<CountryMobility> Inbound()
{
return inbound.AsQueryable();
}

static IQueryable<CountryMobility> Outbound()
{
return outbound.AsQueryable();
}

public static void Run()
{
var transfers = from t in Inbound().Concat(Outbound())
group t by t.countryCode into g
select new CountryMobility() {
countryCode = g.Key,
inbound = g.Sum(x => x.inbound),
outbound = g.Sum(x => x.outbound),
};
foreach (var transfer in transfers)
Console.WriteLine("{0}\t{1}\t{2}", transfer.countryCode, transfer.inbound, transfer.outbound);
}
}
```

## 其他推荐答案

```var defaultActivity = new CountryMobility() { countryCode = String.Empty, outbound = 0, inbound = 0 };
```

```from last in temp.DefaultIfEmpty(defaultActivity)
select new CountryMobility
{
//...
};
```

```var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin)
.GroupBy (oj => oj.countryCode)
.Select (oj => oj.FirstOrDefault());
```

## 其他推荐答案

``` List<CountryMobility> inboundStudents = new List<CountryMobility>{
new CountryMobility { countryCode="EG", inbound=2, outbound = 0},
new CountryMobility { countryCode="CA", inbound=3, outbound = 0},
new CountryMobility { countryCode="CH", inbound=5, outbound = 0}};

List<CountryMobility> outboundStudents = new List<CountryMobility>{
new CountryMobility { countryCode="PE", inbound=0, outbound = 1},
new CountryMobility { countryCode="CA", inbound=0, outbound = 4},
new CountryMobility { countryCode="CH", inbound=0, outbound = 5}};

var joinedList = inboundStudents.Concat(outboundStudents).GroupBy(item => new { item.countryCode});
var result = joinedList.Select(x => new
{
countryCode = x.Key.countryCode,
inbound = x.Sum(i => i.inbound),
outbound = x.Sum(i => i.outbound)
});
```

### 问题描述

I have two objects called CountryMobility that I believe I need to combine with a full outer join. How can I do this using linq?

```public class CountryMobility
{
public string countryCode { get; set; }
public int inbound { get; set; }
public int outbound { get; set; }
}
```

I want to combine two of these objects like so:

```inboundStudents:
countryCode | inbound | outbound
EG |    2    |     0
CA |    3    |     0
CH |    5    |     0

outboundStudents:
countryCode | inbound | outbound
PE |    0    |     1
CA |    0    |     4
CH |    0    |     5

-
-
-
-
V

combinedStudents:
countryCode | inbound | outbound
PE |    0    |     1
CA |    3    |     4
CH |    5    |     5
EG |    2    |     0
```

I have tried the following linq statements but have not been able to figure out the correct syntax. I am currently getting a syntax error near temp.DefaultIfEmpty(new { first.ID, inbound = 0, outbound=0 }) in both statements.

```var leftOuterJoin =
from first in inboundActivities
join last in outboundActivities
on first.countryCode equals last.countryCode
into temp
from last in temp.DefaultIfEmpty
(new { first.countryCode, inbound = 0, outbound=0 })
select new CountryMobility
{
countryCode = first.countryCode,
inbound = first.inbound,
outbound = last.outbound,
};
var rightOuterJoin =
from last in outboundActivities
join first in inboundActivities
on last.countryCode equals first.countryCode
into temp
from first in temp.DefaultIfEmpty
(new { last.countryCode, inbound = 0, outbound = 0 })
select new CountryMobility
{
countryCode = last.countryCode,
inbound = first.inbound,
outbound = last.outbound,
};

var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);
```

## 推荐答案

After your latest information. It seems to me that you can do something much simpler. Namely a UNION ALL that you subsequently group by country code. A UNION ALL can be created using the Concat method.

The sample below works for me (using in memory collections). The query is shown in the Run method.

```public class CountryMobility
{
public string countryCode { get; set; }
public int inbound { get; set; }
public int outbound { get; set; }
}

public static class JoinedMobilityQuery
{
static CountryMobility[] inbound = {
new CountryMobility() { countryCode = "EG", inbound = 2 },
new CountryMobility() { countryCode = "CA", inbound = 3 },
new CountryMobility() { countryCode = "CH", inbound = 5 },
};
static CountryMobility[] outbound = {
new CountryMobility() { countryCode = "PE", outbound = 1 },
new CountryMobility() { countryCode = "CA", outbound = 4 },
new CountryMobility() { countryCode = "CH", outbound = 6 },
};

static IQueryable<CountryMobility> Inbound()
{
return inbound.AsQueryable();
}

static IQueryable<CountryMobility> Outbound()
{
return outbound.AsQueryable();
}

public static void Run()
{
var transfers = from t in Inbound().Concat(Outbound())
group t by t.countryCode into g
select new CountryMobility() {
countryCode = g.Key,
inbound = g.Sum(x => x.inbound),
outbound = g.Sum(x => x.outbound),
};
foreach (var transfer in transfers)
Console.WriteLine("{0}\t{1}\t{2}", transfer.countryCode, transfer.inbound, transfer.outbound);
}
}
```

## 其他推荐答案

Your DefaultIfEmpty is thropwing an error, because you are defining an anonymous object, but you are creating stronly typed objects in your select statement. They both have to be of the same type.

So define a default object like this:

```var defaultActivity = new CountryMobility() { countryCode = String.Empty, outbound = 0, inbound = 0 };
```

After this, use it in your DefaultIfEmpty() method:

```from last in temp.DefaultIfEmpty(defaultActivity)
select new CountryMobility
{
//...
};
```

last but not least, you have to do a groupby to get the desired results:

```var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin)
.GroupBy (oj => oj.countryCode)
.Select (oj => oj.FirstOrDefault());
```

Output: Full Demo Code:

## 其他推荐答案

You can do like this

``` List<CountryMobility> inboundStudents = new List<CountryMobility>{
new CountryMobility { countryCode="EG", inbound=2, outbound = 0},
new CountryMobility { countryCode="CA", inbound=3, outbound = 0},
new CountryMobility { countryCode="CH", inbound=5, outbound = 0}};

List<CountryMobility> outboundStudents = new List<CountryMobility>{
new CountryMobility { countryCode="PE", inbound=0, outbound = 1},
new CountryMobility { countryCode="CA", inbound=0, outbound = 4},
new CountryMobility { countryCode="CH", inbound=0, outbound = 5}};

var joinedList = inboundStudents.Concat(outboundStudents).GroupBy(item => new { item.countryCode});
var result = joinedList.Select(x => new
{
countryCode = x.Key.countryCode,
inbound = x.Sum(i => i.inbound),
outbound = x.Sum(i => i.outbound)
});
```