Today I learned an interesting thing about Entity Framework I had not experienced in the past. Many people use a single integer value as a primary key for every table. Joining to that table is then done using a foreign key, again as an integer.
I am implementing tables with language and country codes, utilizing the ISO standards for each, and using the two character code assigned by ISO as the primary and foreign keys. You actually see these values when you look at internationalized products. For example, the code ‘en’ is used for the language English, and the code ‘US’ represents the country United States of America. I’m sure you have seen this represented as en-US from time to time. en-CA represents Canadian English, and fr-CA represents Canadian French.
Notice that these codes are case specific. This helps reduce confusion between language and country codes. Language codes are all lower case; country codes are all upper case. I have these codes and descripts all in tables using a CHAR(2) datatype. My collation is not case specific. So, en and EN should join as the same thing. In fact, it does if you run an SQL query, when the foreign key value does not match the case of the primary key value.
But, Entity Framework does not see it this way. I have a table with valid combinations of language and country. I am
returning from that table (Languages) and including the ISO lookup value for each language.
var langageList = Context.DbSet
.Include(t => t.ISOLanguage)
.Include(t => t.ISOCountry)
.ToList();
When I would run this query, I would always have a null ISOLanguage object in my results for each row. I knew the data was there because running an SQL query always returned a complete set. Finally I found that all the foreign key values in my Languages table (Language object) for CountryCode were lower case. So, even though my SQL worked fine, and running the query generated from Entity Framework returned all of the data, when Entity Framework adapted data results from SQL Server into Dot Net objects, it still did a Dot Net string comparison for the key values, which is CASE SPECIFIC.
Updating the foreign key in my Languages table resolved the problem. Now I am able to retrieve all Language combinations with accuracy.
Cheers,
Ben