question

Magnus Ahlkvist avatar image
Magnus Ahlkvist 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?
sql-server-2008partitioningentity-framework
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Looks like a mapping issue (as with most ORM issue), could you provide the mapping as well?
0 Likes 0 ·
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.
0 Likes 0 ·
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.
0 Likes 0 ·
paulirwin avatar image
paulirwin answered
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!
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The "code" option in this editor is not allowing me to put in the generic type parameter to the Entity() method above because it looks like HTML, so you would need to do Entity*RecordType*() replacing the asterisks with angle brackets.
0 Likes 0 ·
bonskijr avatar image
bonskijr answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.