Hi,

I'm having issue with ORM Profiler, I'll try to give in as much details as I can.

First, quick intro and background. We have WebAPI that uses both Npgsql (5.0.10) and Devart for Postgresql (9.13). App is running on .NET 471.

Npgsql is used with LLBLGen 5.6 queries, and Devart is used for queries with Entity framework.

Code for interceptor initialization is as follows

SD.Tools.OrmProfiler.Interceptor.InterceptorCore.Initialize("WebAPI");

Once app is started, I'm seeing only Devart queries (EF) and raw SQL queries executed via connections opened by Devart over DbConnectionFactory, and I'm not seeing LLBLGEn/Npgsql queries.

I have tried with ORMProfiler 2.0.1 and ORMProfiler 2.0.4. Also tried with different versions of interceptor libs, including 1.5 (Interceptor EFv6.NET40) and 2.0.3 (Interceptor.EFv6).

If I use Interceptor.NetFull (2.0.3), then I'm having exceptions with Devart connections due to casting of ProfilerDbConnection to DevartDbConnection

Now, fun part starts here. I have windows service with same stack as WebAPI (doing different thing obviously), which I can profile without any issues, both EF queries and LLBLGen queries. Same versions of Devart, Npgsql, LLBlgen, even with ORMProfiler 2.0 and old interceptor EFv6.NET40.

Please assist, as I'm getting desperate here.

Thanks.

It's a bit problematic as Entity Framework 6 requires additional code in the interceptor which will cause problems when used in normal ADO.NET, hence the separate interceptor for EF.

As you use both llblgen and ef in your app, which interceptor did you reference? Npgsql doesn't come with an installer, you registered its DbProviderFactory in the web.config file perhaps? If so, that's likely being processed too late, the DbProviderFactories table modifications have likely already taken place when the .NET runtime starts processing that file.

Could you make sure npgsql's factory is registered in the machine.config file of .NET? (C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config)

Hi Otis,

Thanks for quick reply.

I have tried the following in web API project
- 1.5 interceptor (EFv6.Net40) - I'm getting only EF/Devart queries
- 2.0 interceptor (EFv6) - I'm getting only EF/Devart queries
- 2.0 interceptor (NetFull) - I'm getting devart exceptions due to invalid cast from ProfilerDbConnection to DevartDbConnection

Excerpt from Web.config

  <system.data>
    <DbProviderFactories>
      <remove invariant="Devart.Data.PostgreSql" />
      <add name="dotConnect for PostgreSql" invariant="Devart.Data.PostgreSql" description="Devart dotConnect for PostgreSql"
        type="Devart.Data.PostgreSql.PgSqlProviderFactory, Devart.Data.PostgreSql" />
      <remove invariant="Npgsql" />
      <add name="Npgsql" invariant="Npgsql" description="Npgsql" type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>

Npgsql unfortunately doesn't have option to install in GAC anymore, but I could try and follow these guidelines https://stackoverflow.com/questions/65234620/npgsql-v5-0-0-gac-installation

But I don't think that will help, because profiling works in windows service as intended, with same stack as WebAPI. Tried running with profiler 2.0.1 and old 1.5 interceptors (EFv6.NET40). System.Data/DbProviderFactories are identically defined there.

BTW you might have a bug in forum (bad request - 400 on saving), I've just lost entire post and had to retype

ikalafatic wrote:

Hi Otis,

Thanks for quick reply.

I have tried the following in web API project
- 1.5 interceptor (EFv6.Net40) - I'm getting only EF/Devart queries
- 2.0 interceptor (EFv6) - I'm getting only EF/Devart queries
- 2.0 interceptor (NetFull) - I'm getting devart exceptions due to invalid cast from ProfilerDbConnection to DevartDbConnection

Excerpt from Web.config

  <system.data>
    <DbProviderFactories>
      <remove invariant="Devart.Data.PostgreSql" />
      <add name="dotConnect for PostgreSql" invariant="Devart.Data.PostgreSql" description="Devart dotConnect for PostgreSql"
        type="Devart.Data.PostgreSql.PgSqlProviderFactory, Devart.Data.PostgreSql" />
      <remove invariant="Npgsql" />
      <add name="Npgsql" invariant="Npgsql" description="Npgsql" type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>

Yes, that's what I suspected, and this makes the registration of the factory here being done after the interceptor already has been initialized. If the factory is present in the machine.config file, the factory would be in the table when the interceptor is initialized.

Npgsql unfortunately doesn't have option to install in GAC anymore, but I could try and follow these guidelines https://stackoverflow.com/questions/65234620/npgsql-v5-0-0-gac-installation

It's a bit tricky indeed. What you also could try is to delay the initialization of the interceptor. It's key that it's initialized before any ado.net work is done so a factory is cached already, but till then it might be you can get away with it: try to obtain the factories table from DbProviderFactories before you call initialize and check if the npgsql factory is there.

The casting errors might be caused by the fact that EF uses wrapped connections, like I said, they're not compatible hence there's a separate interceptor.

But I don't think that will help, because profiling works in windows service as intended, with same stack as WebAPI. Tried running with profiler 2.0.1 and old 1.5 interceptors (EFv6.NET40). System.Data/DbProviderFactories are identically defined there.

Migth be the windows service initializes things in a slightly different order which makes the config file being read earlier than the interceptor initialization call is made, which makes the npgsql factory be present in the DbProviderfactories table. The whole interception process on .netfx is a bit complicated anyway, we overwrite entries in the static (in the appdomain) DbProviderfactories datatable, but EF (and llblgen too) cache factories once obtained, so they're no longer read from that table after the first time.

BTW you might have a bug in forum (bad request - 400 on saving), I've just lost entire post and had to retype

Hmm... might be a timeout related thing on the database... no idea to be honest, it works fine normally but I have had it occasionally too, never have been able to track it down tho what it was caused by... Sorry for this, it can be annoying indeed

Otis wrote:

It's a bit tricky indeed. What you also could try is to delay the initialization of the interceptor. It's key that it's initialized before any ado.net work is done so a factory is cached already, but till then it might be you can get away with it: try to obtain the factories table from DbProviderFactories before you call initialize and check if the npgsql factory is there.

Ha! You gave me a hint here It's key that it's initialized before any ado.net work.

I tried with this and had both entries.

                    var npgsqlFactory = DbProviderFactories.GetFactory("npgsql");
                    string npgsqlFullTypeName = npgsqlFactory.GetType().FullName;

                    var devartFactory = DbProviderFactories.GetFactory("Devart.Data.PostgreSql");
                    string devartFullTypeName = devartFactory.GetType().FullName;

However, based on your hint above, it seems that I've initialized the interceptor too late, part of the application already accessed the DB where the original initialization of the interceptor happened.

I have moved the interception initialization call to the earliest possible place of application start, and now I'm seeing everything in the profiler, as expected.

Otis wrote:

Hmm... might be a timeout related thing on the database... no idea to be honest, it works fine normally but I have had it occasionally too, never have been able to track it down tho what it was caused by... Sorry for this, it can be annoying indeed

No worries

Thanks so much!