InsertOrReplaceWithChildren throws an exception on notNull attribute of a Foreign key

[Sasan Pasha]

i have 2 tables and a one to many relationships established

    public class Parking 
    {
        [PrimaryKey, AutoIncrement]
        public int Parking_Id { get; set; }

        [MaxLength(30)]
        public string ParkingName { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]      // One to many relationship with Lots )
        public List<Lot> Lots { get; set; }

    }

and another table named Lot

    public class Lot 
    {
        [PrimaryKey, AutoIncrement]
        public int Lot_Id { get; set; }

        [ForeignKey(typeof(Parking)), NotNull]
        public int Parking_Id { get; set; }

        public int LotNumber { get; set; }
     }

so to add new records i use this

            var lots= new List<Lot>()
            {
                new Lot() {LotSize = "Large", LotNumber = 200},
                new Lot() {LotSize = "Medium", LotNumber = 201},
                new Lot() {LotSize = "Small", LotNumber = 202},
            };

            var parking = new Parking(){ParkingName = "Guest Parking" ,Lots = lots};
          _database.InsertWithChildren(parking , recursive: true);   // Insert the object in the database

at this point I have 3 records in the Lot table and foreign keys are assigned properly/ now if later I want to add a new Lot that belong to the same parking I will get an exception

of type 'SQLite.NotNullConstraintViolationException' occurred in SQLite-net.dll but was not handled in user code but the new lot record is added as well/ and additional info shows Additional information: NOT NULL constraint failed: Lot.Parking_Id

here is the new code

//adding new lot number to the collection 
parking.Lots.Add( new Lot() { LotSize = "Large", LotNumber = 303 } );

_database.InsertOrReplaceWithChildren(parking);  //exception is thrown here but record is added properly/

I also noticed if I use UpdateWithChildern nothing happens, and if I use InsertWithChildren, a new parking record is added to DB with a the list of 4 lots. which is not the intention here.

am i doing something wrong or is this a bug ? the fact that it adds the record to DB and throws an exception is buggy/

[Martin Kuckert]

Which records are added to the database? Is the Lot with LotNumber = 303 properly persisted? With a valid Parking_Id?

[Sasan Pasha]

@mkuckert yes the lotNumber 303 and proper parking_ID is persisted ... but i get an exception/ very weird

[Guillermo Gutiérrez]

First of all, you shouldn't use InsertOrReplace with an AutoIncrement primary key. It simply doesn't work. It will create or update the record with ID 0.

As a consequence of this, your Parking object will have ID set to 0, and therefore Parking_ID will be 0.

Aside from that, using SQLite-Net Extensions, objects are first inserted into the database so everybody has their primary key assigned, and then foreign keys are set. If your foreign keys are not nullable, it will fail on the first step.

[Guillermo Gutiérrez]

Looking at your code, if you want to add a new Lot to the parking, it's much simpler than that:

var lot = new Lot() { LotSize = "Large", LotNumber = 303, Parking_Id = parking.Parking_Id };
_database.Insert(lot);

Or if you had a ManyToOne property from Lot to Parking:

var lot = new Lot() { LotSize = "Large", LotNumber = 303, Parking = parking.Parking };
_database.InsertWithChildren(lot);

[Sasan Pasha]

Thank you !

[Emil Alipiev]

Does insert handles updates also? If PK is exist, does insert acts as update or we must handle it ourselves?