Devart Forums

TL;DR: How can dotConnect for SQLite be configured to use an alternatively named SQLite shared library?

Full story: we're using dotConnect for SQLite for a long time (licensed customer) in conjunction with an alternative SQLite library supporting encryption (https://github.com/utelle/SQLite3MultipleCiphers).

On Windows, things are easy as we simply rename the DLL as sqlite3.dll and place it beside the executable where it gets picked up automatically.

As we're starting to support .NET5+ on Linux, things get more complicated: the shared library is by default not loaded from the executable's directory (unless modifying LD_LIBRARY_PATH), and placing it in e.g. /usr/lib should be supported. Therefore, however, we'd need to keep the library's original name of libsqlite3mc.so to avoid conflicts with a standard libsqlite3.so.
How could we do this and tell dotConnect to load that differently named SQLite library?

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

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?

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 wrote: ↑Mon 30 Nov 2020 15:38 We will notify you when this format is supported by our provider.

Would you mind being a bit more specific?
According to the revision history it already should be supported since 4.6 (released just 7 years ago!):

4.6.224 18-Apr-13
The support of the URI format for the connection string is implemented

Are you confirming the current behaviour as a bug?
When do you expect it to be fixed?
Since we do pay an annual subscription - will it speed things up if I open a support ticket?

I'm trying to connect to a SQLite database using the URI syntax. However, it fails with a "unsupported path format" exception as soon as I include the absolute (Windows) path in the URI. If I leave the path out using only the file name but setting the process working directory accordingly, it works.
Connection string examples:

• Works with cwd set accordingly:

  Code: Select all

  Data Source="file:test.db?cache=private"

• Fails:

  Code: Select all

  Data Source="file:///D:/my/path/test.db?cache=private"

The ultimate goal here is to use an alternative sqlite3.dll which accepts some additional URI parameters, but the same problem occurs using the sqlite3.dll provided with dotConnect with the samples given above.
The same URIs work flawlessly in a sqlite shell, though, so it seems there is a bug in dotConnect?
Tested using dotConnect 5.12.1422 and 5.16.1759

We are using dotConnect for SQLite 5.12.1422 and EF6.2.
One property in our model is of type DateTimeOffset, in the database the values are correctly stored in a culture-agnostic way using ISO formatting.
We also implement IDbCommandInterceptor to log database activity. There however, logging of a DbParameter.Value.ToString() fails for the SQLiteDateTime with the attached exception if CurrentUICulture is switched to a different value than the default for the system.

Exception:

Sorry for the slow reply - yes, we're using EF 6.2

Hoping you'll find a way to support this as we'll probably need it soon and where we have no workaround.

I thoroughly read the referred topic, but I still don't see how to approach the issue.
I already know:

• *how to register a custom function.
  *that I can then use it in a plain SQL query.
  *that registering as "regexp" will map it to the SQLite REGEXP operator (still in plain SQL)

but that doesn't yet help to use the function in LINQ to Entities because

• *the approach in the topic linked further on applies to collating functions specific to a column, but there's no similar configuration option in SQLiteEntityProviderConfig
  *and no Regexp method exists in SqlFunctions which would map to REGEXP operator either

I know that it's possible to register arbitrary functions with SQLite using the SQLiteConnection.RegisterFunction method and already used them successfully in pure SQL statements.

Is it also possible to use a registered function, specifically for the REGEXP SQLite operator, in a LINQ to Entities query?
I.e. how would I write the query on the db Set, e.g.: Thanks for any advice...

I'm using dotConnect for SQLite with EF 6.1 and EF migrations.

Is it possible (and how) to define a SQLiteScalarFunction which then could be called in the SQL code which is executed via DbMigration.Up when running the migration?