我有两个称为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您随后按国家/地区代码分组的一个。阿UNION ALL可以使用被创建Concat方法。
UNION ALL
Concat
下面的示例对我有用(在内存集合中使用)。查询显示在Run方法中。
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); } }