Performance issue: SQLite and C# EntityFramework

I have a performance problem in this specific situation:

• Consider two tables A and B


• Both tables have a foreign key for other table (C): ID


• There are indexes for ID and for the ACC field

In the following query:

SELECT A.ACC FROM A LEFT OUTER JOIN B ON A.ACC = B.ACC AND A.CID = B.CID WHERE B.ACC IS null

I use the EXPLAIN QUERY PLAN and I checked that is using the index for ID, and takes 10 minutes to execute, if I force to use the other index and only takes 100ms.

The problem is that I cannot force in the code (in the CreateQuery) to use "INDEXED BY" because it's not accepted... I try to remove the index for ID in the app but it still not use the other index.

Tests executed (directly on the DB):

• Initial query: 10 minutes


• Same query not using the foreign keys: 25 ms


• Same query using ACC index: 100 ms


• Same query not using the WHERE clause: 100 ms


• Same query using in the WHERE clause a field of table A: 100ms

Any help would be appreciated :)

Thanks!