LINQ CSV Database - Quick way for except

I have a pair CSV Files.

CSV1 : Option Number , Product Number , Product Name

CSV2 : Product Number , Product Details , Product Details

The number of records is very high , number of files (pairs generated weekly) is also very high.

CSV2 will have some of the Product details , manually the Product Details are entered.

How can I : 1.Re Generate CSV1 to have only those Product Number (and Option and Name) for those Products whose details do not exist in CSV2

Tried LINQ to CSV from code project , able to read / write fine but the EXCEPT part takes a lot of time.

var query =    
    from c in dc.Customers    
    where !(from o in dc.Orders    
            select o.CustomerID)    
           .Contains(c.CustomerID)    
    select c;

Thi开发者_JAVA百科s is the equivalent , but still too slow.

How can I trim all the fields retrieved using the query. If I am to set the type to int - Product Number. That should help. Please provide your feedback. I can provide the code. But it is mostly based on LINQTOCSV from codeproject.

---

You could load the product numbers from CSV2 in a hashset and then your where clause could become:

  where !hashSet.Contains(productID)

which performance should be better (O(1))

---

Products from both files select to dictionaries, where key is product number and then compare, it will be faster.