Tracking down a stack overflow error in my LINQ query

Question

I've written the following LINQ query:

IQueryable<ISOCountry> entries =
  (from e in competitorRepository.Competitors
   join c in countries on e.countryID equals c.isoCountryCode
   where !e.Deleted
   orderby c.isoCountryCode
   select new ISOCountry() { isoCountryCode = e.countryID, Name = c.Name }
  ).Distinct();

The objective is to retrieve a list of the countries represented by the competitors found in the system. 'countries' is an array of ISOCountry objects explicitly created and returned as an IQueryable<ISOCountry> (ISOCountry is an object of just two strings, isoCountryCode and Name). Competitors is an IQueryable<Competitor> which is bound to a database table through LINQ to SQL though I created the objects from scratch and used the LINQ data mapping decorators.

For some reason, this query causes a stack overflow when the system tries to execute it. I've no idea why, I've tried trimming the Distinct, returning an anonymous type of the two strings, using 'select c', but all result in the overflow. The e.CountryID value is populated from a dropdown that was in itself populated from the IQueryable<ISOCountry>, so I know the values are appropriate but even if not, I wouldn't expect a stack overflow.

Why is the overflow is occurring or why might it be happening?

As requested, code for ISOCountry:

public class ISOCountry
{
    public string isoCountryCode { get; set; }
    public string Name { get; set; }
}

It's initialised from a static utility class thus:

    public static IQueryable<ISOCountry> GetCountryCodes()
    {
        // ISO 3166-1 country names and codes from http://opencountrycodes.appspot.com/javascript
        ISOCountry[] countries = new ISOCountry[] {
            new ISOCountry { isoCountryCode= "AF", Name= "Afghanistan"},
            new ISOCountry { isoCountryCode= "AX", Name= "Aland Islands"},
            new ISOCountry { isoCountryCode= "AL", Name= "Albania"},
            new ISOCountry { isoCountryCode= "DZ", Name= "Algeria"},
            new ISOCountry { isoCountryCode= "AS", Name= "American Samoa"},
            ...
            new ISOCountry { isoCountryCode= "YE", Name= "Yemen"},
            new ISOCountry { isoCountryCode= "ZM", Name= "Zambia"},
            new ISOCountry { isoCountryCode = "ZW", Name = "Zimbabwe"}
        };
        return countries.AsQueryable();
    }

---

How I finally got it to work, see below... I am still curious as to what specifically is wrong with the original query, I'm sure I've done similar things before.

IList<string> entries = competitorRepository.Competitors.Select(c=>c.CountryID).Distinct().ToList();
IList<ISOCountry> countries = Address.GetCountryCodes().Where(a => entries.Contains(a.isoCountryCode)).ToList();

Answer 1

Maybe I'm crazy, but your utility class shouldn't be outputting an IQueryable list. You're creating a local sequence that looks like it should be queryable. Ultimately, IQueryable lists should be delved out by your datacontext. If a utility class is creating a list, that should be returned as (most likely) an array or an IEnumerable, for example:

    public static readonly ISOCountry[] CountryCodes = new ISOCountry[] {
        new ISOCountry { isoCountryCode= "AF", Name= "Afghanistan"},
        new ISOCountry { isoCountryCode= "AX", Name= "Aland Islands"}
        ...
    };

A local sequence can only be used in an IQueryable .Contains() statement. So, if you want to "mesh" your local sequence with your IQueryable sequence, you have to force the IQueryable to fire a SQL statement and grab the records it represents from the database. To do that, all you have to do is iterate over the IQueryable records in some fashion:

IList<Competitor> competitorRecords =  competitorRepository
   .Competitors
   .Where(m => !m.Deleted)
   .OrderBy(m => m.countryId)
   .ToList(); //This fires the SQL statement

Once you've snagged the records from the database, you can create your list of ISOCountry records. Again, since this list isn't coming from your datacontext, it shouldn't be an IQueryable list. Instead, try this:

IList<ISOCountry> = competitorRecords
    .Join(CountryCodes, key1 => key1.countryId, key2 => key2.isoCountryCode, (competitors, codes) => new ISOCountry { isoCountryCode = competitors.countryId, Name = codes.Name })
    .ToList();

This will work, but you're probably grabbing unnecessary records from the database. It'd be even better if you could upload your ISOCountry list to the database. Once you do that, you'd be able to fire the query as you initially conceived it.