What can I use instead of an Include?

Question

Is there an alternative to using Include to eager load entities?

The reason I can't use Include is that it appears to be case sensitive.
Consider the following example:
I have two tables:

Notes the difference in case.

When I want to eager load Sager's Stamkartotek I use Include, but the Include doesn't load Stamkartotek:

** Update 1 **

I noticed this strange behavior - if I use any fields from Stamkartotek it joins correctly:

But if I go and only retrieve the value of Stam_nr instead of the whole object - it gives me A instead of a:

Research so far:

• The EF team knows about this problem - but have decided not to fix it.
• This guy has the same problem only using code-first - no solution has been found

Update 2
SQL genereted with Include:

FROM  [dbo].[Sager] AS [Extent1]
INNER JOIN [dbo].[Stamkartotek] AS [Extent2] ON [Extent1].[Klient_Stam_nr] = [Extent2].[Stam_nr]
WHERE 'jek15' = [Extent1].[Sags_nr]

Update 3
Loading them in seperate queries, and letting changetracker fixup the reference. It doesn't seem to work either:

Coltish

The MSDN Forum post is old. Did you check whether they fixed it in later versions of EF? If they have, you should upgrade your EF version to the one that fixes the issue. Don't expect fixes to be backported to previous EF versions, especially versions as old as 4.1 – Dacha

@PanagiotisKanavos I tried building my model using EF 6.0.1 but the problem is still there :| – Coltish

Then you should probably replace the columns you use for the relationship with ints. The problem isn't in Include, it's in the SQL generator when associations use text fields. Using text data for keys is an uncommon practice anyway (for the reasons you just encountered). Or, you could download the EF code (it's OSS) and fix it, if you have the time ... – Dacha

@PanagiotisKanavos hmmm the SQL generated looks correct (updated question) but the mapping of the result (into CLR classes) that's where I think the problem is. – Coltish

@Colin you should add that link to your answer - it is very relevant – Coltish

Answer 1

Create a view with LOWER around the foreign keys to ensure reads always return the same case to EF.

And do inserts and deletes using stored procedures

You can track and vote for the issue to be addressed here:

String comparison differences between .NET and SQL Server cause problems for resolving FK relationships in the state manager

Unmannerly

I have no doubt that this suggestion would work - however my question is a gross simplification of my real issue. In my program there are a lot of places this would need to be done - so I was hoping for a less "cumbersome" solution – Coltish

You get the bounty for pointing me to the official bug rapport - thanks :) – Coltish

Answer 2

I don't have all the information in this case, I think you have to update the relation of your table using integer keys to make the relation.

When using linq the query is going to execute to the database when you call a ToList() or First for example. If you use an Include, the query will load the data when some of this action is called.

The problem with the A or a can be a collation situation, check your configuration some collation ignore the case of the data.

I propose: Update the tables you are using with integer keys and use left outer joing if you want to load related data. Sometimes is better to use good old tsql(you can make the left outer join in linq too).

Megass

Thank you for anwsering :) Unfortunally I can't make EF use collation becuase it is not supported :| I need EF for changetracking, so I can't handroll the SQL either. Changing the table to use int instead of strings is a good idea :) but this is the core table of and old system - and I dare not change it :s – Coltish

Answer 3

Instead of using .Include you can load entities in separate queries. Change tracker will fix up relations for related entities it is already tracking and if you get your queries right you should get a solution that from the functional perspective is equivalent to using .Include.

Lyontine

Do I need to do anything else that just loading the entities? I can't get it to work - I have updated my question. (update 3) – Coltish

I just tried it and you are right - it won't work. I think this uses the same logic as .Include to find relationships. I thought you could 'fix' the keys in the .ObjectMaterialized event but it does not work either. Can't you update the data in database so that the casing is the same? – Lyontine

Thats properly the workaround we have to use. Thank you, for your input – Coltish