foreach first list by using ID and ID_SCH and see if it exists in the 2nd list and if it does then get other values from 2nd list.

string getRecords = "SELECT .....";
         List <Records> firstList = ReadAll(getRecords, reader => {
           return new Records(
             reader.ReadByName(NameRecord.ID, string.Empty),
             reader.ReadByName(NameRecord.ID_SCH, string.Empty)
           );
         });

         string getAllRecords = "SELECT .....";

         List <Records> secondList = ReadAll(getAllRecords, reader => {
           return new Records(
             reader.ReadByName(NameRecord.ID, string.Empty),
             reader.ReadByName(NameRecord.ID_SCH, string.Empty),
             reader.ReadByName(NameRecord.BSID, string.Empty),
             reader.ReadByName(NameRecord.BSID_SCH, string.Empty),
           );
         });

// currently I am able to use id only. But I would like to include `id` and `id_sch` as well in the below statement and then get the value of `BSID` and `BSID_SCH`.

var aa= data.Select(l1 => l1.Id).Intersect(secondList .Select(l2 => l2.Id)).ToList();

Acceptance criteria

1.foreach test in the first list see if it exists in the 2nd list. some how I managed to use idto get the result but I would like to useid_sch` as well.

2. if it does, get the tests that are excluded from 2nd list like BSID and BSID_SCH

CodePudding user response：

You can use a combination of id and id_sch from the first list to find a match in the second list and retrieve the values of BSID and BSID_SCH. Here's one way to do it using LINQ:

var result = firstList.Join(secondList, l1 => new { l1.Id, l1.IdSch }, l2 => new { l2.Id, l2.IdSch }, (l1, l2) => new { BSID = l2.BSID, BSID_SCH = l2.BSID_SCH }).ToList();

CodePudding user response：

You can use tuples to combine the two values

var aa = data
    .Select(l1 => (l1.Id, l1.id_sch))
    .Intersect(secondList.Select(l2 => (l2.Id, l2.id_sch)))
    .ToHashSet();

But if you want the items of the second list excluding the ones that are in the first list you must use Except:

var aa = secondList
    .Select(l2 => (l2.Id, l2.id_sch))
    .Except( data.Select(l1 => (l1.Id, l1.id_sch)) )
    .ToHashSet();

I put the result into a HashSet<T>, so that we can test whether an item exists fast and easily.

Now, you can use this result to filter the second list with of all its properties

var result = secondList
       .Where(r => aa.Contains((r.Id, r.id_sch)));