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

本文是小编为大家收集整理的关于LINQ FULL OUT OUT JOIN在两个对象上的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有两个称为CountryMobility的对象,我相信我需要与完整的外部连接结合使用.我该如何使用linq?

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

我尝试了以下LINQ语句,但无法弄清楚正确的语法.我目前正在遇到语法错误 temp.defaultifempty(new {first.id,Inbound = 0,outbound = 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); 

推荐答案

您的最新信息之后.在我看来,您可以做一些简单的事情.即,您随后按国家代码对UNION ALL进行分组. 可以使用Concat方法创建UNION ALL.

下面的示例对我有用(在内存集合中使用).查询显示在Run方法中.

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);
    }
}

其他推荐答案

您的Defaultifempty正在引起错误,因为您正在定义匿名对象,但是您正在选择"选择语句"中创建stronly键入对象.他们都必须是相同类型的.

因此定义这样的默认对象:

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

之后,在您的defaultifempty()方法中使用它:

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

最后但并非最不重要的一点是,您必须做一个组件才能获得理想的结果:

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

输出: 在此处输入图像说明

完整的演示代码:

http://share.linqpad.net.net/u46gar.linq

其他推荐答案

您可以这样做

 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)
                });

本文地址:https://www.itbaoku.cn/post/1556797.html

问题描述

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: enter image description here

Full Demo Code:

http://share.linqpad.net/u46gar.linq

其他推荐答案

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)
                });