locked
Filter LINQ results based on table contents RRS feed

  • Question

  • User-173651909 posted

    I have two tables, one containing a list of components (dbo.components) and the other a small table containing the ids only (a junction table called dbo.support). A webpage displays all records from the components table. Instead I need to only return the results from the components table, which aren't already contained in the support table. For example, if the components table contains records with ID 1 - 100, and the support table contains ComponentID 1 to 50, then I only want it to return the 50 that aren't contained in support.

    Schema is as follows:

    dbo.components:
    ID, Description, other fields etc
    
    dbo.support
    ID, ComponentID, VendorID
    

    So is it possible to do something like put all the components results in an array, and then remove a record if the ID is in support.ComponentID?

    Thanks in advance
    Adam

    Friday, April 22, 2016 11:38 AM

Answers

  • User-698989805 posted

    Hello Adam-M! Please do the following:

    dbo.components:
    ID, Description, other fields etc
    
    dbo.support
    ID, ComponentID, VendorID
    
    var result = db.components.Where(c => !db.support.Any(f => f.ComponentID == c.ID))
                 .Select(c => new { ID = c.ID, Description = c.Description }).ToList();

    Better use ToList() to get the output.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 23, 2016 9:08 AM