Why does this Linq left outer join not work as expected?

Question

I have two lists, both using the same class (Part). The first list is the "master" list (engineerParts), the second (partDefaults) is the list of attribute values for those parts.

The first list contains 263 items, the second, 238. So, the final list should contain 263. However it doesn't, it contains 598!!

var partsWithDefaults = from a in engineerParts
                                    join b in partDefaults on
                                    new { a.PartNumber, a.Revision }
                                    equals
                                    new { b.PartNumber, b.Revision } into j1
                                    from j2 in j1.DefaultIfEmpty()
                                    select new Part()
                                    {
                                        NewPart = isNewPart(a.PartNumber, PadRevision(a.Revision), concatenateRevision),
                                        PartNumber = concatenateRevision == true ? string.Concat(a.PartNumber, PadRevision(a.Revision)) : a.PartNumber,
                                        Revision = concatenateRevision == true ? "" : a.Revision,
                                        ParentPart = a.ParentPart,
                                        Description = a.Description,
                                        BoMQty = a.BoMQty,
                                        UoM = (j2 != null) ? j2.UoM : null,
                                        ClassId = (j2 != null) ? j2.ClassId : null,
                                        ShortDescription = (j2 != null) ? j2.ShortDescription : null,
                                        ABCCode = (j2 != null) ? j2.ABCCode : null,
                                        BuyerId = (j2 != null) ? j2.BuyerId : null,
                                        PlannerId = (j2 != null) ? j2.PlannerId : null,
                                        OrderPolicy = (j2 != null) ? j2.OrderPolicy : null,
                                        FixedOrderQty = (j2 != null) ? j2.FixedOrderQty : null,
                                        OrderPointQty = (j2 != null) ? j2.OrderPointQty : null,
                                        OrderUpToLevel = (j2 != null) ? j2.OrderUpToLevel : null,
                                        OrderQtyMin = (j2 != null) ? j2.OrderQtyMin : null,
                                        OrderQtyMax = (j2 != null) ? j2.OrderQtyMax : null,
                                        OrderQtyMultiple = (j2 != null) ? j2.OrderQtyMultiple : null,
                                        ReplenishmentMethod = (j2 != null) ? j2.ReplenishmentMethod : null,
                                        ItemShrinkageFactor = (j2 != null) ? j2.ItemShrinkageFactor : null,
                                        PurchasingLeadTime = (j2 != null) ? j2.PurchasingLeadTime : null,
                                        MFGFixedLeadTime = (j2 != null) ? j2.MFGFixedLeadTime : null,
                                        PlanningTimeFence = (j2 != null) ? j2.PlanningTimeFence : null,
                                        FulfillMethod = (j2 != null) ? j2.FulfillMethod : null,
                                        ItemStatus = (j2 != null) ? j2.ItemStatus : null,
                                        TreatAsEither = (j2 != null) ? j2.TreatAsEither : false,
                                        AltItem1 = (j2 != null) ? j2.AltItem1 : null,
                                        AltItem2 = (j2 != null) ? j2.AltItem2 : null,
                                        AltItem3 = (j2 != null) ? j2.AltItem3 : null,
                                        AltItem4 = (j2 != null) ? j2.AltItem4 : null,
                                        AltItem5 = (j2 != null) ? j2.AltItem5 : null,
                                        DesignAuthority = (j2 != null) ? j2.DesignAuthority : null,
                                        Level = a.Level
                                    };

            return partsWithDefaults.ToList<Part>();

The anonymous types you're making for the comparison will not Equal, or ReferenceEqual one another. I would expect that, at a glance, to land you with 263 results. Use && to join on multiple columns instead like shown [here](http://stackoverflow.com/questions/345427/linq-to-sql-join-multiple-columns-from-the-same-table) — Smudge202, Aug 03 '11 at 10:37
As Join's in C# don't support &&, I presume you mean remove the join and use the WHERE clause? Although it must surely be doing some sort of join as 263+238 = 501, yet I'm getting 598 items! — sparkymark75, Aug 03 '11 at 10:43
Also, I need to do a left join to ensure all items from the engineerParts list are included, even where there's no matching parts in the partDefaults list. A WHERE clause will effectively do a join. — sparkymark75, Aug 03 '11 at 11:00
Fair point. To get your anonymous types to match correctly, name the fields. `{col1 = a.PartNumber, col2 = a.Revision}`. This (_IIRC_) will make the compiler consume the same _Type_ for both the anonymous types. If you still have issues after that, change the result to an anonymous class and reduce the number of fields in the resulting type to 1, see if the issue still exists. If not, add the second field, then third, etc... — Smudge202, Aug 03 '11 at 12:14
I've tried both your suggestions above and both yield the same result as before, 598 items :( — sparkymark75, Aug 04 '11 at 08:50
My C# linq to sql syntax isn't very good (as you pointed out), but I can provide you a Linq/Lambda solution that _should_ work if that's any use? — Smudge202, Aug 04 '11 at 09:17

score 0 · Accepted Answer · answered Aug 04 '11 at 09:36

0

Turns out it was a data issue. The person that had sent me the data in Excel for the second list had left duplicates in it!!!!

answered Aug 04 '11 at 09:36

sparkymark75

583
8
29

Why does this Linq left outer join not work as expected?

1 Answers1