C# : LINQ Group Multiple Datatables with difference in rowcount

Question

I'm pretty new using LINQ + DataTables and I'm stuck for some time. I have searched the internet for some similar problems and solution, but its not exactly what I want to accomplish.

I'll try my best to describe the case and setup:

In text :

I have 3 DataTables filled from a SQL query.

1. acdTable (6 rows)
2. hourTable (5 rows)
3. transferTable (4 rows)

acdTable contains the following Columns :
Weeknumber, Date (dd-mm-yyyy), Daynumber, Scores

hourTable contains the following Columns :
Weeknumber, Date (dd-mm-yyyy), Daynumber, Hours

transferTable contains the following Columns :
Weeknumber, Date (dd-mm-yyyy), Daynumber, Transfered

The problem is when I use LINQ to join the DataTables with eachother (on Weeknumber) I will only get 4 rows back instead of the 6 I need.

If I'm right this has something to do with the joined values which does not exist in either table f.e :
acdTable has Weeknumber 20 with Date 21-7-2020 and hourTable has Weeknumber 20 without 21-7-2020 same goes for transferTable

In this case I would like to see :
If a row from acdTable does not exist in acdTable or hourTable :
Weeknumber 20, Date 21-7-2020, Scores 10, Hours 0, Transfers 0

If a row from acdTable exist in acdTable and or hourTable :
Weeknumber 20, Date 21-7-2020, Scores 10, Hours 32, Transfers 12

I hope the example is clear for what I want to accomplish.

My Code:

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

My Result :

[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>

Result I want to accomplish :

{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}

What am I doing wrong or what can I do to get the result I want.

I hope anyone can help me.

Answer 1

Try following :

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

        }
    }
 

}

Answer 2

So you want to join three tables on some equal keyValue. If for every row in AcdTable you want all rows from TransferTable and HourTable with the same WeekNumber and Date.

Alas LINQ doesn't have a method to join three tables, so we'll have to create it. If we do this as an extension method we can use it as if it was a standard LINQ method. See extension methods demystified

Convert table to IEnumerable

You will be doing LINQ-like things more often. Consider to create extension methods that will convert your tables to IEnumerable:

private static IEnumerable<DataRow> ToDataRows(this DataTable table)
{
    foreach (var row in table.Rows)
    {
        yield return row;
    }
}

private static AcdRecord ToAcdRecord(this DataRow row)
{
     return new AcdRecord
     {
         // TODO: fill properties of Acd with values from the DataRow
     }
}

To convert the table to Acd is a one liner:

public IEnumerable<AcdRecord> ToAcdRecords(this DataTable table)
{
     return table.ToDataRows()
                 .Select(dataRow => dataRow.ToAcd());
}

Similar one liners for ToHours and ToTransfers:

Usage:

DataTable acdTable = ...
DataTable hourTable = ...
DataTable transferTable = ...

IEnumerable<AcdRecord> acds = acdTable.ToAcdRecords();
IEnumerable<HourRecord> hourRecords = hourTable.ToHourRecords();
IEnumerable<TransferRecord> transferRecords = transferTable.ToTransferRecords(); 

Simple Solution

The extension method is only for your problem: GroupJoin your three tables on WeekNumber and Date and return per AcdTable, with its HourTables and TransferTables a result.

public static IEnumerable<TResult> CalculateSales<TResult>(
    this IEnumerable<AcdRecord> acdRecords,
    IEnumerable<HourRecord> hourRecords,
    IEnumerable<TransferRecord> transferRecords,

    // Parameter resultSelector: for every AcdRecord, and all its HourRecords
    // and all its TransferRecords, make one new TResult:
    Func<AcdRecord, IEnumerable<HourRecord>, IEnumerable<TransferRecord>, TResult> resultSelector)
{
    // TODO implement.
}

Usage will be:

acdRecords.CalculateSales(hourRecords, transferRecords,

(acdRecord, hourRecordsOfThisAcdRecord, transferRecordsOfThisAcdRecord) => new
{
    WeekNumber = acdRecord.WeekNumber,
    Date = acdRecord.Date,

    Transfers = transferRecordsOfThisAcdRecord.Count(),
    Sales = acdRecord.Sales,
    Hours = hourRecordsOfThisAcdRecord.Select(hourRecord => hourRecord.Hour)
                                      .Sum(),
    ... // etc. Use the acdRecord, or the HourRecords of this acdRecord
        // or the TransferRecords of this acdRecord to create the properties
}

The implementation: after checking the input parameters for null, we put the hourRecords and the transferRecords in separate LookupTables, with key [WeekNumber, Date].

For every AcdRecord, we fetch all hourRecords with the same [WeekNumber, Date]. Do the same for the lookuptable of TransferRecords and create the result. yield return the result.

// TODO: exception if input null


var hourRecordLookup = hourRecords.ToLookup(hourRecord => new
{
    WeekNumber = hourRecord.WeekNumber,
    Date = hourRecord.Date,
});

var transferLookup = transferRecords.ToLookup(transferRecord => new
{
    WeekNumber = transferRecord .WeekNumber,
    Date = transferRecord .Date,
});

foreach (var acdRecord in acdRecords)
{
    var key = new
    {
        WeekNumber = acdRecord .WeekNumber,
        Date = acdRecord .Date,
    });

    var hourRecordsOfThisAcd = hourRecordLookup[key];
    var transferRecordsOfThisAcd = transferRecordLookup[key]

    // use the resultSelector to create a TResult:
    TResult result = resultSelector(acdRecors, hourRecordsOfThisAcd, transferRecordsOfThisAcd);
    yield return result;
}

