Table A has - ID(PK), PartNumber, Code1, Code2
Table B has - InventoryID(PK) PartNumber, Part, and a bunch of other columns.
I need to get everything from Table B where Table B's PartNumber is NOT in Table A.
Example: Table B has PartNumber 123. There is no PartNumber in Table A for 123. Get that row.
What I currently have:
using (SomeEntity context = new SomeEntity())
var partmasterids = context.PartsMasters.Select(x => x.PartNumber).Distinct().ToList();
var test = context.Parts.Where(x => !partmasterids.Contains(x.PartNumber)).ToList();
From what I understand, the equivalent in SQL would be something like
SELECT B.PartNumber AS MissingParts FROM TableB as B LEFT OUTER JOIN TableA as A ON B.PartNumber = A.PartNumber WHERE A.PartNumber IS NULL
Run that SQL and measure the time it takes. Without indexes, that's as fast as it's going to get.
Now, if you really have to do it in EF, you'll need to do an equivalent statement, complete with the left join. Based on this question, it would probably look something like this (untested!)
var query = from b in TableB join a in TableA on b.PartNumber equals a.PartNumber into joind from existsInA in joind.DefaultIfEmpty() where existsInA == null select b.PartNumber; var missingParts = query.Distinct().ToList();