Hi,
we updated from 5.17.1782 to 5.17.1905 recently, now we're running into an exception with unchanged code that never occurred before.
The exception is:
System.Data.Entity.Core.EntityCommandCompilationException:
OUTER APPLY is not supported by SQLite.
The query is simple but tries to fetch data from a lot of tables like this (each related entity lives in its own table)
ctx.Set<A>()
.Include(t => t.ChildrenB)
.Include(t => t.ChildrenC)
.Include(t => t.ChildrenD)
.Include(t => t.ChildrenE)
.Include(t => t.ChildrenF)
.Include( ..... )
.Load()
Is there any known bug that could cause this?
Was the way dotConnect constructs a SQL query out that queryable change recently?
IMHO dotConnect shouldn't in the first place even try to construct a select using something that SQLite doesn't support.
By reducing the number of Includes the exception disappears, but performance of the application suffers.
Strangely, if the exception occurrs also depends on database contents, it's unclear though in what way.
Any help is welcome.
NB: we are a licensed customer with an active subscription.
Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905
Re: Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905
Had you upgraded the version of EntityFramework.dll used in your project before the issue appeared? Refer to a similar discussion at viewtopic.php?t=42269.
Re: Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905
We upgraded EntityFramework and dotConnect simultaneously, EF was updated from 6.2 to 6.4.4
Are you suggesting this is a bug in EF 6.4.4? If so, is there a known bug report for it?
Are you suggesting this is a bug in EF 6.4.4? If so, is there a known bug report for it?
Re: Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905
Microsoft is focused on the MS SQL Server when generating EF expression tree that cannot be translated to SQL for separate DBMS in some cases. Generally, the workaround can be found: either rewrite the request in a different way or downgrade to the EF version your query works with.
Re: Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905
For the record and anyone having similar problems and reading this: the problem may relate to a change in optimizations in EF 6.3 as noted here: https://github.com/dotnet/ef6/issues/1566
I'm not convinced that this is an EF-only problem though...
I'm not convinced that this is an EF-only problem though...