Hans Poppe Hans Poppe - 1 year ago 82
SQL Question

One-To-one Database First EF

Dear fellow programmers,

I'm stuck on this basic concept within EF and can't find any solution on stackoverflow.

I want to have One-to-One optional relation between: FluxLocation and Address.
(Normal words: a flux location could be provided with a physical address)

Note the database is already present and final.

SQL TABLES:

CREATE TABLE sales.sales_flux_location(
id serial PRIMARY KEY,
-- Many unusefull properties
sales_address_id integer REFERENCES sales_address
);

CREATE TABLE sales.sales_address(
id serial PRIMARY KEY,
-- Many unusefull properties
);


EF Mapping:

public partial class FluxLocation
{
public int Id { get; set; }

//Many unusefull properties.

[ForeignKey("Address")]
public int? AddressId { get; set; }
public Address Address { get; set; }
}

internal partial class FluxLocationConfiguration : EntityTypeConfiguration<FluxLocation>
{
public FluxLocationConfiguration()
{
//PK
HasKey(x => x.Id);
ToTable("sales_flux_location", "sales");
Property(a => a.Id)
.HasColumnName("id")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
//FK
HasOptional(l => l.Address)
.WithOptionalDependent(a => a.FluxLocation);
Property(l => l.AddressId)
.HasColumnName("sales_address_id")
.IsOptional();


// + mapping other properties.
}

public partial class Address
{
public int Id { get; set; }

// other properties

public FluxLocation FluxLocation { get; set; }
}

internal partial class AddressConfiguration : EntityTypeConfiguration<Address>
{
public AddressConfiguration()
{
//PK
HasKey(a => a.Id);
ToTable("sales_address", "sales");
Property(a => a.Id)
.HasColumnName("id")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
//FK
HasOptional(a => a.FluxLocation).WithOptionalPrincipal(l=>l.Address);

// mapping many unusefull properties

}


TEST CASE:

var dbAddress = Context.AddressSet.Add(new Address {Country = "BEL", CityName="Brussel", Street = Guid.NewGuid().ToString() });
var dbLocation = Context.FluxLocationSet.Add(new FluxLocation { AddressId = dbAddress.Id, Country = "BEL", Type = "MARKET", ExtId = Guid.NewGuid().ToString() });
Context.SaveChanges();


Error on Context.SaveChanges():


"42703: column \"Address_Id\" of relation \"sales_flux_location\" does not exist"}


Which is correct because the column name is "sales_address_id".
If any one could help why he is ignoring the propery columnname mapping?
I'm happy to provide more code if needed.

Answer Source

EF is not picking up that you want sales_address_id as the FK so it tried to create Address_Id. Also, there is some weirdness in how EF does 0:1 - essentially you need to fool it with a 1:M

So try this:

//FK
HasOptional(l => l.Address)
    .WithMany()
    .HasForeignKey(d => d.AddressId);

Link