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:
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