I've a List(string type) "A" and a Datatable "B". I want to find out all the values of "A" which are neither in column1 nor in column2 of "B".

For demonstration:

List "A":

Lucknow
Delhi
Bhopal
Madhya Pradesh

Datatable "B":

Result:

Delhi

CodePudding user response：

If you are using EF core:

If you are looking for all the values of a list that are not saved in a table row's Column1 and Column2

       var listOfTownNames = new List<string>();
        // populate listOfTownNames with the town names data

       var listOfValuesNotInColumn1OrColumn2 = new List<string>();

        foreach (var townName in listOfTownNames)
        {
            var availableTown = _dbContext.Towns
            .FirstOrDefault(x.Column1==townName || x.Column2==townName);

            if (availableTown == null)
            {
                listOfValuesNotInColumn1OrColumn2.Add(townName);
            }
        }

        foreach (var value in listOfValuesNotInColumn1OrColumn2)
        {
            // print value
        }

If what you are looking for is the towns that are not in the list of town names:

            var listOfTownNames = new List<string>();
            // populate listOfTownNames with the town names data

            // get all towns which Column1 and Column2 data are not contained in the listOfTownNames list.
            var towns = _dbContext.Towns
                .Where(x => !(listOfTownNames.Contains(x.Column1) || listOfTownNames.Contains(x.Column2)))
                .ToList();

            // get access to each town's column1 and column2 value
            foreach (var town in towns)
            {
                // print town.Column1 value and town.Column2 value
            }

CodePudding user response：

As it was stated inside the comments section both ListA and DataTableB are fairly small, no larger than 10.

This means you can do the filtering inside the memory, you don't have to transfer ListA to the database.

var dataTableB = dbContext.B.ToList();
var listA = new List<string> { "Lucknow", ... };

Calculating those values that are not present in columnA

var columnAValues = dataTableB.Select(b => b.ColumnA);
var notPresentInColumnA = listA.Except(columnAValues); 

Calculating those values that are not present in columnB either

var columnBValues = dataTableB.Select(b => b.ColumnB);
var notPresentInEitherColumns = notPresentInColumnA.Except(columnBValues); 

Here is a working dotnetfiddle version.