时间戳

首页 > 解决方案 > C#：LINQ 对具有不同行数的多个数据表进行分组

c# - C#：LINQ 对具有不同行数的多个数据表进行分组

问题描述

我是使用 LINQ + DataTables 的新手，我被困了一段时间。我已经在互联网上搜索了一些类似的问题和解决方案，但这并不是我想要完成的。

我会尽力描述案例和设置：

在文本中：

我从 SQL 查询中填充了 3 个数据表。

  1. acdTable（6 行）
  2. 小时表（5 行）
  3. 转移表（4 行）

acdTable 包含以下列：
Weeknumber、Date (dd-mm-yyyy)、Daynumber、Scores

hourTable 包含以下列：
Weeknumber、Date (dd-mm-yyyy)、Daynumber、Hours

transferTable 包含以下列：
Weeknumber、Date (dd-mm-yyyy)、Daynumber、Transfered

问题是当我使用 LINQ 将 DataTables 彼此加入时（在 Weeknumber 上），我只会得到 4 行而不是我需要的 6 行。

如果我是对的，这与两个表 fe 中都不存在的连接值有关：
acdTable 的 Weeknumber 20 与 Date 21-7-2020 和 hourTable 的 Weeknumber 20 没有 21-7-2020 同样适用于 transferTable

在这种情况下，我想查看：
如果 acdTable 或 hourTable 中不存在 acdTable 中的一行：
Weeknumber 20，Date 21-7-2020，Scores 10，Hours 0，Transfers 0

如果 acdTable 和/或 hourTable 中存在 acdTable 中的一行：
Weeknumber 20，Date 21-7-2020，Scores 10，Hours 32，Transfers 12

我希望这个例子对我想要完成的事情很清楚。

我的代码：

var merge = from acd in acdTable.AsEnumerable()
            join hours in hourTable.AsEnumerable()
                 on new
                       {
                         Weeknumber= acd.Field<int>("Weeknumber"),
                         Date= acd.Field<string>("Date")
                       }
                 equals new
                       {
                         Weeknumber= hours.Field<int>("Weeknumber"),
                         Date= hours.Field<string>("Date")
                       }
            join transfers in transferTable.AsEnumerable()
                 on new
                       {
                         Weeknumber= acd.Field<int>("Weeknumber")
                       } 
                 equals new 
                       {
                         Weeknumber= transfers.Field<int>("Weeknumber")
                       }
            select new
                       {
                         Weeknumber= acd.Field<int>("Weeknumber"),
                         Date= acd.Field<string>("Datum"),
                         Transfers= transfers.Field<string>("Transfers"),
                         Sales = acd.Field<int>("Sales"),
                         Hours = hours.Field<double>("Hours")
                        };

我的结果：

[0]    { Weeknumber = 29, Date = "13-07-2020", Transfers = "2", Sales = 201, Hours = 44 } <Anonymous Type>
[1]    { Weeknumber = 29, Date = "13-07-2020", Transfers = "3", Sales = 201, Hours = 44 } <Anonymous Type>
[2]    { Weeknumber = 29, Date = "13-07-2020", Transfers = "1", Sales = 201, Hours = 44 } <Anonymous Type>
[3]    { Weeknumber = 29, Date = "13-07-2020", Transfers = "2", Sales = 201, Hours = 44 } <Anonymous Type>
[4]    { Weeknumber = 29, Date = "14-07-2020", Transfers = "2", Sales = 156, Hours = 38.25 }     <Anonymous Type>
[5]    { Weeknumber = 29, Date = "14-07-2020", Transfers = "3", Sales = 156, Hours = 38.25 }     <Anonymous Type>
[6]    { Weeknumber = 29, Date = "14-07-2020", Transfers = "1", Sales = 156, Hours = 38.25 }     <Anonymous Type>
[7]    { Weeknumber = 29, Date = "14-07-2020", Transfers = "2", Sales = 156, Hours = 38.25 }     <Anonymous Type>
[8]    { Weeknumber = 29, Date = "15-07-2020", Transfers = "2", Sales = 155, Hours = 37.5 }      <Anonymous Type>
[9]    { Weeknumber = 29, Date = "15-07-2020", Transfers = "3", Sales = 155, Hours = 37.5 }      <Anonymous Type>
[10]   { Weeknumber = 29, Date = "15-07-2020", Transfers = "1", Sales = 155, Hours = 37.5 }      <Anonymous Type>
[11]   { Weeknumber = 29, Date = "15-07-2020", Transfers = "2", Sales = 155, Hours = 37.5 }      <Anonymous Type>
[12]   { Weeknumber = 29, Date = "16-07-2020", Transfers = "2", Sales = 122, Hours = 26 } <Anonymous Type>
[13]   { Weeknumber = 29, Date = "16-07-2020", Transfers = "3", Sales = 122, Hours = 26 } <Anonymous Type>
[14]   { Weeknumber = 29, Date = "16-07-2020", Transfers = "1", Sales = 122, Hours = 26 } <Anonymous Type>
[15]   { Weeknumber = 29, Date = "16-07-2020", Transfers = "2", Sales = 122, Hours = 26 } <Anonymous Type>
[16]   { Weeknumber = 29, Date = "17-07-2020", Transfers = "2", Sales = 0, Hours = 0 }    <Anonymous Type>
[17]   { Weeknumber = 29, Date = "17-07-2020", Transfers = "3", Sales = 0, Hours = 0 }    <Anonymous Type>
[18]   { Weeknumber = 29, Date = "17-07-2020", Transfers = "1", Sales = 0, Hours = 0 }    <Anonymous Type>
[19]   { Weeknumber = 29, Date = "17-07-2020", Transfers = "2", Sales = 0, Hours = 0 }    <Anonymous Type>

