C#•5mo ago

Anyone with DBInitializer experience/.NET mentor? Could use some help

Hey All Is there anyone with some experience on this topic willing to help? Wouldn't ask if i could find my way, but i just can't seem to make this work as expected (latest error is web server fails -> FK constraint exception on context.saveChanges(), but my models seem correct) Thanks

27 Replies

Pobiega•5mo ago

$details

MODiX•5mo ago

When you ask a question, make sure you include as much detail as possible. Such as code, the issue you are facing, and what you expect the result to be. Upload code here https://paste.mod.gg/ (see $code for more information on how to paste your code)

studentoffuturearts•5mo ago

Here is most of the relevant code https://paste.mod.gg/xpxtoxoywybd/5

BlazeBin - xpxtoxoywybd

A tool for sharing your source code with the world!

Pobiega•5mo ago

Okay, so this is essentially a database seeder when exactly is this exception thrown? please post the actual stacktrace, including any and all sql errors

studentoffuturearts•5mo ago

exception is SqlException: The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Ins_Contacts_ContactId". The conflict occurred in database "FinAppAPI", table "dbo.Contacts", column 'ContactId'. occurs at the end of DBInitializer/seeder at context.saveChanges() thrown at call stack Microsoft.Data.SqlClient.SqlConnection.OnError(Microsoft.Data.SqlClient.SqlException, bool, System.Action<System.Action>) Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Microsoft.Data.SqlClient.TdsParserStateObject, bool, bool) Microsoft.Data.SqlClient.TdsParser.TryRun(Microsoft.Data.SqlClient.RunBehavior, Microsoft.Data.SqlClient.SqlCommand, Microsoft.Data.SqlClient.SqlDataReader, Microsoft.Data.SqlClient.BulkCopySimpleResultSet, Microsoft.Data.SqlClient.TdsParserStateObject, out bool) Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(out bool) Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreResults(out bool) Microsoft.Data.SqlClient.SqlDataReader.TryNextResult(out bool) Microsoft.Data.SqlClient.SqlDataReader.NextResult() Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(Microsoft.EntityFrameworkCore.Storage.RelationalDataReader) Thanks for helping me, really appreciate it If there's a better way for me to troubleshoot and things to watch out for/tips to trace such errors, you're welcome to inform me

Pobiega•5mo ago

Ah yeah I see the issue You are not following naming conventions ContactId should just be Id in the Contact class same thing with UserId etc

studentoffuturearts•5mo ago

I actually changed it into ContactId to check if i was using the wrong convention originally they were just Id

Pobiega•5mo ago

okay, thats what its supposed to be can you also show the definition of In btw?

studentoffuturearts•5mo ago

good to know, i'll change it back

Unknown User•5mo ago

Message Not Public

Pobiega•5mo ago

FK_Ins_Contacts_ContactId tells me that its the relation between In and Contact thats causing the issue

Unknown User•5mo ago

Message Not Public

Pobiega•5mo ago

so we need to see In

studentoffuturearts•5mo ago

https://paste.mod.gg/qxkomveatail/1 here is 'in' and 'out' the [Key] and [database..] clauses i added to make sure primary key was recognized and hoped to make sure the seeded data PK was incremented from 0 (to make sure i was adding correct values and that wasn't the constraint problem)

BlazeBin - qxkomveatail

A tool for sharing your source code with the world!

studentoffuturearts•5mo ago

you mean, just delete the DB and run the app again right ? that we're on the same page

Unknown User•5mo ago

Message Not Public

Pobiega•5mo ago

The models seem okay, I don't personally rely on the auto-configured relationships very often so might have overlooked something there, but it seems fine I wonder if its just because you have so many nav props going both ways and potentially setting up multiple cascade paths or something, but that doesnt mesh with the error you're getting

studentoffuturearts•5mo ago

No, no data present i've been deleting it everytime i change something

Unknown User•5mo ago

Message Not Public

studentoffuturearts•5mo ago

considering good practice, should i be using less ? And yeah, i figured, been at this for hours but i can't seem to find a reason the constraint fails I though is was some underlying framework thing should the models be created in a certain order ? Like with migrations ?

Pobiega•5mo ago

If you don't need them, you shouldn't have them, generally. gonna set up your models in a new project and see what happens worked fine with just In and Contact .net 8

CREATE TABLE "Contacts" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Contacts" PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL
);
CREATE TABLE "Ins" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Ins" PRIMARY KEY AUTOINCREMENT,
    "Date" TEXT NOT NULL,
    "ContactId" INTEGER NULL,
    CONSTRAINT "FK_Ins_Contacts_ContactId" FOREIGN KEY ("ContactId") REFERENCES "Contacts" ("Id")
);

CREATE TABLE "Contacts" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Contacts" PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL
);
CREATE TABLE "Ins" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Ins" PRIMARY KEY AUTOINCREMENT,
    "Date" TEXT NOT NULL,
    "ContactId" INTEGER NULL,
    CONSTRAINT "FK_Ins_Contacts_ContactId" FOREIGN KEY ("ContactId") REFERENCES "Contacts" ("Id")
);

studentoffuturearts•5mo ago

i'm on 6.0 but i'll start a new project later with just these or maybe start one in 8.0

Pobiega•5mo ago

found two potential errors, dont think its this thou but still User has a role property, instead of Role and in your initializer, you are assigning roles via ID, before the roles are saved better to assign via the entity reference ah and you do the same on ID pretty sure thats it works absolutely fine for me when I use the actual nav props

var role = new Role() { Name = "Testuser" };

var contactUtil = new Contact() { Name = "Utilities" };
var contactWage = new Contact() { Name = "Wage" };

var userMaggie = new User() { Name = "Maggie", Role = role };

var in1 = new In() { Contact = contactWage, User = userMaggie, Date = DateTime.Now.AddDays(-1), Amount = 2000 };
var out1 = new Out() { Contact = contactUtil, User = userMaggie, Date = DateTime.Now, Amount = 2000 };

_db.Add(in1);
_db.Add(out1);

await _db.SaveChangesAsync();

var role = new Role() { Name = "Testuser" };

var contactUtil = new Contact() { Name = "Utilities" };
var contactWage = new Contact() { Name = "Wage" };

var userMaggie = new User() { Name = "Maggie", Role = role };

var in1 = new In() { Contact = contactWage, User = userMaggie, Date = DateTime.Now.AddDays(-1), Amount = 2000 };
var out1 = new Out() { Contact = contactUtil, User = userMaggie, Date = DateTime.Now, Amount = 2000 };

_db.Add(in1);
_db.Add(out1);

await _db.SaveChangesAsync();

your models are mostly intact, I jsut removed all the attributes yep, when using raw ids it fails since the ids are not yet set

studentoffuturearts•5mo ago

going to check it out now

Pobiega•5mo ago

I added

var in2 = new In() { ContactId = 1, UserId = 1, Date = DateTime.Now, Amount = 123 };

_db.Add(in2);

var in2 = new In() { ContactId = 1, UserId = 1, Date = DateTime.Now, Amount = 123 };

_db.Add(in2);

and it broke with an FK error, since those ids wasnt guaranteed to exist you should be using the nav props when seeding like this, since you dont know what IDs the resulting items might get.

studentoffuturearts•5mo ago

Logical, but would've never thought of this indeed, the server is launching this time, so it seems functional Wow man, thanks, for real i would've never found this out

Pobiega•5mo ago

Np. Feel free to /close this thread

Gaming

Programming

Anyone with DBInitializer experience/.NET mentor? Could use some help