none
How to find items not in table RRS feed

  • Question

  • 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?

    Monday, August 27, 2012 9:20 PM

Answers

  • I think what you are looking for is the Except operator.  You would get a list of the user entered names as one list and a list of the names in the database.

    You would then get the items that are in the user entered list "Except" those found in the database.

    An example from MSDN (without the database part) is found at :

    http://msdn.microsoft.com/en-us/library/bb300779(v=vs.100)

    Hope this helps

    Lloyd Sheen


    Lloyd Sheen

    • Marked as answer by Alexander Sun Monday, September 3, 2012 9:12 AM
    Tuesday, August 28, 2012 4:19 AM
  • Hi Juan468761606;

    sqlguy is correct, you should use the Linq Except method to get a list of those that are NOT in the database. Here is some sample code that should do that for you.

    Dim sqlQuery = From c In _dbContext.Customers
                   Where custNamesForQuery.Contains(c.custName)
                   Select c
    
    Dim notInQuery = custNamesForQuery.Except(From c in custNamesForQuery _
                      Where sqlQuery.Any(Function( r ) r.custName = c) _
                      Select c)

    At this point sqlQuery contains the records returned back from the database and notInQuery contains a list of the names not found.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Monday, September 3, 2012 9:12 AM
    Tuesday, August 28, 2012 2:13 PM

All replies

  • I think what you are looking for is the Except operator.  You would get a list of the user entered names as one list and a list of the names in the database.

    You would then get the items that are in the user entered list "Except" those found in the database.

    An example from MSDN (without the database part) is found at :

    http://msdn.microsoft.com/en-us/library/bb300779(v=vs.100)

    Hope this helps

    Lloyd Sheen


    Lloyd Sheen

    • Marked as answer by Alexander Sun Monday, September 3, 2012 9:12 AM
    Tuesday, August 28, 2012 4:19 AM
  • Don't know the Visual Basic syntax. But in C# you would write

    if(sqlQuery.Count()==0)
    {
        //whatever
    }

    Tuesday, August 28, 2012 2:04 PM
  • Hi Juan468761606;

    sqlguy is correct, you should use the Linq Except method to get a list of those that are NOT in the database. Here is some sample code that should do that for you.

    Dim sqlQuery = From c In _dbContext.Customers
                   Where custNamesForQuery.Contains(c.custName)
                   Select c
    
    Dim notInQuery = custNamesForQuery.Except(From c in custNamesForQuery _
                      Where sqlQuery.Any(Function( r ) r.custName = c) _
                      Select c)

    At this point sqlQuery contains the records returned back from the database and notInQuery contains a list of the names not found.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Monday, September 3, 2012 9:12 AM
    Tuesday, August 28, 2012 2:13 PM