Improve Entity Framework 6.x query identification

[plutora-steve-g]

Suggestion for a feature. Entity Framework 6.x (not sure about EF Core) generates queries in patterns that get grouped together in New Relic.

I'll list a couple of common groupings we see, noting I've only tried EF using Sql Server not PostgreSql and the other databases it can work with.

generated_keys declare

MSSQL generated_keys declare is an example summary heading that could be reported as "MSSQL insert"

The pattern is regular

DECLARE @generated_keys table([ID] uniqueidentifier) INSERT [dbo].[MyTable] etc etc

MSSQL (subquery) select

EF generates very complicated queries that no sane human would write, and these often end up as (subquery) select -- it's about 40% of some of our apps. Could be there's a way to categorize these statements so we can tell them apart. Some ideas:

1. Add the current transaction name: MSSQL (subquery) select - /foobars/tanstafl
2. Add a (cheap) hash: MSSQL (subquery) select - 0xdeadbeef
3. Analyse the query further -- having written some very complicated regexes to deal with EF-generated SQL I'd actually not go this route, but throwing it out there.

skip the first command

I've recently had to concatenate a stored proc call to the start of every INSERT/UPDATE/DELETE to set user context information which means ALL INSERT/UPDATE/DELETEs get bucketed into MSSQL dbo.uspUserContext ExecuteProcedure.

The bulk of our queries are SELECTs so this isn't the end of the world, but it'd be nice if there was a way to indicate to the agent to skip to the first semicolon or line. Perhaps something like this

/* NewRelicIgnore */ exec uspUserContext @userId;
UPDATE Foobar...

what next?

I assume New Relic knows how popular EF is amongst their customers. If there's appetite for some of these changes I might have a go at it, checking first.

[nr-ahemsath]

Hello @plutora-steve-g, thanks for submitting this issue.

We currently do not have any instrumentation specific to Entity Framework; as you pointed out, we are aware of the popularity of EF and we are currently considering adding EF-specific instrumentation to our roadmap (although this might be limited to EF Core). Thank you for the detailed description of your use cases and desired behavior, this will help us prioritize and plan our work for EF support.

Regarding the ability to more uniquely and meaningfully identify the datastore operations generated by EF, you can use a relatively new feature of the agent as described in this PR: https://github.com/newrelic/newrelic-dotnet-agent/pull/799 It involves adding a comment like /* NewRelicQueryName: customQueryName */ and that query will be tagged with [customQueryName] in the UI. It looks like that approach would at least work since the author of that PR is also using EF 6. We do realize this requires code changes in your application and is therefore not an ideal solution, but hopefully it could help as a workaround until we add better support for EF.

[plutora-steve-g]

@nr-ahemsath thanks for the #799 suggestion, I've implemented that and it works as expected. I had to write a (rather terrible) sql "parser" to re-create the db transaction name the same way New Relic does. The suggested /* NewRelicIgnore */ would bypass this need. I thought about using the same parsing code from the New Relic repo, didn't go that way in the end.

[workato-integration[bot]]

https://issues.newrelic.com/browse/NEWRELIC-3679

[workato-integration[bot]]

This issue won't be actioned.