Entity Framework 6 navigation property loading not working

Question

Been stuck for hours with this weird problem. For some reason entity framework wont load the navigation properties of this 1 to many relationship, even though the generated SQL seems to be right, foreign key exists from SubItems to FrontPageItems:

CONSTRAINT [FK_dbo.SubItems_dbo.FrontPageItems_FrontPageItemId] 
    FOREIGN KEY ([FrontPageItemId]) 
        REFERENCES [dbo].[FrontPageItems] ([Id]) ON DELETE CASCADE

I have tried loading all frontpageitems with: _repo.Get();, but even though the SubItem table contains foreign keys referencing to FronPageItems table, no navigation properties gets loaded.

public class FrontPageItem : Logger, IEntity, IIsPublished
{
    public FrontPageItem()
    {
        SubItems = new HashSet<SubItem>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public bool IsPublished { get; set; }
    public virtual ICollection<SubItem> SubItems { get; set; }
}

    public class SubItem : Logger, IEntity, IIsPublished
{
    public SubItem()
    {
        FrontPageItem = new FrontPageItem();
    }
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string YoutubeUrl { get; set; }
    public virtual FrontPageItem FrontPageItem { get; set; }

    public int FrontPageItemId { get; set; }

    public bool IsPublished { get; set; }
}
public class SampleContext : IdentityDbContext<ApplicationUser>
{
    // throwIfV1Schema is used when upgrading Identity in a database from 1 to 2.
    // It's a one time thing and can be safely removed.
    public SampleContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
    }

    public static SampleContext Create()
    {
        return new SampleContext();
    }

    // Define you conceptual model here. Entity Framework will include these types and all their references. 
    public IDbSet<FrontPageItem> FrontPageItem { get; set; }
    public IDbSet<SubItem> SubItems { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // The DateTime type in .NET has the same range and precision as datetime2 in SQL Server.
        // Configure DateTime type to use SQL server datetime2 instead.
        modelBuilder.Properties<DateTime>().Configure(c => c.HasColumnType("datetime2"));

        base.OnModelCreating(modelBuilder);
    }
}

Btw it works if I remove the foreignkey property in SubItem:

public int FrontPageItemId { get; set; }

I have tried eager loading with include like this:

        var frontPageItems = _repo.AsQueryable().Include(o => o.SubItems).ToList();

What is _repo.Get()? Why do you expect it to load related entities for you automatically? Do you have lazy loading enabled? — DavidG
– DavidG, Commented Dec 4, 2015 at 9:35
Where do you tell the EF how it should handle your relation? I am missing something like HasRequired(si=> si.FrontPageItem ) .WithMany(fpi=> fpi.SubItems) .HasForeignKey(si=> si.FrontPageItemId ); — Dom84
– Dom84, Commented Dec 4, 2015 at 9:39
I use conventions, and it seems to generate the right SQL: CONSTRAINT [FK_dbo.SubItems_dbo.FrontPageItems_FrontPageItemId] FOREIGN KEY ([FrontPageItemId]) REFERENCES [dbo].[FrontPageItems] ([Id]) ON DELETE CASCADE — lyzz
– lyzz, Commented Dec 4, 2015 at 10:00

Batavia · Accepted Answer · 2015-12-04 09:58:22Z

2

As DavidG mentioned, lazy loading is the default en EF. that means the properties only get loaded once you actually use them.

In order force inclusion of subitems you can use include like this

FrontPageItem.Include(x => x.SubItems).FirstOrDefault().

FrontPageItem here is your dbset. If you query it like this it will load the first frontpageitem with all subitems connected to it.

Update: If you follow the conventions stricktly you won't need to do this but if you have different names for your properties or want to have an explicit foreign key then you can configure the foreign key like this

modelBuilder.Entity<SubItem>() 
  .HasRequired(t => t.FrontPageItem) 
  .WithMany(t => t.SubItems) 
  .HasForeignKey(d => d.FrontPageItemId) 
  .WillCascadeOnDelete(true);

edited Dec 4, 2015 at 9:58

answered Dec 4, 2015 at 9:38

Batavia

2,49716 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

lyzz Over a year ago

Seems like conventions should handle this: msdn.microsoft.com/en-us/data/jj679962.aspx Btw i tried the Include trick. It didnt work, and it works now if i remove the public int FrontPageItemId { get; set; } property and let EF generate this foreignkey for me

Batavia Over a year ago

you're right whitelatino, i updated my answer. You can use the modelbuilder part if you want to specify your own foreign key instead of letting EF generate it for you. otherwise convention should take care of it for you.

lyzz Over a year ago

Btw _repo is a generic repository tugberkugurlu.com/archive/… Im sorry i tried all of this and none worked. I have read the top posts about this on google, nothing seems explain why my foreign key property is making me not being able load the navigation property.

Batavia Over a year ago

If you have specific code that isn't working maybe you can update the question. Also can you show you exactly you tried your include trick if it's a generic repository?

lyzz Over a year ago

I have updated, and to make thinks clear, it all works when i am not declaring a foreignkey property in SubItems, and that is the big mystery. It even works in serverexplore, since I cannot choose a SubItemId on FrontPageItems that doesnt exists.

Bilel Chaouadi · Accepted Answer · 2015-12-04 10:13:41Z

1

LazyLoadingEnabled must be true, not false:

context.Configuration.LazyLoadingEnabled = true; true is the default if you don't set LazyLoadingEnabled at all.

And the SubItems property must be virtual to enable lazy loading for this property.

Or you can include the property directly in the query. You also need to set the FKby using the modelBuilder

    modelBuilder.Entity<SubItem>() 
  .HasRequired(t => t.FrontPageItem) 
  .WithMany(t => t.SubItems) 
  .HasForeignKey(d => d.FrontPageItemId) 
  .WillCascadeOnDelete(true);

Or by configuring Relationships using attributes

[ForeignKey("FrontPageItem")]
 public virtual FrontPageItem FrontPageItem { get; set; }

answered Dec 4, 2015 at 10:13

Bilel Chaouadi

9331 gold badge11 silver badges31 bronze badges

1 Comment

lyzz Over a year ago

Sorry my conventions do exacly the same as your fluent api statement / Data Annotation.

lyzz · Accepted Answer · 2015-12-04 12:23:18Z

0

    public SubItem()
{
    FrontPageItem = new FrontPageItem();
}

Was the problem. For some reason the constructor in SubItem was created this weird behavour, everything else was fine.

answered Dec 4, 2015 at 12:23

lyzz

1382 silver badges9 bronze badges

3 Comments

dragonfly02 Over a year ago

What's wrong with constructor? Not sure I understand what you mean by "Was the problem"...

lyzz Over a year ago

Removing this constructor made me able to load navigation properties.

dragonfly02 Over a year ago

Ah ok. This is interesting and I hope some EF expert can explain why such a constructor would disable the navigation property loading. I guess it might override some default behaviour of EF but I would like to know the details of how EF implementation works. Why did you want to initialise the principle object anyway?

Collectives™ on Stack Overflow

Entity Framework 6 navigation property loading not working

3 Answers 3

5 Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related