How do I delete records from one table that are linked to another table through its PK?

Question

I have a database in a Maui app that has two tables. The first table was setup with a class like this:

public class Groups
{
    [PrimaryKey, AutoIncrement]
    public long Id { get; set; }
    public string? GrpName { get; set; }
}

The other table was created with this class:

public class Sites
{
    [PrimaryKey, AutoIncrement]
    public long Id { get; set; }
    public string? Title { get; set; }
    public string? SiteAddr { get; set; }
    public long GrpId { get; set; }    // The Id number matching the Group Id
    public string? Notes { get; set; }
}

What I'm looking to do is select one or more groups from a datagrid that I already created and delete all Groups and Sites records linked to the Groups table via the GrpId field.

My database file was created like this:

var dbname = GenDbName();  // This generates the database file name.
var dbpath = Path.Combine(dbLoc, dbname);  //This is the full path of the database
var options = new SQLiteConnectionString(dbpath, true);
Conn = new SQLiteAsyncConnection(options);
await Conn.CreateTablesAsync<Groups, Sites>(CreateFlags.AutoIncPK);

I did research in LINQ figuring I could delete the Sites records in a similar way that MySql does it, but I just can't grasp it. Any suggestions?

Answer 1

var options = new SQLiteConnectionString(dbpath, true); Conn = new SQLiteAsyncConnection(options);

From the code you shared, we could know that you use nuget sqlite-net-pcl as the local database.

And if you want to delete records from one table that are linked to another table through its PK, you can try to use Nuget SQLiteNetExtensions which is a very simple ORM that provides one-to-one, one-to-many, many-to-one, many-to-many, inverse and text-blobbed relationships between sqlite-net entities.

For your problem, you can try to add a ForeignKey GroupsId for Sites.cs and add CascadeOperation.CascadeDelete attribute for the InnerSites that is related to current group。

Please refer to the following code:

public class Sites
{
    [PrimaryKey, AutoIncrement]
    public long Id { get; set; }
    public string? Title { get; set; }
    public string? SiteAddr { get; set; }

    [ForeignKey(typeof(Groups))]
    public long GroupsId { get; set; }    // The Id number matching the Group Id

    public string? Notes { get; set; }
}


public class Groups
{
    [PrimaryKey, AutoIncrement]
    public long Id { get; set; }
    public string? GrpName { get; set; }


    [OneToMany(CascadeOperations = CascadeOperation.CascadeInsert | CascadeOperation.CascadeDelete)]
    public List<Sites> InnerSites { get; set; } = new List<Sites>();
}

Then if you delete the current Groups from the database, then the relative Sites will be deleted at the same time.

       private SQLiteConnection _connection;

      string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "ItemsDb.db");

      _connection = new SQLiteConnection(dbPath);


   public void DeleteItem(Groups item)
   {
       _connection.Delete(item,true);

   }

For more information, you can check: sqlite-net.

Answer 2

The question doesn't contain any LINQ code at all. It seems to that SQLite-Net and SQLite-Net Extension are used.

As the SQLite-Net documentation shows to insert or delete an object you need to call Insert or Delete.

In SQLite itself, and all other databases, related rows will be deleted as well if a foreign key constraint was created with ON DELETE CASCADE, eg :

FOREIGN KEY (customer_id) references customers(id) ON DELETE CASCADE

In other databases this happens automatically. In SQLite though, you need to enable cascade deletes on every connection with :

PRAGMA foreign_keys = ON

To avoid this hassle, SQLite-Net Extensions offers a cascade delete overload that automatically enables the PRAGMA when you pass recurse: true. Extensions also allow you to specify the FK constraints in C# code as object relations :

public class Customer {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.CascadeDelete)]
    public Order[] Orders { get; set; }
}

public class Order
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    
    [ForeignKey(typeof(Customer))]
    public int CustomerId { get; set; }
    ...
}

...

var customer = conn.Get<Customer>(1234);

// All orders for this customer will be deleted from the database
conn.Delete(customer, recursive: true);

LINQ is a generic query only language that each provider translates to whatever it needs, eg API calls, URLs or in the case of ORMs like NHibernate, EF or SQLite-Net, SQL. Modifying data is the job of the ORM, not LINQ.