6

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:

enter image description here

enter image description here

Notes the difference in case.

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

enter image description here

** Update 1 **

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

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

enter image description here

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:
enter image description here

Community
  • 1
  • 1
Jens Kloster
  • 11,099
  • 5
  • 40
  • 54
  • 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 – Panagiotis Kanavos Nov 25 '13 at 09:39
  • @PanagiotisKanavos I tried building my model using [EF 6.0.1](http://entityframework.codeplex.com/releases/view/112029) but the problem is still there :| – Jens Kloster Nov 25 '13 at 09:49
  • 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 ... – Panagiotis Kanavos Nov 25 '13 at 09:54
  • @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. – Jens Kloster Nov 25 '13 at 10:03
  • @Colin you should add that link to your answer - it is very relevant – Jens Kloster Nov 28 '13 at 07:28

3 Answers3

3

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

Colin
  • 22,328
  • 17
  • 103
  • 197
  • 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 – Jens Kloster Nov 27 '13 at 07:38
  • You get the bounty for pointing me to the official bug rapport - thanks :) – Jens Kloster Nov 28 '13 at 10:07
1

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).

Juan
  • 1,352
  • 13
  • 20
  • Thank you for anwsering :) Unfortunally I can't [make EF use collation](http://stackoverflow.com/q/14631035/1244816) 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 – Jens Kloster Nov 26 '13 at 07:49
1

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.

Pawel
  • 31,342
  • 4
  • 73
  • 104
  • 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) – Jens Kloster Nov 27 '13 at 07:34
  • 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? – Pawel Nov 27 '13 at 17:59
  • Thats properly the workaround we have to use. Thank you, for your input – Jens Kloster Nov 28 '13 at 07:25