How can I query hierarchies with LinqToSQL?

Question

I have a hierarchy that I'd like to query with LinqToSql:

Country -> Region -> City -> ZipCode

Each entity holds both a reference to it's parent (eg. Region.Country) and a collection of it's children (eg. Region.Cities).

I'd like to eager load each entity's parent along with Countries and Regions but lazy load cities and zip codes.

To complicate things, each entity is being localized before being projected in to the model. So Country.Name changes based on the language.

Here's some snippets of what I have so far:

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

... etc.

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

The test fails with:

System.NotSupportedException: Method 'System.Linq.IQueryable`1[Beeline.EducationCompass.Model.Region] GetRegions(Int32)' has no supported translation to SQL.

How would you recommend I go about this? Would it be simpler (or possible) if each level of the hierarchy was in the same table instead of separate ones?

Answer 1

That's one sticky piece of code, and I wouldn't have answered this due to lack of relevant skill if anyone else had, but since you had no responses...

I can tell you what the error message means. It means the function GetRegions can't be translated into sql by the linq to sql provider. Some built-in functions can be, because the provider understands them, here is a list. Otherwise you can provide translations see here.

In your situation you need to 'inline' the logic of this query, the logic won't cross the boundary of a function call, because you are dealing with an expression tree, the sql server can't call back into your GetRegions method.

As to the exact way to do that, you'll have to have a go, I don't have the time to oblige you at the moment. (Unless someone else has time and skill?)

Good luck.

Answer 2

You're going to want to use the linq designer to set up relationships between your objects. This gets you out of writing join after join after join by creating properties.

• between a Country and its Regions
• between a Region and its Cities
• between a Country and its Localizations
• between a Region and its Localizations

You're going to want to use ToList to seperate those operations you intend to be translated into SQL, and those operations you intend to be done in local code. If you don't do this, you'll keep seeing those "cannot translate your method into SQL" exceptions.

You're also going to want to use DataLoadOptions to eagerly load these properties in some cases. Here's my stab at it.

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