samedi 7 février 2015

SQLite - retrieving data using LINQ

I am stuck with this problem since few evenings. I have SQLite database in my application. I have created that SQLite DB from a file. The ERD diagram is shown below: enter image description here


And now in my application I create a connection to my database:



using (var conn = new SQLiteConnection(DB_PATH))
{
// retrieving statemets...
}


I have created classes which represent tables in my DB:



public class Kantory
{
public Kantory()
{
this.kursy = new HashSet<Kursy>();
}

[SQLite.PrimaryKey, SQLite.AutoIncrement]
public int id_kantory { get; set; }
public string nazwa { get; set; }

public virtual ICollection<Kursy> kursy { get; set; }
}

public class Waluty
{
public Waluty()
{
this.kursy = new HashSet<Kursy>();
}

[SQLite.PrimaryKey, SQLite.AutoIncrement]
public int id_waluty { get; set; }
public string nazwa { get; set; }

public virtual ICollection<Kursy> kursy { get; set; }
}

public class Kursy
{
[SQLite.PrimaryKey, SQLite.AutoIncrement]
public int id_kursy { get; set; }
public int id_kantory { get; set; }
public int id_waluty { get; set; }
public decimal kurs { get; set; }
public System.DateTime data { get; set; }
public int aktualne { get; set; }

public virtual Kantory kantory { get; set; }
public virtual Waluty waluty { get; set; }
}


As you can see, in kursy table I have two foreign keys - id_kantory and id_waluty.


And now very curious and strange thing happens. When I try to retrieve some information using normal SQL statemets it work fine!:



using (var conn = new SQLiteConnection(DB_PATH))
{
var query = new SQLiteCommand(conn);
query.CommandText = "SELECT * FROM Kursy INNER JOIN Kantory ON Kursy.id_kursy=Kantory.id_kantory WHERE Kantory.id_kantory = 1";
var result = query.ExecuteQuery<Kursy>();
}


This code works fine! BUT when I try to use my classes using LINQ like this:



using (var conn = new SQLiteConnection(DB_PATH))
{
var result = conn.Table<Kursy>().Where(k => k.kantory.id_kantory == 1).FirstOrDefault();
}


It throws me a NotSupportedException! The messsage is: Member access failed to compile expression


BUT when I use my classes using LINQ WITHOUT JOINING another class it works!:



using (var conn = new SQLiteConnection(DB_PATH))
{
var result = conn.Table<Kursy>().Where(k => k.id_kursy == 1).FirstOrDefault();
}


So in the end: my problem is that I cannot join more that one table using LINQ query. Seems that this model in classes is wrong but I really don't know why.


PS. This is Windows Phone 8.1 Application, so I cannot use Entity Framework for that.


Aucun commentaire:

Enregistrer un commentaire