c# - 带有 Average 的 Linq 查询

Question

我有 3 个表:

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

我有课

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; }
}

我想要一个 LINQ 查询来产生一个匹配 Company 类的结果。

我写了这个查询但是它不起作用

        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

为整个查询编辑:

(我不得不说我很抱歉，但我还没有办法测试这个查询)

您可以使用 let clause而不是连接:

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>();