Tracking error in my LINQ query

Question

I wrote 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 goal is to get a list of countries represented by competitors found in the system. 'countries' is an array of ISOCountry objects explicitly created and returned as IQueryable <ISOCountry> (ISOCountry is a two-line object, isoCountryCode and Name). Competitors is an IQueryable <Competitor> that is bound to a database table via LINQ to SQL , although I created objects from scratch and used LINQ data display decoders.

For some reason, this request causes a stack overflow when the system tries to execute it. I have no idea why, I tried Distinct trimming, returning the anonymous type of the two strings using "select c", but all this leads to overflow. The value of e.CountryID is populated from the drop-down list, which was itself populated from the IQueryable <ISOCountry>, so I know that the values ​​are appropriate, but even if not, I would not expect it.

Why does overflow occur or why can it happen?

As requested, the code for ISOCountry:

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

It is initialized from the static utility class:

  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'm still wondering what exactly is wrong with the original request, I'm sure I did 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

I may be crazy, but your utility class should not list IQueryable. You create a local sequence that looks as if it should be queried. Ultimately, IQueryable lists must be processed by your datacontext. If the utility class creates a list, it should be returned as (most likely) an array or IEnumerable, for example:

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

The local sequence can only be used in the IQueryable.Contains () statement. So, if you want to “connect” your local sequence to your IQueryable sequence, you need to get IQueryable to run the SQL statement and grab the records it represents from the database. To do this, you just need to iterate over the IQueryable entries:

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

Once you hooked records from the database, you can create your ISOCountry record list. Again, since this list does not come from your datacontext, it should not be an IQueryable list. Instead, try the following:

 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 are probably grabbing unnecessary records from the database. It would be even better if you could upload your ISOCountry list to the database. Once you do this, you will be able to run the request as you originally intended.