Add a Lambda in a LINQ query to replace a line in a foreach

Question

I have a situation where I have to match up multiple customers numbers from one system with a single customer number in another system. So for instance customer number 225, 228 and 223 in system A will all map to customer number 110022 in system B. Easy enough, I have a matrix setup to do that.

I pull the matrix data in like this:

 var dt_th_matrix = (from m in aDb.Matrix_Datatrac_TopHat select m).ToArray();

So the records would be something like:

customerA: 3 CustomerB: 1001

CustomerA: 4 CustomerB: 1001

CustomerA: 5 Customer: 1002

Then I do a big data pull and step through all the items. For each of the items I go grab the matching customer number from the matrix like this:

foreach (var dt_stop in mainPull)
        {
            int? th_customerId = (from d in dt_th_matrix 
                                  where d.datatrac_customer_no == dt_stop.Customer_No.ToString() 
                                  select d.tophat_customer_detail_Id).First();

What I would rather do is to just embed the code to grab the customer numbrer from the matrix directly in my datapull -- the part "Query goes here somehow" will be some type of Lambda I assume. Any help?

I have tried something like this:

  th_customerId = (dt_th_matrix.First().tophat_customer_detail_Id.Equals c.Customer_No)

But that is not it (obviously)

var mainPull = (from c in cDb.DistributionStopInformations
                        join rh in cDb.DistributionRouteHeaders on c.Route_Code equals rh.Route_Code
                        where c.Company_No == 1 &&
                       (accountNumbers.Contains(c.Customer_No)) &&
                         (brancheSearchList.Contains(c.Branch_Id) && brancheSearchList.Contains(rh.Branch_Id)) &&
                        c.Shipment_Type == "D" &&
                       (c.Datetime_Created > dateToSearch || c.Datetime_Updated > dateToSearch) &&
                       rh.Company_No == 1 &&
                       ((rh.Route_Date == routeDateToSearch && c.Route_Date == routeDateToSearch) ||
                       (rh.Route_Date == routeDateToSearch.AddDays(1) && c.Route_Date == routeDateToSearch.AddDays(1)))
                        orderby c.Unique_Id_No
                        select new
                        {
                            c.Datetime_Updated,
                            th_customerId = ("Query goes here somehow")
                            c.Datetime_Created,
                            c.Unique_Id_No,
                            c.Original_Unique_Id_No,
                            c.Unique_Id_Of_New_Stop,
                            c.Branch_Id,
                            c.Route_Date,
                            c.Route_Code,
                            c.Sequence_Code,
                            c.Customer_No,
                            c.Customer_Reference,
                            c.Shipment_Type,
                            c.Stop_Name,
                            c.Stop_Address,
                            c.Stop_City,
                            c.Stop_State,
                            c.Stop_Zip_Postal_Code,
                            c.Stop_Phone_No,
                            c.Stop_Arrival_Time,
                            c.Stop_Departure_Time,
                            c.Address_Point,
                            c.Stop_Special_Instruction1,
                            c.Stop_Special_Instruction2,
                            c.Stop_Expected_Pieces,
                            c.Stop_Expected_Weight,
                            c.Stop_Signature,
                            c.Actual_Arrival_Time,
                            c.Actual_Depart_Time,
                            c.Actual_Service_Date,
                            c.Stop_Actual_Pieces,
                            c.Stop_Exception_Code,
                            c.Created_By,
                            rh_Route_Date = rh.Route_Date,
                            routeHeaderRouteCode = rh.Route_Code,
                            rh.Actual_Driver,
                            rh.Assigned_Driver,
                            rh_routeDate = rh.Route_Date

                        }).ToArray();

I will try and clarify the above.

What I need is for the Linq query to say : For each record that I pull I will goto the Array named dt_th_matrix and get the record that matches for this line and use it.

The data in the matrix looks exactly like this:

Record 1: datatrac_customer_no: 227, tophat_customer_detail_Id 1

Record 2: datatrac_customer_no: 228, tophat_customer_detail_Id: 1

Record 3: datatrac_customer_no: 910, tophat_customer_detail_Id: 5

Then for the first record pulled in the mainPull the field c.customer_no == 228 so I need the query in the select new statement to replace th_customerId with 1 (from Record 2 in the Matrix.

Then say the next record pulled in the mainPull the field c.customer_no = 910 the th_customerId would be 5.

That is what the first line of my foreach statement is currently doing. I want to move that logic to inside my LINQ query.

It's easier for me to follow with some sample data, so I'm not quite sure on the syntax for you, but I think you want a group by clause on the id at first, and then join off of that: https://stackoverflow.com/q/9173410/1462295 — BurnsBA, Dec 20 '18 at 13:22
I added clarification and more data. I read over the group by link you provided and that does not look like it to me. — Joe Ruder, Dec 20 '18 at 15:20

Christoph Sonntag · Accepted Answer · 2018-12-20T15:42:50.717

If I understand you correctly, using a dictionary with a key of datatrac_customer_no and a value of tophat_customer_detail_Id would be a good idea here:

var dt_th_matrix = (from m in aDb.Matrix_Datatrac_TopHat select m).ToDictionary(m=>m.datatrac_customer_no,m=>m.tophat_customer_detail_Id);

With this you should be able to replace your "Query goes here somehow" with

dt_th_matrix[c.Customer_No]

Using LINQ would be possible as well, but I don't think it's worth the performance overhead and reduction in readibility.

If you still want to use LINQ for this with your original matrix, this should work as your query:

dt_th_matrix.Single(m => m.datatrac_customer_no == c.Customer_No).tophat_customer_detail_Id

Both expressions will throw an exception if the key is not found or exists multiple times - but if I understand your structure correctly this should not be possible. Otherwise you need to check for this.

Thank you -- I never thought about using a dictionary, good idea. — Joe Ruder, Dec 20 '18 at 16:50

Add a Lambda in a LINQ query to replace a line in a foreach

1 Answers1