Entity Framework One to One navigation property not loading

2
votes

So I have a simple database with two tables setup with code first using entity framework 6.02. The Submission table has a one to one relationship with the tbl_lst_Company table. They are related by the company and CompanyID fields.

public partial class Submission
{
    public Submission()
    {           
        this.Company = new tbl_lst_Company();
    }
    public int Keytbl { get; set; }
    public int companyid { get; set; }
    public virtual tbl_lst_Company Company { get; set; }        
}

public partial class tbl_lst_Company
{       
    public int CompanyID { get; set; }
    public string Company { get; set; }
    public virtual Submission Submission { get; set; }
}

And here are the Fluent mappings:

    public SubmissionMap()
    {
        // Primary Key
        this.HasKey(t => t.Keytbl);
        // Table & Column Mappings
        this.ToTable("Submissions");
        this.Property(t => t.Keytbl).HasColumnName("Keytbl");
        this.Property(t => t.companyid).HasColumnName("company");

        this.HasRequired(q => q.Company).
        WithOptional().Map(t => t.MapKey("Company"));
    }

    public tbl_lst_CompanyMap()
    {
        // Primary Key
        this.HasKey(t => t.CompanyID);

        // Properties
        this.Property(t => t.Company)
            .IsRequired()
            .HasMaxLength(150);
        // Table & Column Mappings
        this.ToTable("tbl_lst_Company");
        this.Property(t => t.CompanyID).HasColumnName("CompanyID");
        this.Property(t => t.Company).HasColumnName("Company");
    }

Here is my unit test I am running to test the above implementation:

    public void download_data() {
        var db = new SubmissionContext();
        db.Configuration.LazyLoadingEnabled = true;
        var subs = (from s in db.Submissions                     
                    select s).Take(100);
        var x = subs.First().Company;
        var a = subs.ToArray();            
    }

My problem is that the Company field is always null when I run this test. If I explicilty say from s in db.Submissions.Include("Company"), then the Company field is not null but I have to eager load the navigation property. I want the Company navigation property to lazy load. As far as I can tell I am doing everything the way it is suppose to be done but it is not working. What am I doing wrong?

Thanks

2
c#sqlentity-framework
Is the Company really null or is it an uninitialized object? I see that you instantiate the navigation property in the Submission constructor (this.Company = new tbl_lst_Company();) which is not good. You should remove this line in any case. However, I'm not sure if it will solve your particular problem here. - Slauma
The actual Company class is not null, but once expanded, all the properties within it are null. - Chris
Taking out the instantiation of Company, throws this error when view thing Company variable with the debugger: '(a[0]).Company' threw an exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' - Chris
Are there inner exceptions that tell more details about this EntityCommandExecutionException? What about the x in your example? Is it the expected company or null or do you get an exception on that line as well? - Slauma
Thanks, I think i figured it out. Indeed, instantiation a single navigation property causes the item to be always null with lazy loading. - Chris

2 Answers

1
votes

As I understand it you want x populated but you don't want to populate the company of every single submission in a.

You can tell it to load the company of the first one, then use it.

var first = subs.First();
first.CompanyReference.Load();
var x = first.Company;
var a = subs.ToArray();
1
votes

So I figured it out, there were a number of things wrong, but here is the solution that worked for me. You do not need to instantiate a single navigation property. This will cause it to be always null. You still need to instantiate the navigation property if it is an ICollection of objects. There were a few other minor things as well. Thanks for the help.

public partial class Submission
{       
    public int Keytbl { get; set; }
    public int Company { get; set; }
    public virtual tbl_lst_Company tbl_lst_Company{ get; set; }        
}

public partial class tbl_lst_Company
{       
public tbl_lst_Company() {
        this.Submissions = new List<Submission>();
}
    public int CompanyID { get; set; }
    public string Company { get; set; }
    public virtual ICollection<Submission> Submissions { get; set; }
}

public tbl_lst_CompanyMap()
{
    // Primary Key
    this.HasKey(t => t.CompanyID);

    // Properties
    this.Property(t => t.Company)
        .IsRequired()
        .HasMaxLength(150);
    // Table & Column Mappings
    this.ToTable("tbl_lst_Company");
    this.Property(t => t.CompanyID).HasColumnName("CompanyID");
    this.Property(t => t.Company).HasColumnName("Company");
}

public SubmissionMap()
{
    // Primary Key
    this.HasKey(t => t.Keytbl);
    // Table & Column Mappings
    this.ToTable("Submissions");
    this.Property(t => t.Keytbl).HasColumnName("Keytbl");
    this.Property(t => t.Company).HasColumnName("Company");

    this.HasOptional(t => t.tbl_lst_Company)
    .WithMany(t => t.Submissions)
.HasForeignKey(d => d.Company);
}

[TestMethod]
public void test_lazy_loading() {
    using (var db = new SubmissionContext()) {
    var subs = (from s in b.Submissions                     
                   select s);
    var x = subs.First().tbl_lst_Company;
    Assert.AreEqual(x, null, "Lazy Loading Failed");
    }
}