title: Retrieving data using LINQ
body: <p>I am stuck with this problem since few evenings. I have <code>SQLite</code> database in my application. I have created that SQLite DB from a file. The ERD diagram is shown below:
  <img src="https://i.stack.imgur.com/1bUqG.png" alt="enter image description here"></p>
  
  <p>And now in my application I create a connection to my database:</p>
  
  <pre><code>using (var conn = new SQLiteConnection(DB_PATH))
  {
      // retrieving statemets...
  }
  </code></pre>
  
  <p>I have created classes which represent tables in my DB:</p>
  
  <pre><code>public class Kantory
  {
          public Kantory()
          {
              this.kursy = new HashSet&lt;Kursy&gt;();
          }
  
          [SQLite.PrimaryKey, SQLite.AutoIncrement]
          public int id_kantory { get; set; }
          public string nazwa { get; set; }
  
          public virtual ICollection&lt;Kursy&gt; kursy { get; set; }
  }
  
  public class Waluty
  {
          public Waluty()
          {
              this.kursy = new HashSet&lt;Kursy&gt;();
          }
  
          [SQLite.PrimaryKey, SQLite.AutoIncrement]
          public int id_waluty { get; set; }
          public string nazwa { get; set; }
  
          public virtual ICollection&lt;Kursy&gt; 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; }
  }
  </code></pre>
  
  <p>As you can see, in <code>kursy</code> table I have two foreign keys - <code>id_kantory</code> and <code>id_waluty</code>.</p>
  
  <p><strong>And now very curious and strange thing happens</strong>. When I try to retrieve some information using normal SQL statemets with <code>INNER JOIN</code> statement - it works fine:</p>
  
  <pre><code>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&lt;Kursy&gt;();
  }
  </code></pre>
  
  <p>This code works fine! <strong>BUT</strong> when I try to use my classes using LINQ like this:</p>
  
  <pre><code>using (var conn = new SQLiteConnection(DB_PATH))
  {
      var result = conn.Table&lt;Kursy&gt;().Where(k =&gt; k.kantory.id_kantory == 1).FirstOrDefault();
  }
  </code></pre>
  
  <p><strong>It throws me a NotSupportedException!</strong> The messsage is: <strong>Member access failed to compile expression</strong></p>
  
  <p>BUT when I use my classes using LINQ <strong>WITHOUT JOINING</strong> another class it works:</p>
  
  <pre><code>using (var conn = new SQLiteConnection(DB_PATH))
  {
          var result = conn.Table&lt;Kursy&gt;().Where(k =&gt; k.id_kursy == 1).FirstOrDefault();
  }
  </code></pre>
  
  <p>So in the end: my main problem is that <strong>I cannot join more that one table using LINQ query</strong>. Seems that this model in classes is wrong but I really don't know why... </p>
  
  <p>PS. This is <em>Windows Phone 8.1 Application</em>, so I cannot use Entity Framework for that.</p>