ef6 | Entity Framework | Object-Relational Mapping library

Other than the very large amount of data that you are intent on returning, the main problem is that the way your code is structured means that for each of the 250,000 Series you are performing another trip to the database to get the Measurements for the Series and a further 2 trips to get the front/side DropPhotos for each Measurement. Apart from the round-trip time for the 750,000 calls this completely avoids taking advantage of SQL's set-based performance optimisations.

Try to ensure that EF submits as few queries as possible to return your data, preferably one:

You don't need to do anything spacial (like your inerceptor) if you add this code before your query to the data base you will get the query and the params on the output window of the Visual studio:

Since EF Core is not willing to translate such expression (it's a well known limitation that Contains with primitive value is the only supported operation on in-memory collection in L2E query since it directly translates to SQL IN operator), you have to do it yourself. Any is basically equivalent of Or as you mentioned, so for not so big list and top level query it could easily be done by using some predicate builder implementation to dynamically build || predicate. Or directly using the the Expression class methods as in this custom extension method from my answer to How to simplify repetitive OR condition in Where(e => e.prop1.contains() || e.prop2.contains() || ...):

To solve the problem, you need SQL Server installed in your machine.

If you have windows 10 in your system, check if you have SQL Server installed in Apps and features.

You can also check if you have any version of Microsoft.SqlServer.Types in your GAC_MSIL (Global Assembly Cache) directory on C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types, if this folder is empty or does not exist, you will need to install Microsoft.SqlServer.Types in your machine.

Thanks @JeroenMostert for kinda pointing me in the right direction. I did some more digging and found this page that has the answer i was looking for, which is to do the following:

SQLite.Interop.dll does not act as a lib assembly dll. That is not its role. And it should be a content file rather than a assembly dll. So it should not be packed as lib.

To create such custom nuget package, you should first pack System.Data.SQLite.dll, System.Data.SQLite.Linq.dll, System.Data.SQLite.EF6.dll as lib. See this document.

and then pack SQLite.Interop.dll as content.

Also, to make the content file be copied into the output folder of the main project when you install the nuget package, you have to use a .props or targets file to realize it.

1) create a file called .props into your class library project. And it should be the same name as your nuget package. In your side, it should be named as Custom.SQLite.Name.props. Otherwise, it will not work.

And then add these into the file:

Postgres has limit for maximum length of identifier - 63 bytes, so both of your foreign keys end up with the same name - FK_public.UserBankAccountTransactions_public.UserBankAccounts_P.

So you need to find a way to specify FK name. Not sure that it can be done via attribute/fluent api (at least I was not able to find it) but as workaround you should be able to specify it via name parameter of TableBuilder.ForeignKey method in the generated migration (which is usually not a good thing to do):

please note that there are many differences between EF6 and EF Core, as @marc_s mentioned in comment, the EDMX file format for models does not be supported in EF Core.

To generate code for DbContext and entity types for your existing database, you can use Scaffold-DbContext command or dotnet ef dbcontext scaffold command.

Try this: