Azure SQL dependency (based on EF Core 3.1.7) is not appearing in Application Map of App Insights

Question

We have Azure functions based on .net Core 3.1. We use latest version of EntityFrameworkCore.

It connects to Azure SQL to store/retrieve/query data. We are able to see logs for Azure SQL such as Opening connection, closing connection in Live Stream of App insights sometimes (sometimes may be because of Sampling being enabled)

But, we don't see Azure SQL dependency in Application map of App insights. Even, looking at the traces table, I don't see anything related to Azure SQL.

Is there something we need to enable for Azure SQL to show as dependency? I read in few msdn articles, that it is automatically detected for SQL when you use Microsoft.Data.SqlClient package (and I see EF core has that package installed already internally).

Also a follow up question if above is answered and resolved - is there a way, I can check if connection is disposed/closed or when was the connection opened/closed for given function invocation in App insights?

As per below comment, adding more info,

We add DbContext to the services using following statement in the startup file.

builder.Services.AddDbContextPool<OurDbContext>(options =>
{
    options.UseSqlServer("connectionstring"), builder =>
    {
       builder.EnableRetryOnFailure(3, TimeSpan.FromSeconds(2), null);
    });
});

OurDbContext class has following constructor,

public OurDbContext(DbContextOptions<OurDbContext> options)
    : base(options)
{
}

And then we inject OurDbContext class in different repositories which uses this context to talk to SQL. Similar to below:

public class Repo : IRepo
{
  public Repo(OurDbContext ourDbContext)
  {

  }
  
  public async Task AddAsync(Entity entity)
  {
    ourDbContext.AddAsync(entity);
    ourDbContext.SaveChangesAsync()
  }
}

We inject these repos in Function classes and call above methods such as

await _repo.AddAsync()

We use below EFCore packages

we have below in host.json file.

{
    "version": "2.0",
    "logging": {
        "applicationInsights": {
            "samplingExcludedTypes": "Request",
            "samplingSettings": {
                "isEnabled": true
            }
        }
    }
}

Note: I tried below link just to check if sql dependency is showing up in App insights, though it does not use the configuration of EFCore/latest version of Azure functions which I am using. Only thing I have added is APPINSIGHTS_INSTRUMENTATIONKEY in my local settings.

https://dev.to/azure/using-entity-framework-with-azure-functions-50aa GitHub Source code: https://github.com/jeffhollan/functions-csharp-entityframeworkcore

With above, I was able to see SQL dependency in my app insights. But, when I modified above to the version of Azure functions, .net core, EFCore, I am using for my current project, SQL dependencies stopped appearing in App insights. Though, adding below logging level shows debug logs in Console.

"Logging": {
    "LogLevel": {
      "Default": "Debug",
    }
}

Screenshot as per below comment for KrishnenduGhosh-MSFT.

Logs from stackify.

Answer 1

Update your logging section in host.json as below to allow Information level log (note I added logLevel in the existing config you posted above). By default it's Warning if you do not specify. As mentioned in the Note here, dependencies are logged with Information level. Also note excludedTypes (not samplingExcludedTypes) should be inside samplingSettings as per documentation.

{
  "version": "2.0",
  "logging": {
    "applicationInsights": {
            "samplingSettings": {
                "isEnabled": true,
                "excludedTypes": "Dependency;Request"
            }
        },
    "logLevel": {"default": "Information"}
  }
}

Also for Azure function, you should not add microsoft.applicationinsights.aspnetcore nuget and builder.Services.AddApplicationInsightsTelemetry(); in Startup. That's for asp.net core application. Function should not have that https://learn.microsoft.com/en-us/azure/azure-functions/functions-dotnet-dependency-injection#logging-services