CODEWITHSUNDEEP
sql-serverlinqcachingcompilation.net-4.0
Lajos Arpad
Lajos Arpad

Reputation: 76519

How to compile a LINQ query with a List parameter?

I have a problem with one of my LINQ queries. The query looks like this:

        Dim elements = (From filterSum In dataContext.GetTable(Of TblFilterSum)
                        Join filterProdInSum In dataContext.GetTable(Of TblFilterProdsInSum)
                    On filterSum.SumID Equals filterProdInSum.SumID
                        Join filterProd In dataContext.GetTable(Of TblFilterProd)
                    On filterProdInSum.ProdID Equals filterProd.ProdID
                        Join filterElementInProd In dataContext.GetTable(Of TblFilterElementsInProd)
                    On filterProd.ProdID Equals filterElementInProd.ProdID
                        Join filterElement In dataContext.GetTable(Of TblFilterElement)
                    On filterElementInProd.ElementID Equals filterElement.ElementID
                        Where sumIDs.Contains(filterSum.SumID)).Select(Function(r) New With {.SumID = r.filterSum.SumID, .ProdID = r.filterProd.ProdID, .filterElement = r.filterElement, .IsNotSum = r.filterSum.IsNot, .IsNotProd = r.filterProd.IsNot}).ToList

This query loads records from a 5-dimensional problem-space, defined by the tables:

The filter I use in the Where clause is that tblFilterSum.SumID is inside a List(Of Integer) called sumIDs. The Linq query is logically impeccable and has just the result I need. However, executing it takes for an eternity, 20s execution time on average. This is the SQL the LINQ query generates:

-- Region Parameters
DECLARE @p0 Int = 12168
DECLARE @p1 Int = 12157
DECLARE @p2 Int = 11948
DECLARE @p3 Int = 11951
DECLARE @p4 Int = 11952
DECLARE @p5 Int = 11950
DECLARE @p6 Int = 11961
DECLARE @p7 Int = 12153
DECLARE @p8 Int = 12154
DECLARE @p9 Int = 12149
DECLARE @p10 Int = 12158
DECLARE @p11 Int = 11954
DECLARE @p12 Int = 11955
DECLARE @p13 Int = 11956
DECLARE @p14 Int = 11957
DECLARE @p15 Int = 11958
DECLARE @p16 Int = 11959
DECLARE @p17 Int = 12159
DECLARE @p18 Int = 12164
DECLARE @p19 Int = 12150
DECLARE @p20 Int = 12151
DECLARE @p21 Int = 12152
DECLARE @p22 Int = 12156
DECLARE @p23 Int = 12161
DECLARE @p24 Int = 12167
DECLARE @p25 Int = 11962
DECLARE @p26 Int = 12155
DECLARE @p27 Int = 12183
DECLARE @p28 Int = 12182
DECLARE @p29 Int = 12165
DECLARE @p30 Int = 12166
DECLARE @p31 Int = 11953
DECLARE @p32 Int = 12163
DECLARE @p33 Int = 12181
DECLARE @p34 Int = 12180
DECLARE @p35 Int = 12160
DECLARE @p36 Int = 12162
-- EndRegion
SELECT [t0].[SumID], [t2].[ProdID], [t4].[ElementID], [t4].[Field], [t4].[Operator], [t4].[Result], [t4].[IsCustom], [t4].[IsNot], [t4].[DisplayOrder], [t4].[FilteredColumnID], [t4].[ColumnMappingID], [t4].[ResultPointerToAssetPath], [t4].[ResultCustomColumnMappingID], [t0].[IsNot] AS [IsNotSum], [t2].[IsNot] AS [IsNotProd]
FROM [dbo].[tblFilterSum] AS [t0]
INNER JOIN [dbo].[tblFilterProdsInSum] AS [t1] ON [t0].[SumID] = [t1].[SumID]
INNER JOIN [dbo].[tblFilterProd] AS [t2] ON [t1].[ProdID] = [t2].[ProdID]
INNER JOIN [dbo].[tblFilterElementsInProd] AS [t3] ON [t2].[ProdID] = [t3].[ProdID]
INNER JOIN [dbo].[tblFilterElement] AS [t4] ON [t3].[ElementID] = [t4].[ElementID]
WHERE [t0].[SumID] IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36)
-- Context: SqlProvider(Sql2008) Model: MappedMetaModel Build: 4.6.1055.0
GO

