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

  • 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

    Thursday, January 31, 2013 1:32 PM