Group by problem with multiple table joins and columns in WCF RIA Services using LightSwitch

Question

Dear All,

I have four tables, FarmerMaster, FarmerDetails, Province and District. I want to get the following result.

Province     District     Gender     TotalFarmers     Hectares

A               A             Male         10                    10

A               A             Female     20                    12

I have written the following quries.

var result= from a in context.FarmerMaster
                  join  b in context.FarmerDetails on a.mid equals b.mid
                  join  c in context.District on a.DistrictID equals  c.Id
                  join  d in context.Province on c.PID equals d.PID
                 group b by new {b.ID,d.Province,c.District,b.Gender,b.Hectares} into g
                 select new
                {
                 Id=g.key.ID,
                 Province=g.key.Province,
                 District=g.key.District,
                 Gender=g.key.Gender,
                 TotalFarmers=g.Select(z=>z.ID).Distinct().Count(),
          Hectares=g.Sum(x=>x.Hectares)
                 };

Now When  I group by FarmerDetails table  it give me all the records, When I group by Province then if the province has male and female, it returns only male or female and the same is with District and FarmerMaster.

Please need your help!!

Best,

---

Inayat Rehman