Virtual property won't lazy load in Entity Framework 6 (with caveat)

Question

I'm stuck...

Here's my model:

public class Payment 
{
    [ForeignKey("RecipientId")]
    public virtual Account Recipient { get; set; }

    public string RecipientId { get; set; } 

    [Key, Column(TypeName = "char"), MaxLength(36)]
    public string PaymentId { get; set; }
}

And this model is represented through the fluent API as the following:

modelBuilder.Entity<Payment>()
    .HasRequired(t => t.Recipient)
    .WithMany()
    .HasForeignKey(t => t.RecipientId)
    .WillCascadeOnDelete(false);

This has worked for nearly 3 years. We've never had problems with this mapping.

Recently, we decided to change the "MaxLength" attribute to what you see above. It went from "8" to "36".

Our previous ID's started to collide and thus it was time to change.

Changing everything in code was fine. Our unit tests are all passing. When we run our project locally we see the correct results.

Pushing to production was also pretty nominal. We altered our table and readjusted our index to reflect the new column type length (from MaxLength(8) to MaxLength(36)).

Again... This was the same procedure we performed locally.

Here comes the issue:

Entities (Payment) that are created and saved with the new, longer, "PaymentId's" are done-so without error. Adding the relationship also seems to be working without error.

The error comes when we perform any one of the query APIs in entity framework (Find, ToList, SingleOrDefault, etc...).

Our lazy loading isn't working. The recipients are null! However, when tracing the query we notice that SQL is in-fact returning the data. For some reason, entity framework isn't creating the proxy and any reference to Payment.Recipient is null.

Furthermore, when we perform a request on an existing ID (one prior to our length change) we get the results without problem.

So the questions:

1. Why work locally (both ID lengths) but not remote (on production)?

   • What type of db settings could their be that would interfere with this change.

2. The raw SQL execution works in production but not in creating the poxy EF, why?

Any help would be awesome!

-- adding raw sql --

Bad request

=========================================================
ID: 'bf08bdf4-a9d5-42e0-9236-a65faa4cc6ea                  '
=========================================================
SELECT 
    [Extent1].[TransactionId] AS [TransactionId],
    [Extent1].[RecipientId] AS [RecipientId],
    [Extent2].[AccountId] AS [AccountId], 
    [Extent2].[Name] AS [Name],
    FROM   [dbo].[Payment] AS [Extent1]
    INNER JOIN [dbo].[Account] AS [Extent2] ON [Extent1].[RecipientId] = [Extent2].[AccountId]
=========================================================
Recipient is null...

Good Request

=========================================================
ID: 'f0e9beef                               ' 
=========================================================
SELECT 
    [Extent1].[TransactionId] AS [TransactionId],
    [Extent1].[RecipientId] AS [RecipientId],
    [Extent2].[AccountId] AS [AccountId], 
    [Extent2].[Name] AS [Name],
    FROM   [dbo].[Payment] AS [Extent1]
    INNER JOIN [dbo].[Account] AS [Extent2] ON [Extent1].[RecipientId] = [Extent2].[AccountId]
=========================================================
Recipient is not null!
RecipientId: '835cbb56                          '    
Name: John Doe

---

!! Update !!

I'm investigating the results from the RAW sql and am finding that the there are extra spaces being saved on the newly changed properties...

I've stumbled on this post by Ro Miller:

http://romiller.com/2014/10/20/ef6-1workaround-trailing-blanks-issue-in-string-joins/

Communicating that string truncation could be an issue... Let's find out...

Answer 1

Yup,

After changing the length of the ID field I was getting unwanted "whitespace" on the return of a LINQ to Entity query.

By following Ro Miller's interceptor guide I was able to guarantee that all string fields were being trimmed on the way up to the database.

http://romiller.com/2014/10/20/ef6-1workaround-trailing-blanks-issue-in-string-joins/

Its important to note that the inceptor capability was only recently made available in EF6.1.

Hope that helps someone... I spent 11 hours on this today.