Retrieving data using LINQ

Question

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 with 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 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 main 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.

Answer 1

Here is code that works. It uses just EntityFramework 6.3.1 without any SQLite specific assemblies.

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

Specifically, what assemblies contain the following methods?

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

In any case, here is code that works with 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 did, because I didn't know the exact assemblies you used. For instance, it wasn't clear which assembly you used for the Table<T>() method. So, I used the DbContext.Kursy approach instead and the following references:

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

In other words, it works simply with EntityFramework 6.1.3 and doesn't require any SQLite specific assemblies.

For an answer that's relevant to your needs, what SQLite specific references are you referencing?