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 ?
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; }
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With