我想要完成的结果：

{Weeknumber = 29 , Date = "13-07-2020", Transfers = "2", Sales = "201", Hours =  44}
{Weeknumber = 29 , Date = "14-07-2020", Transfers = "3", Sales = "156", Hours =  38,25}
{Weeknumber = 29 , Date = "15-07-2020", Transfers = "1", Sales = "155", Hours =  37,50}
{Weeknumber = 29 , Date = "16-07-2020", Transfers = "2", Sales = "122", Hours =  26}
{Weeknumber = 29 , Date = "17-07-2020", Transfers = "0", Sales = "0", Hours =  0}
{Weeknumber = 29 , Date = "18-07-2020", Transfers = "0", Sales = "0", Hours =  0}

我做错了什么或者我能做些什么来得到我想要的结果。

我希望任何人都可以帮助我。

标签： c#linqjoindatatables

解决方案

尝试以下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication11
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable cdTable = new DataTable(); //(6 rows)
            cdTable.Columns.Add("Weeknumber", typeof(int));
            cdTable.Columns.Add("Date", typeof(DateTime));
            cdTable.Columns.Add("Daynumber", typeof(int));
            cdTable.Columns.Add("Scores", typeof(int));

            cdTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 13), 0, 201 });
            cdTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 14), 0, 156 });
            cdTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 15), 0, 155 });
            cdTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 16), 0, 122 });
            cdTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 17), 0, 0 });

            DataTable hourTable = new DataTable();//(5 rows)
            hourTable.Columns.Add("Weeknumber", typeof(int));
            hourTable.Columns.Add("Date", typeof(DateTime));
            hourTable.Columns.Add("Daynumber", typeof(int));
            hourTable.Columns.Add("Hours", typeof(int));

            hourTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 13), 0, 44 });
            hourTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 14), 0, 38 });
            hourTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 14), 0, 25 });
            hourTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 15), 0, 37 });
            hourTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 15), 0, 50 });
            hourTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 16), 0, 26 });
            hourTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 17), 0, 0 });

            DataTable transferTable = new DataTable();//(4 rows)
            transferTable.Columns.Add("Weeknumber", typeof(int));
            transferTable.Columns.Add("Date", typeof(DateTime));
            transferTable.Columns.Add("Daynumber", typeof(int));
            transferTable.Columns.Add("Transfered", typeof(string));

            transferTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 13), 0, "2" });
            transferTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 14), 0, "3" });
            transferTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 15), 0, "1" });
            transferTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 16), 0, "2" });

            var results = (from cd in cdTable.AsEnumerable()
                           join hour in hourTable.AsEnumerable() on cd.Field<DateTime>("Date") equals hour.Field<DateTime>("Date")
                           join t in transferTable.AsEnumerable() on cd.Field<DateTime>("Date") equals t.Field<DateTime>("Date")
                           select new { cd = cd, hour = hour, transfer = t }
                           ).GroupBy(x => x.cd.Field<DateTime>("Date"))
                           .Select(x => new {
                               Weeknumber = x.First().cd.Field<int>("Weeknumber"),
                               Date = x.First().cd.Field<DateTime>("Date"),
                               Transfers = x.First().transfer.Field<string>("Transfered"),
                               Sales = x.First().cd.Field<int>("Scores"),
                               Hours = string.Join(",",x.Select(y => y.hour.Field<int>("Hours")))
                           }).ToList();

        }
    }
 

}

推荐阅读