I have an issue merging some lists together, I thought this would be a simple LINQ join but I think a little more complex and I'm going in circles. I have 3 tables below:

Table 1: Master list of products

Table 2: Product and corresponding Leaflet

Table 3: Product and corresponding Labels

Final result should be:-

Note: Some Products can have no leaflet and some Products can have no label. Also, leaflets and labels can be associated with more than one product.

Here's the query i tried, it brings everything together but there are many odd duplicates of rows.

PMatrix = (from a in AllProducts
           join b in Leaflets on a.Product equals b.Product into b_grp
           from b in b_grp.DefaultIfEmpty()
           join c in Labels on a.Product equals c.Product into c_grp
           from c in c_grp.DefaultIfEmpty()
           select new ProductMatrixModel
           {
               Product = a.Product,
               Leaflet = b == null ? "" : b.Leaflet, 
               Label = c == null ? "" : c.Label
           }).ToBindableCollection();

Sorry for the ugly stretched tables, I cannot see how to set the widths.

CodePudding user response：

I could not test the below code, but if you're into LINQ expressions, you could try something like this. I did something similar a while back.

AllProducts
.GroupJoin(Leaflets , l => l.Product, r => r.Product, (a, b) => new ProductMatrixModel { Product = a, Leaflet = b.FirstOrDefault() ?? string.Empty })
.GroupJoin(Labels, l => l.Product, r => r.Product, (a, b) => new ProductMatrixModel { Product = a.Product , Leaflet = a.Leaflet, Label = b.FirstOrDefault() ?? string.Empty })
.ToBindableCollection();

The above expression uses 'FirstOrDefault', so it will take the first result from possible join matches. If you require all the join matches, then you need to update your modal property to a list and work with the data accordingly.