Creating a ManyToMany relationship between two models

Question

I'm new to creating a Windows Store app and it requires a use of a database. I've settled for sqlite and am using the sqlite-net package. However, I'm unsure of how to create a m2m relationship between two models.

class ModelA
{
     [PrimaryKey, AutoIncrement]
     public int Id { get; set; }
     public string name { get; set; }
     <relationship to ModelB>
} 


class ModelB
{
     [PrimaryKey, AutoIncrement]
     public int Id { get; set; }
     public string name { get; set; }
}

Would I have to use a List? or a byte[]? How can I guarantee the property will be constrained to ModelB?

Foretell

Answer 1

You might be able to use sqlite-net-extensions, it has a ManyToMany attribute that seems perfect for your needs. This is an example of its use from their website.

public class Student
{
    [PrimaryKey, AutoIncrement]
    public int StudentId { get; set; }

    public string Name { get; set; }
    public int Age { get; set; }

    [ManyToMany(typeof(StudentsGroups))]
    public List<Group> Groups { get; set; }

    public int TutorId { get; set; }
    [ManyToOne("TutorId")] // Foreign key may be specified in the relationship
    public Teacher Tutor { get; set; }
}

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

    public string GroupName { get; set; }

    [ForeignKey(typeof(Teacher))]
    public int TeacherId { get; set; }

    [ManyToOne]
    public Teacher Teacher { get; set; }

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

public class StudentGroups
{
    [ForeignKey(typeof(Student))]
    public int StudentId { get; set; }

    [ForeignKey(typeof(Group))]
    public int GroupId { get; set; }
}

Bombardier

Thanks, exactly what I needed. I have one really noob question though, how to I add it to my existing project since the file downloaded is a visual studio project. – Foretell

Unfortunately there isn't a nuGet package or a binary, so you'll have to build it. – Bombardier

I'm unable to open the project let alone build it. Might be because I'm using visual studio express. I'll do some more digging and if I can't find any other solution, or if I manage to build it, I'll accept your answer. – Foretell

SQLite-Net Extensions is already available as a NuGet package: nuget.org/packages/SQLiteNetExtensions – Petta

That example seems poorly spelled, unless I'm missing something. For instance, the Group has a ManyToMany relationship to StudentsGroups, while that class is called StudentGroups. I've been trying to get my own version of this working, but get a NotSupportedException when trying to create one of the Many-side tables. – Araby