'FOREIGN KEY constraint failed in Sqlite using Entity Framework Core and WinUi 3
I am getting the following error when attempting to add to add a record to my SQLite database:
SqliteException: SQLite Error 19: 'FOREIGN KEY constraint failed'
The exception occurs in ClientViewModel.cs - line 74
Link to my project:
https://github.com/SpaceWarlord/Roster
GitHub
GitHub - SpaceWarlord/Roster
Contribute to SpaceWarlord/Roster development by creating an account on GitHub.
26 Replies
can you share the full exception? it should tell you which constraint caused the error
but generally that means you tried to save a relationship that's not valid
that's what foreign keys do, they relate rows in one table to rows in another table
which means they always have to be a valid key in that table (or null in some cases)
so chances are you're adding an item with a foreign key that points to no valid row, or deleting an item that is referenced by a foreign key somewhere else
i also really don't recommend coupling your interface code and your database code as tightly as you are
a synchronous SaveChanges is going to hang your UI and your entities shouldn't implement INPC
inpc?
INotifyPropertyChanged
you mean my models?
your database models, yes
also, dbcontexts should not be long lived like they are in your code
they should be created, used for an operation, then disposed
a guy the other day in the #gui chat said the opposite
that you should use one rather then multiple
that is very bad advice
they're designed to be short-lived
for example, you can't run 2 queries at once on the same dbcontext and the change tracker will get stale and full of junk the longer you keep it
which can lead to weird issues with the state of your database
ok didnt know that
how would i go about decoupling my code more
i thought that's what i was doing with all this mvvm stuff (im a winforms guy moving over to winui )
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
thats the stack trace for the inner exception
Try using this package, it'll give you more details on db exceptions
https://www.nuget.org/packages/EntityFrameworkCore.Exceptions.Common
EntityFrameworkCore.Exceptions.Common 8.1.3
Handle database errors easily when working with Entity Framework Core. Catch specific exceptions such as UniqueConstraintException, CannotInsertNullException, MaxLengthExceededException, NumericOverflowException or ReferenceConstraintException instead of generic DbUpdateException
This is a base package for database specific packages. Install on...
use different classes for your DB models and your MVVM models and map between them
and an easy solution for the dbcontext is to switch to IDbContextFactory and create new dbcontexts through that when you actually need them
With that package installed its now saying I have reference constraint exception
Do you have any examples or tutorials on how to do this for a beginner?
Expand the exception, see the property
ConstraintName
its null for some reason
Well that's not good
Are you sure you followed all the steps in the package's readme
i'll double check
optionsBuilder.UseExceptionProcessor();
optionsBuilder.UseSqlite("Data Source=database27.db"); optionsBuilder.EnableSensitiveDataLogging(true); thats what i have in my OnConfiguring which is what the guide says to do
optionsBuilder.UseSqlite("Data Source=database27.db"); optionsBuilder.EnableSensitiveDataLogging(true); thats what i have in my OnConfiguring which is what the guide says to do
"[!WARNING] ConstraintName and ConstraintProperties will not be populated when using SQLite."
ok nvm its cause im using sqlite
it's just OOP stuff, like
var uiModel = new MyUiModel(myEntity);
with code that takes all the data from the entity and puts it in the ui modelok and whats the benefits of doing it that way
it separates the code that is concerned with working with the UI and the code that is concerned with modeling your database
so if you have to change one it's easier to avoid breaking the other
ok that makes sense
@Jimmacle hi i am bit confused with implementing this entity to model thing
within my model class should i store a reference to the entity?
something like this:
public class UserModel(){
UserEntity userEntity;
public UserModel(string id, string name, ){
userEntity=new UserEntity(id, name);
}
}
my current code:
https://github.com/SpaceWarlord/Roster
GitHub
GitHub - SpaceWarlord/Roster
Contribute to SpaceWarlord/Roster development by creating an account on GitHub.