Filter Linq return set based on table in separate DB

I have two tables. One in one database and one in a separate database. I need to populate a dropdown list with options from the first, filtered by the second. I am using Linq-to-SQL. Below is how I pull the "un-filtered" list.

public static DataTable GetSPCCodeList()
{   
   using (var context = ProviderDataContext.Create())
   {
      IQueryable<tblProviderAdminSPCCode> tSPCCode = context.GetTable<tblProviderAdminSPCCode>();
      return (tSPCCode
                 .Where(spcCode => spcCode.Inactive == null)
                 .OrderBy(spcCode => spcCode.SPCCodeID)
                 .Select(spcCode => new 
                                    { spcCode.SPCCodeID, spcCode.SPCDescription, 
                                      spcCode.SPCCategoryID }))
                 .Cop开发者_运维知识库yLinqToDataTable();
   }
}

The table I need to filter against simply contains a column for SPCCodeID. How would I go about filtering my List based on if they exist in the second table?

Generate and execute a LINQ query on the other database to get a collection of your SPCCodeIDs into memory variable e.g.

IList<int> spcCodeIDs = /* query goes here */;

Then run your query but replace the Where clause like this

.Where(spcCode => spcCode.Inactive == null
                    && spcCodeIDs.Contains(spcCode.SPCCodeID))

I would create a list of these SPCCodeID values from the other database and then modify your where clause to be:

.Where(spcCode => spcCode.Inactive == null && spcCodeList.Contains(spcCode.SPCCodeID))

Remember to make the scpCodeList an actual List using ToList() because I think it will have issues if you make a query with two different DataContexts.

You can actually make LINQ to SQL select from multiple SQL databases at the same time if they are on the same server and available on a single connection string.

There are two options:

1. Create a view in the first database that links to the second
2. Create temporary data context, copy the DBML details out and modify the table name

Full details on how to do these are actually listed on my blog rather than repeating them here: http://damieng.com/blog/2010/01/11/linq-to-sql-tips-and-tricks-3