Why would a datetime prevent a navigation property from getting loaded?

Question

I am using Entity Framework 4.3.1 using the DbContext POCO approach against a SQL Server 2012 database.

I have just two tables in the database and they look like this:

NOTE: There are no foreign keys specified in the database at all - I am only enforcing the relationship in the model (I cannot change the database).

They each have one row of data in them that looks like this:

I performed the following query to ensure the join would work:

Now, I have the following entities:

public class Two
{
    public long TwoId { get; set; }
    public string OneId { get; set; }
    public virtual One One { get; set; }
}

public class One
{
    public string OneId { get; set; }
    public DateTime DeliveryDate { get; set; }
    public virtual ICollection<Two> Twos { get; private set; }

    public void AddTwo(Two two)
    {
        if (two == null)
            throw new ArgumentNullException("two");

        if (Twos == null)
            Twos = new List<Two>();

        if (!Twos.Contains(two))
            Twos.Add(two);

        two.One = this;
    }
}

And this is the context:

public class TestContext : DbContext
{
    public TestContext(string conectionString)
        : base(conectionString)
    {
        Configuration.LazyLoadingEnabled = true;
        Ones = Set<One>();
        Twos = Set<Two>();
    }
    public DbSet<One> Ones { get; private set; }
    public DbSet<Two> Twos { get; private set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var one = modelBuilder.Entity<One>();
        one.ToTable("One");
        one.HasKey(d => d.OneId);

        var two = modelBuilder.Entity<Two>();
        two.ToTable("Two");
        two.HasKey(d => new
                            {
                                d.OneId,
                                d.TwoId
                            });
        two.Property(p => p.TwoId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        two.HasRequired(t => t.One)
            .WithMany(o => o.Twos)
            .HasForeignKey(o => o.OneId);
        base.OnModelCreating(modelBuilder);
    }
}

When I run this bit of code I get Why is this printed? printed to my console - which I do not expect as you can see that the navigation property should be filled in (I even explicitly included it):

using (var ctx = new TestContext(@"......"))
{
    const string oneId = "111348718";
    var one = ctx.Ones.Single(o => o.OneId.Equals(oneId));

    if (one != null)
    {
        var sdi = ctx
            .Twos
            .Include(s => s.One)
            .Single(s => s.OneId.Equals(oneId));

        if (sdi.One == null)
        {
            Console.WriteLine("Why is this printed?");
        }
        else
        {
            Console.WriteLine("This is what I expect");
        }
    }
}

Now, this is the really odd bit: If I simply comment out the DeliveryDate property from the One class it works fine (I get This is what I expect printed to the console).

What's wrong here and how can I solve it?

NOTE: If I look at the DeliveryDate property on the one variable it has been correctly set to the expected value so the date must be OK in the database and entity framework is perfectly able to materialize it.

More Information: The fact that it is a date does not matter - if it is, say an nvarchar it still fails - seems any simple property causes the whole thing to fall over - this feels like a bug to me...

Overlie

Answer 1

OK, so I have got to the bottom of this and I think this is a bug in Entity Framework, but I can work around it. Here is what it is...

The OneId column in the Two table had a single space at the end.

Yup - go figure - how horribly nasty is that!?

So I knocked up a sql script that did an LTRIM(RTRIM( on every nvarchar column in the database and all is working fine.

Now, given SQL server doesn't care about the spaces (as proven by the query with the join in the question) I do not really like that Entity Framework does care, especially as it is totally weird and inconsistent here. Surely the point where I call Single to get the sdi variable populated should have thrown or the navigation property should have been populated - one or the other but this behaviour is just really confusing (in my opinion) - what has some arbitrary property got to do with the price of fish??

Just for the sake of completeness here is something to reproduce this really simply. Create a new database and then run this SQL script on it:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[One](
    [OneId] [nvarchar](10) NOT NULL,
    [SomeInt] [int] NOT NULL,
 CONSTRAINT [PK_Delivery] PRIMARY KEY CLUSTERED 
(
    [OneId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Two](
    [TwoId] [int] NOT NULL,
    [OneId] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_DeliveryItem] PRIMARY KEY CLUSTERED 
(
    [TwoId] ASC,
    [OneId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO Two(TwoId, OneId) VALUES (1, '1 ')
INSERT INTO One(OneId, SomeInt) VALUES ('1', 1.0)

Now create a c# console application, add a reference to the EntityFramework (version 4.3.1) and the System.Data.Entity assemblies, put this code in and run it - it will print SHOULD NOT BE PRINTED!!!.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Linq;

namespace EFTest
{
    public class Two
    {
        public int TwoId { get; set; }
        public string OneId { get; set; }
        public virtual One One { get; set; }
    }

    public class One
    {
        public string OneId { get; set; }
        public virtual ICollection<Two> Twos { get; private set; }

        // Comment out this property and it will work
        public int SomeInt { get; set; }

        public void AddTwo(Two two)
        {
            if (two == null)
                throw new ArgumentNullException("two");

            if (Twos == null)
                Twos = new List<Two>();

            if (!Twos.Contains(two))
                Twos.Add(two);

            two.One = this;
        }
    }

    public class Context : DbContext
    {
        public Context(string connectionString)
            : base(connectionString)
        {
            Configuration.LazyLoadingEnabled = true;
            Ones = Set<One>();
            Twos = Set<Two>();
        }

        public DbSet<One> Ones { get; private set; }
        public DbSet<Two> Twos { get; private set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder
                .Entity<One>()
                .HasKey(d => d.OneId)
                .ToTable("One");

            var two = modelBuilder.Entity<Two>();
            two.ToTable("Two");
            two.HasKey(d => new
                                {
                                    d.OneId,
                                    d.TwoId
                                });

            two.Property(d => d.TwoId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

            two.HasRequired(m => m.One)
                .WithMany(t => t.Twos)
                .HasForeignKey(d => d.OneId);

            base.OnModelCreating(modelBuilder);
        }
    }

    internal class Program
    {
        private static void Main()
        {
            using (var ctx = new Context(@"your connection string"))
            {
                const string oneId = "1";
                var one = ctx.Ones.Single(o => o.OneId.Equals(oneId));

                if (one == null)
                {
                    Console.WriteLine("No row with one ID in the database");
                    return;
                }

                var two = ctx
                    .Twos
                    .Include(s => s.One)
                    .Single(s => s.OneId.Equals(oneId));

                Console.WriteLine(two.One == null
                                      ? "SHOULD NOT BE PRINTED!!!"
                                      : "SHOULD BE PRINTED");
            }
        }
    }
}

Then do one of these:

• Comment out the SomeInt property on the One class
• Trim off the space in the database (UPDATE Two SET OneId = RTRIM(OneId)).

Either will work (obviously the trimming is the only reasonable real-life fix).

Overlie