how to create collection (1:n) relation

Question

I'm implementing SQLite database in my Windows Store application (WinRT). I want to relation between two tables (1:n) Book (1) - Chapter (n)

class Book
{
    [SQLite.AutoIncrement, SQLite.PrimaryKey]
    public int Id { get; set; }
    public String Title { get; set; }
    public String Description { get; set; }
    public String Author { get; set; }
    public List<Chapter> Chapters { get; set; }

    public Book() 
    {
        this.Chapeters = new List<Chapter>();
    }
}

I get

-       $exception  {"Don't know about     System.Collections.Generic.List`1[Audioteka.Models.Chapter]"}    System.Exception {System.NotSupportedException}

+       [System.NotSupportedException]  {"Don't know about System.Collections.Generic.List`1[Audioteka.Models.Chapter]"}    System.NotSupportedException

+       Data    {System.Collections.ListDictionaryInternal} System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
    HelpLink    null    string
    HResult -2146233067 int
+       InnerException  null    System.Exception
    Message "Don't know about System.Collections.Generic.List`1[Audioteka.Models.Chapter]"  string

What am I doing wrong ?

This additional information is message of the exception that I get — Fixus, Oct 30 '12 at 20:04
I've never seen an error message "Don't know about System.Collections.Generic.List`1" before. Are you sure you're copying and pasting the exact error message? — Bobson, Oct 30 '12 at 20:31
@Bobson I've updated my message and pasted more thata from exception after app breaks — Fixus, Oct 30 '12 at 20:36
I can't find anything in the [documentation](https://github.com/praeclarum/sqlite-net/wiki) that indicates that it supports relationships between tables at all. That would lead me to believe that it only can understand basic value types which have a direct translation to SQL column types (int/long, float/double/decimal, string, etc). So I can't help, and I can't point to anything that could help. — Bobson, Oct 30 '12 at 20:50

Bobson · Accepted Answer · 2012-11-11T15:53:35.600

10

Just to follow up on my comment with a bit more research - SQLite-net doesn't support anything which can't be directly mapped to the database. See here for why:

The ORM is able to take a .NET class definition and convert it to a SQL table definition. (Most ORMs go in the other direction.) It does this by examining all public properties of your classes and is assisted by attributes that you can use to specify column details.

You can look into using a different ORM to actually access your data (I use Vici Coolstorage), if that's what you're trying to do, or simply remove the List<Chapters> from your class and add a BookID field to the Chapters class. That's how the database would represent it.

For purposes of working with it, you could add one of these to your class:

List<Chapters> Chapters { 
  get { 
     return db.Query<Chapters> ("select * from Chapters where BookId = ?", this.Id); 
  } 
}

or

List<Chapters> Chapters { 
  get { 
     return db.Query<Chapters>.Where(b => b.BookId == this.Id); 
  } 
}

That would at least let you pull the list easily, although it would be slow because it hits the database every time you access it.

edited Nov 11 '12 at 15:53

answered Oct 31 '12 at 00:18

Bobson

13,498
5
55
80

ok but I need the collection of chapters in my Book object :) I see that I should ignore chapters for ORM and populate it manualy while picking up the object. Hmm it's a big minus of SQLite :/ – Fixus Oct 31 '12 at 06:39
@Fixus - SQLite-net is probably not the ORM you want to be using. It seems to be strictly for database creation and population, with query features being an afterthought. SQLite itself doesn't have the issues, but you need a different way to communicate with it. – Bobson Oct 31 '12 at 17:29
@Fixus - I also added a way to populate `Chapters`, although it's not a great way to handle it. – Bobson Oct 31 '12 at 17:32
can you recomend some diffrent ways ? In every tutorial I've bumped on sqlite-net that is why I'm using it. Luck me that I can easly overcome this problem by little manual work so in THIS project this isn't very big problem but I would like to know better solution for the future. Thanks for the update – Fixus Oct 31 '12 at 17:33
@Fixus - I use Vici Coolstorage (and I fixed the link to it in my answer), but I had some issues getting it set up. I've heard good things about [Catnap](https://github.com/timscott/catnap), and there's always the various answers in [this question](http://stackoverflow.com/questions/93654/is-there-a-net-c-wrapper-for-sqlite). – Bobson Oct 31 '12 at 17:46

score 10 · Answer 2 · answered Apr 21 '14 at 13:18

Take a look at SQLite-Net Extensions. It provides complex relationships on top of SQLite-Net by using reflection.

Example extracted from the site:

public class Stock
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [MaxLength(8)]
    public string Symbol { get; set; }

    [OneToMany]      // One to many relationship with Valuation
    public List<Valuation> Valuations { get; set; }
}

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

    [ForeignKey(typeof(Stock))]     // Specify the foreign key
    public int StockId { get; set; }
    public DateTime Time { get; set; }
    public decimal Price { get; set; }

    [ManyToOne]      // Many to one relationship with Stock
    public Stock Stock { get; set; }
}

how to create collection (1:n) relation

2 Answers2

Linked