0

I've a many to many relationship between students and classes using SQLite-net Extensions:

public class Students
{
    [PrimaryKey, AutoIncrement]
    public int StudentId { get; set; }
    public string Name { get; set; }

    [ManyToMany(typeof(Students_Classes))]
    public List<Classes> Classes { get; set; }
}

public class Classes
{
    [PrimaryKey, AutoIncrement]
    public int ClassId { get; set; }
    public string Name { get; set; }

    [ManyToMany(typeof(Students_Classes))]
    public List<Students> Students { get; set; }
}

public class Students_Classes
{
    [ForeignKey(typeof(Students))]
    public int StudentFId { get; set; }

    [ForeignKey(typeof(Classes))]
    public int ClassFId { get; set; }
}

I add a relationship this way:

dbConnection.InsertOrReplace(new Students_Classes { StudentFId = sId, ClassFId = cId });

But when I want to delete a relationship:

var validRelation = dbConnection.Find<Students_Classes>(x => x.StudentFId = sId && x.ClassFId = cId);
if (validRelation == null)
    return;

dbConnection.Delete(validRelation);

I get an error says cannot delete because it has no PK. I can get a Student with all his classes, remove one class then save it again with his classes but there can be performance issues.

How to remove a relationship in a many to many relationship? thanks.

user4126589
  • 51
  • 4

1 Answers1

1

Pretty easy, just add a primary key to your relationship class:

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

    [ForeignKey(typeof(Students))]
    public int StudentFId { get; set; }

    [ForeignKey(typeof(Classes))]
    public int ClassFId { get; set; }
}

Anyway using SQLite-Net Extensions you don't need to manage the relationship table by yourself, just add or remove the children item from the list and call UpdateWithChildren on the object.

Take a look at the Integration Tests project to see how it works.

redent84
  • 18,901
  • 4
  • 62
  • 85
  • Thanks @redent84. I thought there should be performance issues if call `GetWithChildren()`, add the children item and then save them all again? – user4126589 Oct 16 '14 at 22:03
  • Well, if that's the case you're correct. It doesn't make sense to load all elements just to delete one of them. Maybe I can add a convenience method for that in the API. – redent84 Oct 16 '14 at 22:51