Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
klosels
Posts: 9
Joined: Wed 31 Oct 2018 16:33

Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905

Post by klosels » Tue 24 Aug 2021 13:33

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.

Shalex
Site Admin
Posts: 9358
Joined: Thu 14 Aug 2008 12:44

Re: Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905

Post by Shalex » Fri 27 Aug 2021 10:33

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.

klosels
Posts: 9
Joined: Wed 31 Oct 2018 16:33

Re: Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905

Post by klosels » Tue 31 Aug 2021 08:56

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?

Shalex
Site Admin
Posts: 9358
Joined: Thu 14 Aug 2008 12:44

Re: Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905

Post by Shalex » Tue 31 Aug 2021 14:58

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.

klosels
Posts: 9
Joined: Wed 31 Oct 2018 16:33

Re: Exception "OUTER APPLY is not supported by SQLite" in 5.17.1905

Post by klosels » Wed 01 Sep 2021 11:19

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

Post Reply