Hans Poppe Hans Poppe - 5 months ago 21
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

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

Comments