Retrieving Data Using LINQ

Question

I am stuck with this problem from several evenings. I have an SQLite database in my application. I created this SQLite DB from a file. ERD chart is shown below:

And now in my application, I am creating a connection to my database:

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

I created classes that 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 the kursy table I have two foreign keys - id_kantory and id_waluty .

And now a very curious and strange thing is happening . When I try to get some information using regular SQL expressions using the INNER JOIN statement, it works 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 great! BUT , when I try to use my classes using LINQ, for example:

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

This throws me a NotSupportedException! The message looks like this: When accessing a member, the expression could not be compiled

BUT, when I use my classes using LINQ WITHOUT CONNECTING another class, this works:

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

Bottom line: my main problem is that I cannot join more than one table using the LINQ query . This model seems to be wrong in the classes, but I really don't know why ...

PS. This application is for Windows Phone 8.1, so I can not use the Entity Framework for this.

Answer 1

Here is the code that works. It uses only EntityFramework 6.3.1 without any SQLite-specific builds.

I understand that you do NOT want to use the Entity Framework. To add an answer for this, think we need to know what SQLite specific assemblies you are using. For example, are you using DbLinq ?

In particular, which assemblies contain the following methods?

• SQLiteCommand.ExecuteQuery<T>()
• SQLiteConnection.Table<T>()

Anyway, here is the code that works with the Entity Framework.

 using System; using System.Linq; using System.Data.Entity; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; namespace SQLite { class Program { static void Main(string[] args) { using (var conn = new SQLiteConnection(@"C:\linqToSqlite.db")) { SeedEntities(conn); // this is the query that DID work for you var result1 = conn.Kursy .Where(k => k.id_kursy == 1) .FirstOrDefault(); Console.WriteLine( string.Format("id_kursy:{0}", result1.id_kursy)); // this is the query that did NOT work for you // it does work here var result2 = conn.Kursy .Where(k => k.kantory.id_kantory == 1) .FirstOrDefault(); Console.WriteLine( string.Format("id_kursy:{0}", result2.id_kantory)); } Console.ReadKey(); } private static void SeedEntities(SQLiteConnection conn) { SeedEntities(conn); // make sure two entities exist with the appropriate ids if (!conn.Kantory.Any(x => x.id_kantory == 1)) { conn.Kantory .Add(new Kantory() { id_kantory = 1 }); } if (!conn.Kursy.Any(x => x.id_kantory == 1)) { conn.Kursy .Add(new Kursy() { id_kantory = 1 }); } conn.SaveChanges(); } } public class SQLiteConnection : DbContext { public SQLiteConnection(string connString) : base(connString) {} public DbSet<Kantory> Kantory { get; set; } public DbSet<Kursy> Kursy { get; set; } } public class Kantory { public Kantory() { this.kursy = new HashSet<Kursy>(); } [Key] public int id_kantory { get; set; } public virtual ICollection<Kursy> kursy { get; set; } } public class Kursy { [Key] public int id_kursy { get; set; } public int id_kantory { get; set; } public virtual Kantory kantory { get; set; } } } 

I'm afraid I used a different technique than you, because I did not know the exact assemblies you used. For example, it is unclear which assembly you used for the Table<T>() method. So, I used the DbContext.Kursy approach and the following links:

• EntityFramework.dll
• EntityFramework.SqlServer.dll
• System.dll
• System.ComponentModel.DataAnnotations.dll

In other words, it works just with EntityFramework 6.1.3 and does not require any SQLite specific assemblies.

For an answer that suits your needs, what SQLite specific links are you linking to?