C# LINQ Select rows from DataTable or DataView Where 2 Value Matches

Question

I converted CRMDataSetSent into a DataTable/DataView containing the full list of records.

DataTable CRMDataSetSent_Dt = new DataTable();
da.Fill(CRMDataSetSent_Dt, Variables.CRMDataSetSent);
DataView CRMDataSetSent_Dv = new DataView(CRMDataSetSent_Dt);

---

distinct_Wo_Wop_Dt stores distinct msdyn_workorderid + msdyn_workorderproductid pairs.

DataTable distinct_Wo_Wop_Dt= CRMDataSetSent_Dv.ToTable(true, "msdyn_workorderid", "msdyn_workorderproductid");

---

For each row in distinct_Wo_Wop_Dt, I need to fetch rows from CRMDataSetSent_Dt/CRMDataSetSent_Dv where both values match.

Right now I am doing it via foreach loop, which is not as efficient as I want it to be.

Is there a way to do this via LINQ?

Answer 1

If i understood it correctly you want to remove duplicates according to 2 fields. You don't need the DataView temp storage at all which is not an efficient way to filter rows and it's not readable.

You can use GroupBy:

CRMDataSetSent_Dt = CRMDataSetSent_Dt.AsEnumerable()
    .GroupBy(r => (msdyn_workorderid:r.Field<int>("msdyn_workorderid"),msdyn_workorderproductid:r.Field<int>("msdyn_workorderproductid")))
    .Select(g => g.First())
    .CopyToDataTable();

I have assumed that these fields are of type int, change it accordingly if necessary. You can also change the logic which row should be taken. I simply kept an arbitrary, the first. You could for example add an OrderBy to add a custom logic.