Avoiding SQL Server plan cache pollution due to unparameterized Contains queries generated by Entity Framework 6

[skurik]

Hi Erik,

thank you for great articles, including this one. I really hoped this approach would help us get rid of the horrendous non-parameterized queries which seem to be the main problem (query-wise) our database is facing. However, in our case, the resulting execution plan makes it even worse than the original version:

Didn't you encounter a similar problem when using this method?

The following works better, but then I don't think it's something you can produce using EF 6 :-/

DECLARE @idList NVARCHAR(MAX)
DECLARE @splitIds TABLE([Id] UNIQUEIDENTIFIER)

SELECT @idList = N'72a0b76d-a125-4256-84bd-0008377b8551,c79038e8-6868-4aac-80e4-000391a7290c,49114a16-ce5f-4cad-8b37-000e08d91c00'

INSERT INTO @splitIds
SELECT CONVERT(UNIQUEIDENTIFIER, Value)
FROM STRING_SPLIT(@idList, N',')

SELECT *
FROM [prod].[Address] AS [Extent1]
WHERE EXISTS (
    SELECT 1
    FROM @splitIds [Id]
    WHERE ([Id] = [Extent1].[CustomerId]) OR (([Id] IS NULL) AND ([Extent1].[CustomerId] IS NULL))
)

[ErikEJ]

You are keeping me in the dark - please share original EF6 query, string split query and your schema, including indexes

[skurik]

I was just trying if the new SQL would help the performance.

Now that I tried putting it actually into the code, it throws an exception.

I have

[DbConfigurationType(typeof(DbContextConfiguration))]
public abstract class DbContext : System.Data.Entity.DbContext
{
    public DbSet<StringSplitValue> StringSplitResults { get; set; }

    [DbFunction("CodeFirstDatabaseSchema", nameof(String_Split))]
    [DbFunctionDetails(IsBuiltIn = true)]
    public IQueryable<StringSplitValue> String_Split(string @string, string separator)
    {
        var sourceParameter = new ObjectParameter("Source", @string);
        var separatorParameter = new ObjectParameter("Separator", separator);

        return ObjectContext.CreateQuery<StringSplitValue>($"STRING_SPLIT(@Source, @Separator)", sourceParameter, separatorParameter);
    }     

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Conventions.Add(new FunctionsConvention<DbContext>("dbo"));
    }

}    

public class StringSplitValue
{
    [Key]
    public string Value { get; set; }
}

and use it in a LINQ query like

DbContext.String_Split(joined, ",").Any(r => r.Value == s.Id.ToString())

but executing the LINQ query fails with

System.NotSupportedException: The specified method 'System.Linq.IQueryable`1[EntityFramework.Db.StringSplitValue] String_Split(System.String, System.String)' on the type 'EntityFramework.Db.DbContext' cannot be translated into a LINQ to Entities store expression because its return type does not match the return type of the function specified by its DbFunction attribute.
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.FunctionCallTranslator.ValidateReturnType(DbExpression result, TypeUsage actualReturnType, ExpressionConverter parent, MethodCallExpression call, Type clrReturnType, Boolean isElementOfCollection)

[ErikEJ]

Looking at my sample - maybe there is something wrong with the DbFunction declaration.

[vineshkamat]

We are using Database first approach in the Entity framework, was trying the above approach but we are getting the below error.

System.NotSupportedException: 'The specified method 'System.Linq.IQueryable`1[SampleProjectEnity.StringSplitResult] String_split(System.String, System.String)' on the type 'SampleProjectEnity.Workforce_OperationalEntities' cannot be translated into a LINQ to Entities store expression.'

Is the above approach works for Database first as well?

[ErikEJ]

@vineshkamat sure. Please create a new issue and share a full repo and not just an error message

[ErikEJ]

@skurik This line is wrong:

 modelBuilder.Conventions.Add(new FunctionsConvention<DbContext>("dbo"));

It should be referring to a concrete DbContext class.

[timhungdao]

Hi Erik, We really need this feature. However we had performance issue with the legacy ObjectContext. Checkout the EF6 source code we found that access ObjectContext will make EF6 do some heavy work and create a lot of lock contention due to call Assembly.GetCallingAssembly().

Is there anyway we can have this feature without using ObjectContext?

[ErikEJ]

@timhungdao Yes, if you move to EF Core, otherwise no.

[timhungdao]

thanks Erik. Sadly we can't move to EF Core yet. It is a big legacy project :(

[timhungdao]

I think we will try this one https://gist.github.com/ErikEJ/6ab62e8b9c226ecacf02a5e5713ff7bd. It is a great work. Thanks so much Erik

[oysandvik94]

Is it possible that multiple requests that use the TVP table can have their conditions mixed? I don't understand from looking at the code how it ensures that the specific query is using a specific result in the StringSplitResult table.

[ErikEJ]

@oysandvik94 Not sure what you are asking but the shape of the tvp table is not cached/considered. If I misunderstood your question maybe you can elaborate with an example?