Linq To Sql, Build List Based On Comparison of Array Element vs Seperate List

Question

I'm using Linq to SQL in C# to check for conditions based on tables in separate databases. In the end, I need to compare an order list against the order list in the data warehouse to make sure everything matches.

// (Database 1) - Pull list of invalid customer types and statuses
//This selects invalid customers types we need to exclude from our results 
CustomersDBDataContext Customers = new CustomersDBDataContext();
List<int> validCustomerTypes = new List<int> { 1, 9, 10 };
var customersToExclude  = from w in oecMainCompanyIDs.CompanyTypes
                      where !validCompanyTypes.Contains(w.CompanyTypeID) &&
                      w.StatusID != 1
                      select w.customerID;
List<string> negList = Customers.ToList<string>();



// (Database 2) - Pull list from customer / order ID table
CustOrdDataContext orgKeys = new CustOrdDataContext();
var CustomerOrderResults = from v in orgKeys.MemberOrganizations
                       let customerID = v.customerKey.Substring(0, 10)
                       let orderID = v.OrdID
                       select new { customerID, orderID };

I need to build a list of OrderIDs from CustomerOrderResults from (Database 2), which excludes invalid customer types and status IDs from (Database 1). Any guidance on this?

....

eventually, I will return the values from this and compare them against the list in the datawarehouse.

// DataWarehouse (Database 3)
OrdersDBDataContext Orders = new OrdersDBDataContext();
                        var OrderList = from u in Orders.OrderTable
                        select u.orderNumbers;     

List<string> orderList = Orders.ToList<string>();

.. In The End, I want to compare my orderlist from Database one minus invalid records from database two to my list of orders from database three

CollectionAssert.AreEqual(expected, orderList);

...........

When I tried a list from the results (which is several thousand records), I received this this message at runtime "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100."

I wound up feeding them in separately, then sorting them this way.

        foreach (var x in rawOECResultsString)
        {
            if (oecMainNegativeResults.Contains(x.companyID) == false)
                filteredOECResults.Add(x.orgID);
        }

See any other way to handle it?

Gert Arnold · Answer 1 · 2016-05-24T15:08:53.997

0

You can get the valid orders directly from the database by:

var customersToExclude  = (from w in oecMainCompanyIDs.CompanyTypes
                      where !validCompanyTypes.Contains(w.CompanyTypeID) &&
                      w.StatusID != 1
                      select w.customerID).ToArray(); // 

var validOrders = (from v in orgKeys.MemberOrganizations
                   let customerID = v.customerKey.Substring(0, 10)
                   let orderID = v.OrdID
                   where !customersToExclude.Contains(customerID)
                   select orderID).Distinct();

And then the valid orders:

var validOrders = from u in Orders.OrderTable
                  where validOrders.Contains(u.OrderId)
                  select u.orderNumbers;

Note that customersToExclude is created with ToArray, so it's not a query definition anymore, but a materialized array of Id values.

edited May 24 '16 at 15:08

answered May 20 '16 at 21:02

Gert Arnold

105,341
31
202
291

I'm encountering "The query contains references to items defined on a different data context." when I put !customersToExclude in the second string. Any thoughts? – Richard May 24 '16 at 14:59
I think a `ToArray` should help here. See the edited answer. – Gert Arnold May 24 '16 at 15:09
I tried something similar to that with tolist, it's a long, long list, so I wound up with a runtime errors message "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100. – Richard May 25 '16 at 21:31
You could try your luck with a [chunky contains](http://stackoverflow.com/a/24542812/861716). – Gert Arnold May 25 '16 at 21:37

Linq To Sql, Build List Based On Comparison of Array Element vs Seperate List

1 Answers1