使用 Linq 确定缺少的记录组合

Question

让我用一个例子开始解释。

var vProducts = new[] {
    new { Product = "A", Location ="Y", Month = "January", Demand = 50 },
    new { Product = "A", Location ="Y", Month = "February", Demand = 100 },
    new { Product = "A", Location ="Y", Month = "March", Demand = 20 },
    new { Product = "A", Location ="Y", Month = "June", Demand = 10 }
};
var vPeriods = new[] {
    new { Priority = 1, Month = "January" },
    new { Priority  = 2, Month = "February" },
    new { Priority  = 3, Month = "March" },
    new { Priority  = 4, Month = "April" },
    new { Priority  = 5, Month = "May" },
    new { Priority  = 6, Month = "June" }
};
var vAll = from p in vProducts
       from t in vPeriods
       select new
           {
             Product = p.Product,
             Location = p.Location,
             Period = t.Priority,
             PeriodName = t.Month,
             Demand = p.Demand
           };

上述查询将创建"产品和期间"的所有组合。但是，我需要获取所有产品的列表以及没有匹配月份的产品，如下所示。

例

Product      Location      Priority        Month    Demand
  A              Y            1           January     50
  A              Y            2          February    100
  A              Y            3           March       20
  A              Y            4           April       null
  A              Y            5           May         null
  A              Y            6           June        10

感谢您的任何评论。

Answer 1

你想做一个左外连接，它会像这样：

var res = from period in vPeriods
                  join product in vProducts on period.Month equals product.Month into bla
                  from p in bla.DefaultIfEmpty()
                  select new { period.Month, period.Priority, Product = p == null ? null : p.Product, Demand = p == null ? -1 : p.Demand };
  foreach (var a in res)
            {
                Console.WriteLine(string.Format("{0} {1} {2}", a.Product, a.Month, a.Demand));
            }

当然，没有匹配月份的产品没有位置等（如您在示例中所述）