asked
Entity Framework and partition elimination
Hi! I have a query, which is created from Entity Framework. It is running against a partitioned table (partitioned on column dt), and it looks a little something like this (somewhat obfuscated): exec sp_executesql N'SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM dbo.Table1 AS [Extent1] WHERE ( NOT (([Extent1].[Col1] IS NULL) OR ((LEN([Extent1].[Col1])) = 0))) AND (''0'' [Extent1].[Col1]) AND ([Extent1].[dt] = @p__linq__0) AND ((UPPER([Extent1].[CC])) = (UPPER(@p__linq__1))) AND (( CAST(CHARINDEX(LOWER(@p__linq__2), LOWER([Extent1].[ES])) AS int)) = 1) ) AS [GroupBy1]',N'@p__linq__0 datetime2(7),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000)',@p__linq__0='2013-09-30 00:00:00',@p__linq__1=N'no',@p__linq__2=N'S1' This query takes a long time to execute (longer than expected). Looking at the query plan reveals that it is scanning all partitions for an index on column dt. The dt column has datatype **date**, but Entity Framework is using **datetime2(7)**, which is causing an implicit conversion. The predicate in the plan doesn't mention column dt at all (though it is in the Seek predicates, where all 30 partitions of the table are specified to scan). When I paste the query into Management Studio, and change **datetime2(7)** to **date**, I get a query plan which makes the expected partition elimination, and only scans the partition I expect it to scan. Has anyone here seen anything like this before? Is there a way to force Entity Framework to parametrize with a **date**-parameter rather than a **datetime2(7)**-parameter?
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer. Can't believe I'm posting this for you.
Hi Grant, The voting part is already done :) We're still having a developer work on this issue, so I'm not sure which (or any) of the answers provides the solution, though I **can** say both answers have been helpful.
Unfortunately it doesn't seem like it's possible to make the LINQ parameters `date` instead of `datetime2`. See here:
http://stackoverflow.com/a/16700661 You can set in the mapping that it should be `date` instead, but that only applies to things like migrations, it doesn't apply to LINQ queries. To do that if using EF Code First, add the `[Column(TypeName = "date")]` attribute to your property, or in OnModelCreating, add `modelBuilder.Entity().Property(i => i.DateValue).HasColumnType("date");` But again, that won't help with the LINQ queries. Your best bet at this point is probably going to be to use a stored procedure. Sorry!
You could try using the SqlQuery method of the DBContext and explicitly use the correct type. string query = "SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM dbo.Table1 AS [Extent1] WHERE ( NOT (([Extent1].[Col1] IS NULL) OR ((LEN([Extent1].[Col1])) = 0))) AND (''0'' <> [Extent1].[Col1]) AND ([Extent1].[dt] = @p__linq__0) AND ((UPPER([Extent1].[CC])) = (UPPER(@p__linq__1))) AND (( CAST(CHARINDEX(LOWER(@p__linq__2), LOWER([Extent1].[ES])) AS int)) = 1) ) AS [GroupBy1]"; var param1 = new SqlParameter("@p__linq__0", System.Data.SqlDbType.Date); var param2 = new SqlParameter("@p__linq__1", System.Data.SqlDbType.Varchar, 100); var param3 = new SqlParameter("@p__linq__2", System.Data.SqlDbType.Varchar, 100); param1.Value = new DateTime(2013, 09, 30); param2.Value = "no"; param3.Value = "S1"; var res = ctx.YourEntity.SqlQuery(query, param1, param2, param3).ToList(); Sucks to not able to specify in the model itself as linq looks to ignore those, at least with the datetime. HTH
16 People are following this question.
