LINQ to Entities performance issue with Where and Contains

Question

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();
}

I currently first get and select all the distinct part numbers from Table A.

Then I check Table A and Table B's partnumbers and get each part from Table B where there that part number is not in Table A.

There are about 11,000 records in table B and 200,000 records in table A. I should be getting about 9000 parts which are not in table A.

I am running into huge performance issues with that second LINQ statement. If I do a .Take(100), that will even take around 20-30 seconds. Anything above 1000 will take way too long.

Is there a better way to write this LINQ statement?

Are TableA and TableB related somehow? Or they are independent from each-other? — Karel Tamayo, Nov 30 '16 at 23:42
If you put partmasterids into a HashSet (assuming PartNumber is an int), the look ups will be faster then using a list. You can just do HashSet(...Distinct()). Try that and see if the performance is better. — SledgeHammer, Nov 30 '16 at 23:48
Another idea may be to get rid of the Where clause entirely and use .Except(). — SledgeHammer, Nov 30 '16 at 23:51

score 3 · Accepted Answer · edited May 23 '17 at 10:29

From what I understand of your question, the equivalent in SQL would be something like

SELECT DISTINCT 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 look something like this

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();

LINQ to Entities performance issue with Where and Contains

1 Answers1