Reusable solution

If you will be GroupJoining three tables more often, consider to create an extension method that takes any three Enumerable sequences.

The code will be very similar to the code above. We only need extra parameters to say which properties will be used as key.

private static IEnumerable<TResult> GroupJoin<T1, T2, T3, TKey, TResult>(
    this IEnumerable<T1> t1Records,
    IEnumerable<T1> t2Records,
    IEnumerable<T1> t3Records,

    // KeySelector to fetch key for each T1 / T2 / T3
    Func<T1, TKey> key1Selector,
    Func<T2, TKey> key2Selector,
    Func<T3, TKey> key3Selector,

    // ResultSelector:
    Func<T1, IEnumerable<T2>, IEnumerable<T3>, TResult> resultSelector)
{
    var t2Lookup = t2Records.ToLookup(t2Record => key2Selector(t2Record));
    var t3Lookup = t3Records.ToLookup(t3Record => key3Selector(t3Record));

    foreach (var t1Record in t1Records)
    {
        var key = key1Selector(t1Record);
        var t2RecordsOfThisT1Record = t2Lookup(key);
        var t3RecordsOfThisT1Record = t3Lookup(key);

        var result = resultSelector(t1Record, t2RecordsOfThisT1Record, t3RecordsOfThisT1Record);
        yield return result;
}

The nice thing is that you can use this for classes with different properties.

For example: from a database with Schools, Teachers and Students, give me every School with its students and its Teachers:

var result = Schools.GroupJoin(Students, Teachers:

// KeySelectors:
school => school.Id,           // from every School take the primary key in Id
student => student.SchoolId,   // from every Student take the foreign key to School
teacher => teacher.SchoolId,   // from every Teacher take the foreign key to School

// ResultSelector: for every School, with all its Students and all its Teachers:
(school, students, teachers) => new
{
    Id = school.Id,
    Name = school.Name,
    ...

    Students = students.Select(student => new
    {
         Id = student.Id,
         Name = student.Name,
         ...
    })
    .ToList(),

    Teachers = teachers.Select(teacher => new
    {
       ...
    })
    .ToList(),
);

Note that the Key for School is different than the Key for Students. They only have the same type.

Answer 3

I am really not that good at Linq. But if i may expand on Jdweng's answer. Would the below suffice?

I added a left join with a null check (only on transfers)

 using System;
 using System.Linq;
 using System.Data;
 
 namespace ConsoleApplication11
 {
     class Program
     {
         static void Main(string[] args)
         {
             DataTable acdTable = new DataTable(); //(6 rows)
             acdTable.Columns.Add("Weeknumber", typeof(int));
             acdTable.Columns.Add("Date", typeof(DateTime));
             acdTable.Columns.Add("Daynumber", typeof(int));
             acdTable.Columns.Add("Scores", typeof(int));
             
             acdTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 13), 0, 201 });
             acdTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 14), 0, 156 });
             acdTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 15), 0, 155 });
             acdTable.Rows.Add(new object[] { 29, new DateTime(2020, 7, 16), 0, 122 });
             acdTable.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 acd in acdTable.AsEnumerable()
                            join hour in hourTable.AsEnumerable() on acd.Field<DateTime>("Date") equals hour.Field<DateTime>("Date")
                            join t in transferTable.AsEnumerable() on acd.Field<DateTime>("Date") equals t.Field<DateTime>("Date") into transferGroup
                            from t in transferGroup.DefaultIfEmpty()
                            select new { acd = acd, hour = hour, transfer = t }
                            ).GroupBy(x => x.acd.Field<DateTime>("Date"))
                            .Select(x => new {
                                Weeknumber = x.First().acd.Field<int>("Weeknumber"),
                                Date = x.First().acd.Field<DateTime>("Date"),
                                Transfers = x.First().transfer == null ? "0" : x.First().transfer.Field<string>("Transfered"),
                                Sales = x.First().acd.Field<int>("Scores"),
                                Hours = string.Join(",",x.Select(y => y.hour.Field<int>("Hours")))
                            }).ToList();
         }
     }
 }