LINQ Join With Multiple Where Clause

I am struggling once again so any help would be gratefully received.

I have the following LINQ that pulls back a list of data:

public static List<tblWeight> GetWeights(string memberid, string locationid, string buyer, string subcategory, string product)
        {

            MyEntity getweights = new MyEntity ();

            var r = (from p in getweights.tblWeights
                     where p.MemberId == memberid &&
                              p.LocationId == locationid
                     select p);

            if (buyer != "Not Specified")
                r = r.Where(p => p.UnitUserField1 == buyer);

            if (subcategory != "Not Specif开发者_C百科ied")
                r = r.Where(p => p.UnitUserField2 == subcategory);

            if (product != "Not Specified")
                r = r.Where(p => p.IDDesc == product);

            return r.ToList();
        }

Lovely!

What I would like to do now is based upon this result set and the unit IDs (IDDesc), I then go to tblPurchase, pull back a few columns from tblPurchases and group the columns.

So for example, we have tblWeight looking like so:

MemberID    LocationID  Buyer   SubCategory IDDesc
1            1           Cat1   Sub1        ab
1            1           Cat1   Sub1        abc
1            1           Cat1   Sub2        abcd

The user makes a search for Sub1 in subcategory and the above LINQ does the trick and pulls back the first two rows from above. Fine.

What I need the LINQ to do now is to go to tblPurchases:

MemberID    LocationID  IDDesc  SupplierID  SupplierStatus
1           1          ab        Sup1            Live
1           1          abc       Sup1            Live
1           1          abcd      Sup2            Dead

And then pull back the following result so it is joined on MemberID, LocationID and IDDesc but just selects tblPurchases.

Sup1 Live (or all columns in tblPurchases, just grouped/distinct)

I have tried to add in a join and on but no matter how many different variations, I still come across the red squiggle of doom!!!

If anyone can help, beer/kiss is on offer again.

The following LINQ query should do what you want:

var result = from w in tblWeight
             where w.SubCategory == "Sub1"
             join p in tblPurchases on
                new { w.MemberID, w.LocationID, w.IDDesc } equals
                new { p.MemberID, p.LocationID, p.IDDesc }
             group p by new { p.SupplierID, p.SupplierStatus } into pg
             select pg.Key;

The variable result is a list containing tuples of SupplierID and SupplierStatus.

If you also want to put the conditional parts in there, it gets a little more complicated. Here's how to do it:

var weights = from w in tblWeight
              select w;
weights = weights.Where(w => w.SubCategory == "Sub1");
// You can add additional where clauses here.

// Now join with tblPurchases and group by SupplierID and SupplierStatus.
var result =
    weights.Join(tblPurchases,
                 w => new { w.MemberID, w.LocationID, w.IDDesc },
                 p => new { p.MemberID, p.LocationID, p.IDDesc },
                 (w, p) => p)
           .GroupBy(p => new { p.SupplierID, p.SupplierStatus },
                    (k, ps) => new
                                {
                                    k.SupplierID,
                                    k.SupplierStatus,
                                    TotalQty = ps.Sum(p => p.PurchaseQty)
                                });