Entity Framework Code First Foreign Key Problems

Question

I am trying to use EF Code First on an existing database. I first tried some of the reverse-engineering tools, but I ran into problems with that, so at the moment I am trying to hand-code some of the classes. I am having some trouble getting some of the foreign key relationships set up. Consider two tables. The first is called LocaleValueLookup:

public class LocaleValueLookup
{
    public int Id { get; set; }
    public Guid Guid { get; set; }
}

This table provides an Id for multi-language text held in a different table (that other table is not important for the purposes of this question). The second table is called SectionType, and it has an optional FK to LocaleValueLookup:

public class SectionType
{
    public int EnumId { get; set; }
    public string Name { get; set; }
    public int? DefaultSectionTextLocaleValueLookupId { get; set; }

    // Navigation property
    public LocaleValueLookup DefaultSectionTextLocaleValueLookup { get; set; }
}

I have tried various things, including adding a [ForeignKey] attribute to the SectionType.LocaleValueLookup property, and various incantations in the DbContext.OnModelCreating() override, but when I query the DbContext, I can't get the DefaultSectionTextLocaleValueLookup to be anything but null. I can retrieve other objects from the context just fine, and I have verified that DefaultSectionTextLocaleValueLookupId is not null at least some of the time.

My OnModelBuilding() contains the following:

modelBuilder.Entity<LocaleValueLookup>()
    .ToTable("LocaleValueLookup")
    .HasKey(lvl => lvl.Id);
modelBuilder.Entity<LocaleValueLookup>().Property(lvl => lvl.Id).IsRequired();

modelBuilder.Entity<SectionType>()
    .ToTable("SectionType")
    .HasKey(st => st.EnumId);
modelBuilder.Entity<SectionType>().Property(st => st.EnumId).IsRequired();

A couple of other points:

I would prefer not to have a SectionType collection on the LocaleValueLookup object. LocaleValueLookup is a low-level class that a lot of other classes depend on, so to include a collection property on LocaleValueLookup for every other class that references it will make for an unwieldy class with a lot of collections on it that I don't need from a domain perspective.
I would prefer to do the mapping setup in DbContext.OnModelCreating() rather than using attributes on my model objects

Any help would be greatly appreciated!

Dismissile · Accepted Answer · 2014-04-15 17:01:49Z

1

It looks like your foreign key is nullable so that means an optional -> many relationship.

Could you try something like this:

modelBuilder.Entity<SectionType>()
    .HasOptional(opt => opt.DefaultSectionTextLocaleValueLookup)
    .WithMany() // no navigation on the other side
    .HasForeignKey(fk => fk.DefaultSectionTextLocaleValueLookupId);

If you were to write a query like this you should get a value back:

var query = 
    from st in db.SectionTypes
    where st.EnumId == 12345
    select new
    {
        SectionType = st,
        LocaleValue = st.DefaultSectionTextLocaleValueLookup
    };

It will only be non-null if the foreign key has a value, obviously.

answered Apr 15, 2014 at 17:01

Dismissile

33.1k40 gold badges179 silver badges269 bronze badges

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

4 Comments

Eric Pohl Over a year ago

I get an error doing it that way: Invalid column name 'LocaleValueLookup_Id'. I would have thought that the HasForeignKey call would have handled that. The SQL being run is this: SELECT TOP (1) [Extent1].[EnumId] AS [EnumId], [Extent1].[Name] AS [Name], [Extent1].[DefaultSectionTextLocaleValueLookupId] AS [DefaultSectionTextLocaleValueLookupId], [Extent1].[LocaleValueLookup_Id] AS [LocaleValueLookup_Id] FROM [dbo].[SectionType] AS [Extent1] WHERE 3 = [Extent1].[EnumId]

Dismissile Over a year ago

Did you remove the [ForeignKey] attribute from the entity?

Eric Pohl Over a year ago

Yes I did; there are no [ForeignKey] annotations on the SectionType table.

Eric Pohl Over a year ago

Not sure why your solution didn't work originally, but when I started a new project from scratch, it worked just fine.

Collectives™ on Stack Overflow

Entity Framework Code First Foreign Key Problems

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related