0

I'm using VS2008, .NET 3,5, Entity Framework 3.5, Sql Server Compact 3.5 SP2. SQL SC 3.5 is accessed using EF, with Database first approach.

I'm using a simple query (linq and EF) that should use an existing compound index for columns StanjeId (tinyint) and ArtiklId (int).

var compQuery2 = from art in MobileDb.Artikl
                             where art.Stanje.StanjeId == (byte)1
                             && art.ArtiklId == tmp1
                             select art;

var quer1 = MobileDb.Artikl.Where(a => a.Stanje.StanjeId == (byte)1 && a.ArtiklId == tmp1);

The generated query using (compQuery2 as System.Data.Objects.ObjectQuery).ToTraceString() is:

SELECT 
1 AS [C1], 
[Extent1].[ArtiklGuid] AS [ArtiklGuid], 
.
.
[Extent1].[StanjeId] AS [StanjeId], 
[Extent1].[ZemljaPorijeklaDrzavaGuid] AS [ZemljaPorijeklaDrzavaGuid]
FROM [Artikl] AS [Extent1]
WHERE (1 = ( CAST( [Extent1].[StanjeId] AS int))) AND ([Extent1].[ArtiklId] = @p__linq__4)

The problem is that the generated query uses cast to int in the where part of the query for column StanjeId although StanjeId is of type tinyint (byte equivalent). That causes the SQL SC 3.5 not to use index seek, but a very slow table scan (table has >1M records).

How to get EF 3.5 to not use CAST as int in the where part the generated SQL query?

Core Zero
  • 3
  • 2
  • 1
    what is you used a `byte` variable assigned to 1 , `byte one=1` instead of `(byte)1` ? – Rameez Ahmed Sayad Sep 27 '13 at 07:12
  • WHERE part of the generated query is now: WHERE (( CAST( [Extent1].[StanjeId] AS int)) = ( CAST( @p__linq__3 AS int))) AND ([Extent1].[ArtiklId] = @p__linq__4) So is generally the same but now both StanjeId column and variable one are cast separately to int. – Core Zero Sep 27 '13 at 07:20
  • try this operator `Byte.CompareTo(Byte)` , `art.Stanje.StanjeId.CompareTo(one) == 0` , even if this explicitly casts , we will have to start looking into source code for diff methods/ – Rameez Ahmed Sayad Sep 27 '13 at 07:32
  • Thanks Rameez :) Your solution works great and removes the CAST from the generated SQL query. Why does art.Stanje.StanjeId == (byte)1 generate different code than art.Stanje.StanjeId.CompareTo(one) == 0 although logically it should be same? – Core Zero Sep 27 '13 at 08:22
  • Posted the answer below as it might help others , logically many things look same in C# but when we look in the source code or ILDASM , we realize the difference. But your question is much more important than my answer :) Thanks – Rameez Ahmed Sayad Sep 27 '13 at 08:41

1 Answers1

0

try this operator
Byte.CompareTo(Byte) , art.Stanje.StanjeId.CompareTo(one) == 0 ,
even if this explicitly casts , we will have to start looking into source code for diff methods.

Rameez Ahmed Sayad
  • 1,300
  • 6
  • 16
  • 29