efcore icon indicating copy to clipboard operation
efcore copied to clipboard

Owned table using entity splitting and mapped to different table doesn't work in SqlServer

Open smitpatel opened this issue 2 years ago • 2 comments

               mb.Entity<EntityOne>(
                    b =>
                    {
                        b.ToTable("EntityOnes");

                        b.OwnsOne(e => e.OwnedReference,
                            o =>
                            {
                                o.ToTable("OwnedReferences");

                                o.SplitToTable("OwnedReferenceExtras1",
                                    t =>
                                    {
                                        t.Property(e => e.OwnedIntValue3);
                                        t.Property(e => e.OwnedStringValue3);
                                    });

                                o.SplitToTable("OwnedReferenceExtras2",
                                    t =>
                                    {
                                        t.Property(e => e.OwnedIntValue4);
                                        t.Property(e => e.OwnedStringValue4);
                                    });
                            });
                    });

public class EntityOne
{
    public int Id { get; set; }
    public string StringValue1 { get; set; }
    public string StringValue2 { get; set; }
    public string StringValue3 { get; set; }
    public string StringValue4 { get; set; }
    public int IntValue1 { get; set; }
    public int IntValue2 { get; set; }
    public int IntValue3 { get; set; }
    public int IntValue4 { get; set; }
    public OwnedReference OwnedReference { get; set; }
}

public class OwnedReference
{
    public int Id { get; set; }
    public string OwnedStringValue1 { get; set; }
    public string OwnedStringValue2 { get; set; }
    public string OwnedStringValue3 { get; set; }
    public string OwnedStringValue4 { get; set; }
    public int OwnedIntValue1 { get; set; }
    public int OwnedIntValue2 { get; set; }
    public int OwnedIntValue3 { get; set; }
    public int OwnedIntValue4 { get; set; }
}

Issue: Tables for additional fragments

CREATE TABLE [OwnedReferenceExtras1] (
    [EntityOneId] int NOT NULL,
    [OwnedStringValue3] nvarchar(max) NULL,
    [OwnedIntValue3] int NOT NULL,
    CONSTRAINT [PK_OwnedReferenceExtras1] PRIMARY KEY ([EntityOneId]),
    CONSTRAINT [FK_OwnedReferenceExtras1_EntityOnes_EntityOneId] FOREIGN KEY ([EntityOneId]) REFERENCES [EntityOnes] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_OwnedReferenceExtras1_OwnedReferences_EntityOneId] FOREIGN KEY ([EntityOneId]) REFERENCES [OwnedReferences] ([EntityOneId]) ON DELETE CASCADE
)

Microsoft.Data.SqlClient.SqlException : Introducing FOREIGN KEY constraint 'FK_OwnedReferenceExtras1_OwnedReferences_EntityOneId' on table 'OwnedReferenceExtras1' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.

smitpatel avatar Sep 13 '22 20:09 smitpatel

Same happens for owned collections which are always mapped to different table.

smitpatel avatar Sep 14 '22 22:09 smitpatel

Note from triage: waiting for @AndriySvyryd

ajcvickers avatar Sep 21 '22 18:09 ajcvickers

~~Note from triage: we should block this in model validation. @AndriySvyryd to prepare PR for Tactics.~~

Wrong issue. 😶‍🌫️

ajcvickers avatar Oct 06 '22 12:10 ajcvickers

Note from triage: consider not creating a foreign key in this case.

ajcvickers avatar Oct 06 '22 12:10 ajcvickers