Entity Framework Core generates a new column when selecting from the children tables from a View

Viewed 153

I have a table in my database Customer and have an external database table Customer with a lot of information, I created a CustomerAccount view for representing this

View - CustomerAccount

CREATE VIEW CustomerAccount AS
SELECT c.Id, c.CustomerNumber, a.Name, ISNULL(DateTimeCreated, 0) as CreatedDate, ISNULL(DateTimeUpdated, ISNULL(DateTimeCreated, 0)) as UpdatedDate, ~InactiveFlag as Active 
FROM externaldatabase..CMAccount a
INNER JOIN mydatabase..Customer c on a.URI = c.CustomerNumber;

My table - Customer

CREATE TABLE [dbo].[Customer](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerNumber] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NOT NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Product1 and Product2 tables have foreign keys for my database Customer table

Configuration for the table Product1

public void Configure(EntityTypeBuilder<Product1> builder)
{
    builder
        .HasKey(b => b.Id);

    // all other properties

    builder
        .HasOne(b => b.Customer)
        .WithMany();
}

Configuration for the table Product2

public void Configure(EntityTypeBuilder<Product2> builder)
{
    builder
        .HasKey(b => b.Id);

    // all other properties

    builder
        .HasOne(b => b.Customer)
        .WithMany();
}

Configuration for the table Customer:

public void Configure(EntityTypeBuilder<Customer> builder)
{
    builder
        .HasKey(b => b.Id);

    builder
        .HasIndex(b => b.CustomerNumber);

    builder
        .Property(b => b.CustomerNumber)
        .IsRequired();

    builder
        .Property(b => b.CreatedDate)
        .HasColumnType("datetime")
        .IsRequired();

    builder
        .Property(b => b.UpdatedDate)
        .HasColumnType("datetime")
        .IsRequired();

    builder
        .Property(b => b.Active)
        .IsRequired();
}

Configuration for the view CustomerAccount:

public void Configure(EntityTypeBuilder<CustomerAccount> builder)
{
    builder
        .ToView(nameof(CustomerAccount));

    builder
        .HasMany(c => c.Product1Setups)
        .WithOne()
        .HasForeignKey(p1 => p1.CustomerId);

    builder
        .HasMany(c => c.Product2Setups)
        .WithOne()
        .HasForeignKey(p2 => p2.CustomerId);
}

Even if I use the string overload as below the same happens:

builder
    .HasMany(c => c.Product2Setups)
    .WithOne()
    .HasForeignKey("CustomerId");

I was expecting only a logical relationship between those, but when selecting from Product1 or Product2 EF core generates a query with an invalid column name CustomerId1. Both tables are already a column named CustomerId referenced by mydatabase..Customer of the example above.

Is there any solution for that?

UPDATE - TL/DR:

In summary what I need to do is to have two parent objects, one table and one view being referenced by the same columns in the children tables, since the view shares the same unique key basically.

0 Answers
Related