Linq to Filter Data from 2 Datatable

Help Insights
,
1

Hi, I have a master data in dtMasterJadwal that contain

Nama Debitur Tgl Realisasi Tgl Lunas KPR No. ILS Tgl Ambil Dok
Dummy1 1/17/2014 1/25/2024 00000 01/02/2024
Dummy2 4/26/2012 1/29/2024 11111 01/02/2024
Dummy3 11/1/2019 2/23/2024 22222 01/07/2024
Dummy4 5/4/2018 2/24/2024 33333 01/07/2024
Dummy5 4/13/2020 2/27/2024 44444 01/07/2024
Dummy6 2/6/2017 12/6/2023 55555 01/07/2024
Dummy7 1/22/2019 1/22/2024 66666 01/07/2024
Dummy8 3/23/2022 2/21/2024 77777 01/03/2024
Dummy9 3/13/2018 2/26/2024 88888 01/03/2024

and dtRegisterRoyaFiltered that contain

No No. ILS Nama Debitur Tgl Lunas KPR Tgl Ambil Dok Operator
3333333 Dummy3 2/23/2024 01/07/2024 Data Tetap
4444444 Dummy4 2/24/2024 01/07/2024 Data Tetap
5555555 Dummy5 2/27/2024 01/07/2024 Data Hapus
6666666 Dummy6 12/6/2023 01/07/2024 Data Hapus
7777777 Dummy7 1/22/2024 01/07/2024 Data Tetap

I want to check if dtMasterJadwal have a data with same Tgl Ambil Dok but different No. ILS in dtRegisterRoyaFiltered, if there any data I will get it as datatable. With this sampe data the output I expect is No Data will be copy because all data in dtMasterJadwal have the same Tgl Ambil Dok and No. ILS with dtRegisterRoyaFiltered . Now I use this linq, but the output is not right, can you help me to update my linq? Thank you and have a nice day :slight_smile:

dtMasterJadwal.AsEnumerable().Where(Function(row) dtRegisterRoyaFiltered.AsEnumerable().Where(Function(r) r(“Tgl Ambil Dok”).ToString.Equals(DateTime.ParseExact(row(“Tgl Ambil Dok”).ToString.Split(" “c).first, “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)) And
Not r(“No. ILS”).ToString.Trim.Equals(row(“No. ILS”).ToString.Trim)).Any
).Select(Function(row) dtRegisterRoyaFiltered.Clone().LoadDataRow(New Object() {
“”,
row(“No. ILS”),
row(“Nama Debitur”),
DateTime.ParseExact(row(“Tgl Lunas KPR”).ToString.Split(” “c).first, “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”),
DateTime.ParseExact(row(“Tgl Ambil Dok”).ToString.Split(” "c).first, “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”),
row(“Operator”)}, False)
).CopyToDataTable()

2

@Hendaryie_Tjoeng,

Try this:

dtMasterJadwal.AsEnumerable() _
    .Where(Function(row) dtRegisterRoyaFiltered.AsEnumerable() _
        .Where(Function(r) DateTime.ParseExact(r("Tgl Ambil Dok").ToString().Split(" "c).First(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy") = _
                       DateTime.ParseExact(row("Tgl Ambil Dok").ToString().Split(" "c).First(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy") AndAlso _
                       Not r("No. ILS").ToString().Trim().Equals(row("No. ILS").ToString().Trim())).Any()) _
    .Select(Function(row) dtRegisterRoyaFiltered.Clone().LoadDataRow(New Object() {
        "",
        row("No. ILS"),
        row("Nama Debitur"),
        DateTime.ParseExact(row("Tgl Lunas KPR").ToString().Split(" "c).First(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy"),
        DateTime.ParseExact(row("Tgl Ambil Dok").ToString().Split(" "c).First(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy"),
        row("Operator")
    }, False)).CopyToDataTable()
3

Hi, thank you for reply, the linq is still make output 5 data, the output i expect is 0 data

4

@Hendaryie_Tjoeng

Dim query = 
    From masterRow In dtMasterJadwal.AsEnumerable()
    Let masterTglAmbilDok = DateTime.ParseExact(masterRow.Field(Of String)("Tgl Ambil Dok").Split(" "c)(0), "MM/dd/yyyy", CultureInfo.InvariantCulture)
    Where dtRegisterRoyaFiltered.AsEnumerable().
        Any(Function(registerRow) 
                Dim registerTglAmbilDok = DateTime.ParseExact(registerRow.Field(Of String)("Tgl Ambil Dok").Split(" "c)(0), "MM/dd/yyyy", CultureInfo.InvariantCulture)
                registerTglAmbilDok = masterTglAmbilDok AndAlso
                Not registerRow.Field(Of String)("No. ILS").Trim().Equals(masterRow.Field(Of String)("No. ILS").Trim())
            )
    Select dtRegisterRoyaFiltered.Clone().Rows.Add("", 
                                                masterRow.Field(Of String)("No. ILS"),
                                                masterRow.Field(Of String)("Nama Debitur"),
                                                DateTime.ParseExact(masterRow.Field(Of String)("Tgl Lunas KPR").Split(" "c)(0), "MM/dd/yyyy", CultureInfo.InvariantCulture).ToString("dd-MM-yyyy"),
                                                masterTglAmbilDok.ToString("dd-MM-yyyy"),
                                                dtRegisterRoyaFiltered.AsEnumerable().
                                                    Where(Function(r) DateTime.ParseExact(r.Field(Of String)("Tgl Ambil Dok").Split(" "c)(0), "MM/dd/yyyy", CultureInfo.InvariantCulture) = masterTglAmbilDok).
                                                    Select(Function(r) r.Field(Of String)("Operator")).FirstOrDefault()
                                            )

Dim resultDataTable As DataTable = query.CopyToDataTable()

Try this in invoke code

5

I Cannot use the invoke code because of bug in invoke code for some version of system package, and i can’t upgrade or downgrade my system package