How to map foreign key property to database in EF6 automatically

c# entity-framework entity-framework-6

Question

In my database I have two tables:

Scenario:

Id
Name
Location_Id

Locations:

Id
Name

So Scenario has a foreign key to Locations.

In the code I have this:

Scenario.cs:

public int Id { get; set; }
public string Name { get; set; }
public int LocationId { get; set; }

Location.cs

public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection<Screen> Screens { get; set; } // I do not really need this...

When I use it like this I get an exception:

SqlException: Invalid column name 'LocationId'

But, when I use public virtual Location { get; set; } in my Scenario class, it off course recognizes the relationship.

What I would like is that EF automatically 'maps' LocationId to Location_Id. I don't want LocationId in my database because other foreign keys are using the underscore: Entity_Id. So I don't want to configure each foreign key like this:

modelBuilder.Entity<Scenario>().Property(s => s.LocationId).HasColumnName("Location_Id");
1
0
4/14/2020 2:06:03 PM

Accepted Answer

You can use custom code first conventions.

Here's a convention that looks for properties ending with Id and at least one character preceding Id. The convention replaces the names by <preceding part> + _ + Id.

using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Text.RegularExpressions;
...
class ForeignKeyPropertyConvention : IStoreModelConvention<EdmProperty>
{
    public void Apply(EdmProperty property, DbModel model)
    {
        property.Name = Regex.Replace(property.Name, "^(.*.)(Id)$", @"$1_$2");
    }
}

Add this convention to the model builder in OnModelCreating:

modelBuilder.Conventions.Add(new ForeignKeyPropertyConvention());

Of course, for just one foreign key property that's overkill, but in larger projects this would be a convenient way to do it.

For just this one property it would suffice to add this line:

modelBuilder.Entity<Scenario>().Property(s => s.LocationId)
    .HasColumnName("Location_Id");
2
4/14/2020 7:56:14 PM

Popular Answer

Use the data attribute ForeignKey:

public int Id { get; set; }
public string Name { get; set; }
public Location Location { get; set; }
[ForeignKey("Location")]
public int LocationId { get; set; }

Using the fluent API, it would look almost the same. Leave out the data attribute and include this code:

modelBuilder.Entity<Scenarios>()
    .HasOne<Location>(s => s.Location)
    .WithMany()
    .HasForeignKey(s => s.LocationId);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow