How to find items not in table

[donjt81]

I am writing a VB.net application and using MS SQL as the back end. I am using LINQ to access one of the tables and here is what I want to do.

User enters a list of customers like "Company1", "Company2", "Company3", "Company4". Let's assume that in the DB only companies 1 and 2 are available. I want to figure out how to know that the missing companies are 3 and 4

Currently I am just writing something like this to get the companies that are in the DB.

Dim sqlQuery = From c In _dbContext.Customers
               Where custNamesForQuery.Contains(c.custName)
               Select c

_custList = sqlQuery.ToList()

_custList gives me the companies that are in the DB. But I also want to notify the users of the companies that are not in the database.

Any suggestions?

 

[jmcilhinney]

Use LINQ to SQL or LINQ to Entities to get all the names from the database and then LINQ to Objects to determine which values are missing.

Dim allNames = _dbContext.Customers.Select(Function(c) c.custName).ToArray()
Dim missingNames = custNamesForQuery.Except(allNames).ToArray()

That second array will contain all the names from the original list except those that are in the database.