Linq查询与平均值

Jmocke 发表于 Dev

乔莫克

我有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>();
        }

马塞尔·B

编辑整个查询：

（我不得不说很抱歉，但我尚无法测试此查询）

您可以使用let子句代替联接：

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