Bug 60481 - EF Core can't query table with foreign key on IOS
Summary: EF Core can't query table with foreign key on IOS
Alias: None
Product: iOS
Classification: Xamarin
Component: General ()
Version: XI 11.2 (d15-4)
Hardware: PC Windows
: --- normal
Target Milestone: Untriaged
Assignee: Bugzilla
Depends on:
Reported: 2017-11-01 19:20 UTC by Darren
Modified: 2017-11-02 18:38 UTC (History)
2 users (show)

Is this bug a regression?: ---
Last known good build:

Project to reproduce (376.51 KB, application/x-zip-compressed)
2017-11-01 19:20 UTC, Darren

Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.

Please create a new report on Developer Community or GitHub with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:

Description Darren 2017-11-01 19:20:20 UTC
Created attachment 25534 [details]
Project to reproduce

I created two SQLite tables, one with a foreign key reference to the other.  When a query runs against the table with the FK on IOS it doesn't find the column.  The target contains the prepopulated db file as an asset or resource and overwrites the local db each time.  

SQLite Error 1: 'no such column: c.CustomerId'.

I thought it may be forcing a reverse reference but added column CustomerId to both tables did not change the error.

The example runs fine in the Android project.

XCode 9.0.1
Comment 1 Vincent Dondain [MSFT] 2017-11-01 23:35:42 UTC

Thanks for the bug report.

I'm having issues reproducing this, could you be more specific with the repro steps?

Is it happening on simulator or device?

I can run the iOS project on simulator just fine, I fail to see what you're doing exactly you just seem to get the DB path in App.xaml.cs from `FileAccessHelper` but you don't do anything with it: `var dbPath = service.GetDBPathAndCreateIfNotExists("sales.db");`. I don't see your queries.

How do I get your `SQLite Error 1: 'no such column: c.CustomerId'.`?

Comment 2 Darren 2017-11-02 16:06:53 UTC
It's happening on both the iPad Pro simulator and a iPad Mini 2.  

GetDBPathAndCreateIfNotExists is poorly named.  It creates a file path in the SpecialFolder.Personal location then deletes any file with that name, then copies a sample sqlite database for the application resources folder.  This is done to make sure I start with the same file each time.  The dbcontext uses the same method to construct the path to the dbfile.

It is failing on IOS on line 29 of the MainViewModel.cs 

var customer = context.Customers.Skip(1).First();  

If I can get setup to step into the EF Core code on IOS I will gather more detail.
Comment 3 Darren 2017-11-02 16:32:19 UTC
I was able to get a call stack

{Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: c.CustomerId'.
  at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC (System.Int32 rc, SQLitePCL.sqlite3 db) [0x00053] in <06a93310d75849fab69d91cbdb9b969b>:0 
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x000dd] in <06a93310d75849fab69d91cbdb9b969b>:0 
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) [0x00000] in <06a93310d75849fab69d91cbdb9b969b>:0 
  at System.Data.Common.DbCommand.ExecuteReader () [0x00000] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute (Microsoft.EntityFrameworkCore.Storage.IRelationalConnection connection, Microsoft.EntityFrameworkCore.Diagnostics.DbCommandMethod executeMethod, System.Collections.Generic.IReadOnlyDictionary`2[TKey,TValue] parameterValues) [0x000d6] in <71d9d043aef04336aba3ddeb79c061d9>:0 
  at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader (Microsoft.EntityFrameworkCore.Storage.IRelationalConnection connection, System.Collections.Generic.IReadOnlyDictionary`2[TKey,TValue] parameterValues) [0x0000c] in <71d9d043aef04336aba3ddeb79c061d9>:0 
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1+Enumerator[T].BufferlessMoveNext (System.Boolean buffer) [0x000f8] in <71d9d043aef04336aba3ddeb79c061d9>:0 
  at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions+<>c__DisplayClass12_0`2[TState,TResult].<Execute>b__0 (Microsoft.EntityFrameworkCore.DbContext c, TState s) [0x00000] in <0998bf911f014e7884d2695c95a67016>:0 
  at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult] (TState state, System.Func`3[T1,T2,TResult] operation, System.Func`3[T1,T2,TResult] verifySucceeded) [0x00011] in <0998bf911f014e7884d2695c95a67016>:0 
  at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult] (Microsoft.EntityFrameworkCore.Storage.IExecutionStrategy strategy, System.Func`2[T,TResult] operation, System.Func`2[T,TResult] verifySucceeded, TState state) [0x0001f] in <0998bf911f014e7884d2695c95a67016>:0 
  at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult] (Microsoft.EntityFrameworkCore.Storage.IExecutionStrategy strategy, TState state, System.Func`2[T,TResult] operation) [0x00000] in <0998bf911f014e7884d2695c95a67016>:0 
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1+Enumerator[T].MoveNext () [0x0001e] in <71d9d043aef04336aba3ddeb79c061d9>:0 
  at System.Linq.Enumerable.TryGetFirst[TSource] (System.Collections.Generic.IEnumerable`1[T] source, System.Boolean& found) [0x00045] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at System.Linq.Enumerable.First[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00000] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
  at System.Reflection.MonoMethod.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00032] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
--- End of stack trace from previous location where exception was thrown ---
  at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (System.Exception source) [0x00000] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at System.Linq.Expressions.Interpreter.ExceptionHelpers.UnwrapAndRethrow (System.Reflection.TargetInvocationException exception) [0x00000] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at System.Linq.Expressions.Interpreter.MethodInfoCallInstruction.Run (System.Linq.Expressions.Interpreter.InterpretedFrame frame) [0x00035] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at System.Linq.Expressions.Interpreter.Interpreter.Run (System.Linq.Expressions.Interpreter.InterpretedFrame frame) [0x00015] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at System.Linq.Expressions.Interpreter.LightLambda.Run1[T0,TRet] (T0 arg0) [0x0001c] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler+<>c__DisplayClass17_0`1[TResult].<CompileQueryCore>b__0 (Microsoft.EntityFrameworkCore.Query.QueryContext qc) [0x00031] in <0998bf911f014e7884d2695c95a67016>:0 
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult] (System.Linq.Expressions.Expression query) [0x0006e] in <0998bf911f014e7884d2695c95a67016>:0 
  at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult] (System.Linq.Expressions.Expression expression) [0x00000] in <0998bf911f014e7884d2695c95a67016>:0 
  at System.Linq.Queryable.First[TSource] (System.Linq.IQueryable`1[T] source) [0x0000e] in /Library/Frameworks/Xamarin.iOS.framework/Versions/ 
  at App4.MainViewModel.get_MyCustomer () [0x00002] in D:\source\App4\App4\App4\MainViewModel.cs:29 }
Comment 4 Darren 2017-11-02 17:42:00 UTC
I tracked down the issue.  It appeared to the db file wasn't getting copied correctly on the IOS project.  The sales.db file in the Resources folder was set to an asset type instead of a bundle resource.  

The strange thing is I think that NSBundle.MainBundle.PathForResource should have been returning null.

I've found the source code for EF core so it should be easier to debug in the future.

Sorry for the misleading report.
Comment 5 Vincent Dondain [MSFT] 2017-11-02 18:38:20 UTC
Hi Darren, glad you found the cause of the issue.

`NSBundle.MainBundle.PathForResource` does return null for me with Xamarin.iOS Version: (current latest master) and VSMac Version 7.4 Preview (7.4 build 372).

Could you please give us you full version information so I understand why `NSBundle.MainBundle.PathForResource` is not returning null for you?