如何使用 LinqToSQL 查询层次结构？

Question

我有一个层次结构，我想使用 LinqToSql 进行查询：

Country -> 地区-> 城市-> 邮政编码

每个实体都保存对其父级（例如 Region.Country）的引用及其子级（例如 Region.Cities）的集合。

我想立即加载每个实体的父级以及国家和地区，但延迟加载城市和邮政编码。

让事情变得复杂的是，每个实体在投影到模型中之前都要进行本地化。 因此 Country.Name 根据语言而变化。

以下是我到目前为止所拥有的一些片段：

public IQueryable<Country> ListCountries()
{
  return ProjectCountry(dataContext.GetTable<ec_Country>());
}

private IQueryable<Country> ProjectCountry(IQueryable<ec_Country> query)
{
  var result = from country in query
  join localized in dataContext.GetTable<ec_CountryLocalization>() on country.CountryID equals localized.CountryID
  let regions = GetRegions(country.CountryID)
  where localized.StatusID == 4 && localized.WebSiteID == this.webSiteID
  select new Country(country.CountryID) {
    CreatedDate = country.CreatedDate,
    IsDeleted = country.IsDeleted,
    IsoCode = country.IsoCode,
    Name = country.Name,
    Regions = new LazyList<Region>(regions),
    Text = localized.Text,
    Title = localized.Title,
    UrlKey = country.UrlKey
  };

  return result;
}

private IQueryable<Region> GetRegions(Int32 countryID)
{
  var query = from r in dataContext.GetTable<ec_Region>()
  where r.CountryID == countryID
  orderby r.Name
  select r;

  return ProjectRegion(query);
}

private IQueryable<Region> ProjectRegion(IQueryable<ec_Region> query)
{
  var result = from region in query
  join localized in dataContext.GetTable<ec_RegionLocalization>() on region.RegionID equals localized.RegionID
  join country in ListCountries() on region.CountryID equals country.CountryID
  let cities = GetCities(region.RegionID)
  select new Region(region.RegionID) {
    Cities = new LazyList<City>(cities),
    Country = country,
    CountryID = region.CountryID,
    CreatedDate = region.CreatedDate,
    IsDeleted = region.IsDeleted,
    IsoCode = region.IsoCode,
    Name = region.Name,
    Text = localized.Text,
    Title = localized.Title,
    UrlKey = region.UrlKey
  };

  return result;
}

...等。

[TestMethod]
public void DataProvider_Correctly_Projects_Country_Spike()
{
  // Act
  Country country = dataProvider.GetCountry(1);

  // Assert
  Assert.IsNotNull(country);
  Assert.IsFalse(String.IsNullOrEmpty(country.Description));
  Assert.IsTrue(country.Regions.Count > 0);
}

测试失败并显示：

System.NotSupportedException: Method 'System.Linq.IQueryable`1[Beeline.EducationCompass.Model.Region] GetRegions(Int32)' has no support翻译成SQL。

你会建议我如何去做这件事？ 如果层次结构的每个级别都在同一个表中而不是单独的表中，会更简单（或可能）吗？

Answer 1

您将需要使用 linq 设计器来设置对象之间的关系。 这使您不必通过创建属性来编写一个又一个的连接。

• 国家及其地区
• 之间 地区及其城市
• 之间 国家及其本地化
• 之间 地区及其本地化之间

您需要使用 ToList 来分隔您打算转换为 SQL 的操作和您打算在本地代码中完成的操作。 如果您不这样做，您将不断看到那些“无法将您的方法转换为 SQL”的异常。

您还需要使用 DataLoadOptions 在某些情况下急切地加载这些属性。 这是我的尝试。

DataLoadOptions dlo = new DataLoadOptions();
//bring in the Regions for each Country
dlo.LoadWith<ec_Country>(c => c.Regions);
//bring in the localizations
dlo.AssociateWith<ec_Country>(c => c.Localizations
  .Where(loc => loc.StatusID == 4 && loc.WebSiteID == this.webSiteID)
);
dlo.AssociateWith<ec_Region>(r => r.Localizations);

//set up the dataloadoptions to eagerly load the above.
dataContext.DataLoadOptions = dlo;

//Pull countries and all eagerly loaded data into memory.
List<ec_Country> queryResult = query.ToList();

//further map these data types to business types
List<Country> result = queryResult
  .Select(c => ToCountry(c))
  .ToList();

---

public Country ToCountry(ec_Country c)
{
  return new Country()
  {
    Name = c.Name,
    Text = c.Localizations.Single().Text,
    Regions = c.Regions().Select(r => ToRegion(r)).ToList()
  }
}

public Region ToRegion(ec_Region r)
{
  return new Region()
  {
    Name = r.Name,
    Text = r.Localizations.Single().Text,
    Cities = r.Cities.Select(city => ToCity(city)).ToLazyList();
  }
}

Answer 2

这是一段粘性代码，如果其他人有的话，由于缺乏相关技能，我不会回答这个问题，但由于您没有回复......

我可以告诉您错误消息的含义。 这意味着函数 GetRegions 无法由 linq to sql 提供程序转换为 sql。 一些内置函数可以，因为提供者理解它们，这里有一个 列表。 否则，您可以提供翻译，请参阅此处。

在您的情况下，您需要“内联”此查询的逻辑，逻辑不会跨越函数调用的边界，因为您正在处理表达式树，sql 服务器无法回调到您的 GetRegions 方法。

至于具体的方法，你得去尝试一下，我现在没时间强迫你。 （除非其他人有时间和技能？）

祝你好运。