Linq query with Average

Question

I have 3 tables:

tblCompany : Id, Name, Location 
tblRating  : Id, CompanyId, Rate 
tblImages  : Id, CompanyId, ImagePath

I have a class

public class Company
{
    public string Id { get; set; }
    public string CompanyName { get; set; }
    public string Location { get; set; }
    public string AverageRate { get; set; }
    public List<string> ImagePath { get; set; }
}

I want a LINQ query to produce a result to match the Company class.

I wrote this query but it does not work

        List<Company>  result = null;

        using (DataContext dc = new DataContext())
        {
            result = (from a in dc.GetTable<tblCompany>()
                      join b in dc.GetTable<tblRating>()
                      on a.Id equals b.CompanyId
                      join c in dc.GetTable<tblImages>()
                      on a.Id equals c.CompanyId

                      select new SearchResult
                      {
                          CompanyName = a.Company,
                          Location = a.Location,
                          AverageRate = b.Rate.Average(),
                          ImagePath = c.ImagePath.ToList()

                      }).ToList<Company>();
        }

Answer 1

Edited for whole Query:

(I've to say i'm sorry but i have no way of testing this query yet)

You can use the let clause instead of the joins:

var result = (from c in dc.GetTable<tblCompany>()

              let r = (from re in dc.GetTable<tblRating>()
                       where re.CompanyId == c.Id && re.Rate != null
                       select re.Rate)

              let i = (from im in dc.GetTable<tblImages>()
                       where im.CompanyId == c.Id
                       select im.ImagePath)

              select new SearchResult
              {
                  CompanyName = c.Name,
                  Location = c.Location,
                  AverageRate = r.Average(),
                  ImagePath = i.ToList()
              }).ToList<Company>();

Answer 2

try this -

result = (from a in dc.GetTable<tblCompany>()
                      join b in dc.GetTable<tblRating>()
                      on a.Id equals b.CompanyId
                      join c in dc.GetTable<tblImages>()
                      on a.Id equals c.CompanyId
                      group new { b.Rate, c.ImagePath}
                      by new { a.Id, a.Location,a.Name} into groupList
                      select new Company
                      {
                          CompanyName = groupList.Key.Name,
                          Location = groupList.Key.Location,
                          AverageRate = groupList.Average(a=>a.Rate),
                          ImagePath = groupList.Select(i=>i.ImagePath).ToList()

                      }).ToList<Company>();