How to set class property's value to be set to generated id value of another class on insertion to database?

c# entity-framework entity-framework-core sql-server

Question

I have a bit of a hard time putting this just in words, so I'll use some code to help explain myself and my problem. So imagine I have two classes ClassA and ClassB:

class ClassA
{
    public int ClassAId { get; set; }
    public string Name { get; set; }
}

[Owned]
class ClassAOwned
{
    public int ClassAId { get; set; }
    public string Name { get; set; }
}

class ClassB
{
    public int ClassBId { get; set; }
    public string Action { get; set; }
    public ClassAOwned ClassA { get; set; }
}

as you can see ClassB contains ClassA, but I have another class for it ClassAOwned because I want ClassB to own ClassA (flatten its columns into ClassB table), but also have ClassA DbSet as a seperate table (and as I understand entity class cannot be owned and have its own DbSet at the same time), so I had to use 2 different classes. Here's my context to make it easier to understand:

class TestContext : DbContext
{
    public DbSet<ClassA> ClassAs { get; set; }
    public DbSet<ClassB> ClassBs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseInMemoryDatabase("TestContext");
    }
}

Now my problem comes when I'm trying to insert ClassA and ClassB to context at the same time and have to match their ClassAId values which is generated by database provider:

var testContext = new TestContext();
var classA = new ClassA
{
    Name = "classAName"
};
var classB = new ClassB
{
    Action = "create",
    ClassA = new ClassAOwned
    {
        ClassAId = classA.ClassAId,
        Name = classA.Name
    }
};
testContext.ClassAs.Add(classA);
testContext.ClassBs.Add(classB);
classB.ClassA.ClassAId = classA.ClassAId;
testContext.SaveChanges();

when using InMemoryDatabase the following call:

testContext.ClassAs.Add(classA);

actually changes classA.ClassAId to correct generated value, however when using SQL server classA.ClassAId gets set to int.MinValue so next call:

classB.ClassA.ClassAId = classA.ClassAId;

sets classB.ClassA.ClassAId to int.MinValue. and the final call:

testContext.SaveChanges();

changes classA.ClassAId to correct generated value, but classB.ClassA.ClassAId stays as int.MinValue and that's the value that gets inserted into the database.

My question is: Is there a way to tell EF core that when adding two entities into context set one's property to whatever value was generated for another entity's primary key? So the functionality I'm looking for is exactly the same as adding two entities where one has a foreign key, except in this case it's not really a foreign key.

A simple workaround would be to set the "foreign key"(classB.ClassA.ClassAId) after testContext.SaveChanges() and save changes again, but then it becomes two separate operations and what if the second one fails? The database will be in invalid state.

1
6
7/8/2019 3:26:59 PM

Accepted Answer

It's possible, but with some trickery which works with the latest at this time EF Core 2.2, and might stop working in 3.0+ (at least needs to be verified).

First, it has to be mapped as relationship - there is just no other way. It doesn't need to be a real database relationship though, just should be such from the EF Core model point of view.

Second, and this is quite important, the delete cascade behavior should be set to Restrict, which currently means enforce in the database, but do nothing with the tracked related entities in memory.

So let's do that with you sample. Both aforementioned mappings require fluent configuration similar to this:

modelBuilder.Entity<ClassB>().OwnsOne(e => e.ClassA)
    .HasOne<ClassA>().WithMany() // (1)
    .OnDelete(DeleteBehavior.Restrict); // (2)

If you are using migrations, the generated migration would contain something like this:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "ClassA",
        columns: table => new
        {
            ClassAId = table.Column<int>(nullable: false)
                .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
            Name = table.Column<string>(nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_ClassA", x => x.ClassAId);
        });

    migrationBuilder.CreateTable(
        name: "ClassB",
        columns: table => new
        {
            ClassBId = table.Column<int>(nullable: false)
                .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
            Action = table.Column<string>(nullable: true),
            ClassA_ClassAId = table.Column<int>(nullable: false),
            ClassA_Name = table.Column<string>(nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_ClassB", x => x.ClassBId);
            table.ForeignKey(
                name: "FK_ClassB_ClassA_ClassA_ClassAId",
                column: x => x.ClassA_ClassAId,
                principalTable: "ClassA",
                principalColumn: "ClassAId",
                onDelete: ReferentialAction.Restrict);
        });

    migrationBuilder.CreateIndex(
        name: "IX_ClassB_ClassA_ClassAId",
        table: "ClassB",
        column: "ClassA_ClassAId");
}

Manually edit it and remove the ForeignKey command (line) since you don't want a real FK. You could also remove the corresponding CreateIndex command, although it won't hurt.

And that's all. The only important thing you need to remember is to use the principal TableAId property only after the new entity has been added to (thus tracked by) the context. i.e.

var testContext = new TestContext();
var classA = new ClassA
{
    Name = "classAName"
};
testContext.ClassAs.Add(classA); // <--
var classB = new ClassB
{
    Action = "create",
    ClassA = new ClassAOwned
    {
        ClassAId = classA.ClassAId, // <--
        Name = classA.Name
    }
};
testContext.ClassBs.Add(classB);
testContext.SaveChanges();

It will have generated temporary negative value, but after SaveChanged both ids will be updated with the actual database generated value.

2
7/10/2019 6:10:47 PM

Popular Answer

After getting more information, one way to implement this is something like

class A
{
    public int ID;
    public string data;
...
}

class HistoryA
{
    public int ID;
    public int AID;
    public string data;
    public DateTime updated;
...
}

So I hade one class (class A) holding the current information and another historyA referencing the current one (HistoryA.AID = A.ID) and storing all the previous information in HistoryA.

So all data properties in A was duplicated in HistoryA.



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