Why does datetime prevent the loading of a navigation property?

Question

I am using Entity Framework 4.3.1 using the DOCContext POCO approach to SQL Server 2012 database.

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

NOTE. There are no foreign keys specified in the database at all - I only observe the relations in the model (I cannot change the database).

Each of them has one row of data that looks like this:

I ran the following request to make sure the connection would work:

Now I have the following objects:

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 on my console, which I do not expect, as you can see that the navigation property should be filled (I even explicitly enabled 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 really an odd bit . If I just comment on the DeliveryDate property from the One class, it works fine (I get This is what I expect printed on the console).

What is wrong here and how can I solve it?

NOTE. If I look at the DeliveryDate property in the One variable, it was correctly set to the expected value, so the date should be ok in the database, and the entity structure is quite capable of materializing it.

Additional information: The fact that this is a date does not matter - if it is, say, nvarchar, it still fails - it seems that any simple property makes it all fall down - it seems like an error for me ...

Answer 1

OK, so I got to the end, and I think this is a bug in the Entity Framework, but I can get around this. That's what it is ...

The OneId column in Table Two had one place at the end.

Yup - go figure - how terribly unpleasant is it !?

So, I got out a sql script that made LTRIM(RTRIM( in each nvarchar column in the database, and everything works fine.

Now, given that the SQL server does not care about spaces (as proved in the query with the connection in the question), I don’t really like that the Entity Framework will take care, especially since it is completely strange and inconsistent here. Of course, the point at which I call Single to get the filled sdi variable should have been thrown out or the navigation property should be filled out - one or the other, but this behavior is just confused (in my opinion) - which has arbitrary property related to the price of the fish ?

Just for completeness, there is something here to reproduce it is really simple. 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 link to EntityFramework (version 4.3.1) and the System.Data.Entity assembly, paste this code 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 the following:

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

Or it will work (obviously, cropping is the only reasonable real fix).