This runs instantly if I run it directly, therefore the problem is that LINQ under .NET Framework 4.0 compiles the query each time it is being executed and there is no cache whatsoever. There is a possibility to cache the results though: System.Data.Linq.CompiledQuery.Compile is a useful method, with which one can compile the LINQ query. The Func it returns could be reused again and again with different parameters, working around the always-compile strategy used at .NET Framework 4.0. The idea is to create a unique signature to each possible query and using a Shared Dictionary we could compile every kind of query once, store into the cache and later reuse it with other attributes. This sounds good, but looking at the possible usages:

Public Shared Function Compile(Of TArg0 As DataContext, TResult)(query As Expression(Of Func(Of TArg0, TResult))) As Func(Of TArg0, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TResult))) As Func(Of TArg0, TArg1, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TResult))) As Func(Of TArg0, TArg1, TArg2, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TArg14, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TArg14, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TArg14, TResult)

Public Shared Function Compile(Of TArg0 As DataContext, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TArg14, TArg15, TResult)(query As Expression(Of Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TArg14, TArg15, TResult))) As Func(Of TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TArg14, TArg15, TResult)

I quickly realized I have two problems. For once, the Compile method has overloads with different possible parameter values and the same number of parameters for the Func it returns. This is the less serious problem, since I can cache a compiled query for each number of elements I had. It wastes some memory, but not that much. If it gets too much, I will store it with Redis, but it is a different problem. The real problem is that the number of parameters cannot be more than sixteen and my List could contain easily more elements. To this problem, the solution would be to convert sumIDs into a String, with a separator between each number and the string would start and end with that separator, like this:

,5,2,7,34,764,346,1,

and to check whether this String contains the given tblFilterSum.SumID converted into String like this:

"," & tblFilterSum.SumID & ","

but while this would work, it would not be able to use the indexes and it would compare strings, which would be very slow. Since I have these problems and the task was urgent, I implemented this with SQL, but wonder whether I can compile a LINQ query and make sure that arbitrarily many parameters could be passed as a List, for instance. Hence my question:

How to compile a LINQ query with a List as parameter?

EDIT:

If I knew how to contact the creators of LINQ, then I believe I could find out the answer myself. I have searched on google for LINQ, but unfortunately I have not found contact info.

Upvotes: 3

Views: 544

Answers (1)

felipe
felipe

Reputation: 682

Queries that use Contains() cannot be automatically cached. So the solution has to be to avoid the Contains method. To do so we will have to create an equivalent expression (f => f.SumID == x || f.SumId == y ...) via the Linq Expressions API.

The following solution uses the ExpressionCombiner from netfx. This is C#, conversion to VB should be simple.

public static IQueryable<T> WherePropertyIn<T, TProp>(this IQueryable<T> src, Expression<Func<T, TProp>> property, IEnumerable<TProp> values) {
    var valuesList = values.ToList();
    // If no values passed, then nothing matches
    if (!valuesList.Any()) {
        return src.Where(_ => false);
    }
    // This builds the 'f => f.Prop == x || f.Prop == y ...' expression
    Expression<Func<T, bool>> expr = valuesList
                                        .Select(val => property.EqualTo(val)) // Here we have a list of 'f => f.Prop == x' style expressions
                                        .Aggregate(ExpressionCombiner.Or); // And combine them with ||
    return src.Where(expr);
}

// Creates an expression 'f => f.Prop == val' out of expression 'f => f.Prop' and value 'val'
private static Expression<Func<T, bool>> EqualTo<T, TProperty>(this Expression<Func<T, TProperty>> leftHand, TProperty val) {
    // If we don't wrap in property access, LINQ to Entities uses the value directly instead of via a sql parameter, thus breaking caching.
    Expression rightHand = Expression.Constant(val, typeof(TProperty)).WrapInPropertyAccess();
    Expression comparison = Expression.Equal(leftHand.Body, rightHand);
    return Expression.Lambda<Func<T, bool>>(comparison, leftHand.Parameters);
}

/// <summary>
/// Returns an expression around the ConstantExpression, enabling LINQ to Entities to generate parameterized queries
/// </summary>
/// <param name="constant"></param>
/// <returns></returns>
private static UnaryExpression WrapInPropertyAccess(this ConstantExpression constant) {
    Tuple<object> container = new Tuple<object>(constant.Value);
    Expression containerExpression = Expression.Constant(container, typeof(Tuple<object>));
    MemberExpression propAccess = Expression.Property(containerExpression, "Item1");
    UnaryExpression result = Expression.Convert(propAccess, constant.Type); // Cast back the object to the value type
    return result;
}

This code can now be used by your query:

Dim filterSumSubQuery = dataContext.GetTable(Of TblFilterSum).WherePropertyIn(Function(t) r.SumID,  sumIDs)
' Subquery now contains only the TbFilterSum tuples that have the right SumIDs

Upvotes: 1

Related Questions