A CAST to int is introduced in the generated query for the tinyint column.

c# entity-framework sql-server

Question

I am querying a tinyint column and entity-framework generates a SELECT query that introduces a CAST to INT for this column even when the value that I am using in the WHERE clause is of the type byte.

Looking the Model, the generated Type for my tinyint column is byte.

Looking the code:

byte byteValue = 6;
var entityList = from r in rep.DataContext.FooTable
                 where r.TinyintColumn == byteValue
                 select r;

Looking the generated query:

SELECT [Extent1].[TinyintColumn] AS [TinyintColumn] WHERE @p__linq__0 = CAST( [Extent1].[TinyintColumn] AS int)

I have strict restrictions in the performance so I don't want those CAST in any select.

So my question whould be, is there any way to avoid this CAST over the column tinyint? or am I doing something wrong?

Thanks in advance.

1
15
1/26/2012 9:47:43 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

If you use IList<T>.Contains with a List<byte> the Entity Framework won't cast.

List<byte> byteValue = new List<byte> { 6 };
var entityList = from r in rep.DataContext.FooTable
             where byteValue.Contains(r.TinyintColumn)
             select r;

I ran into the same problem and blogged about it.

11
12/1/2015 6:43:06 AM

Popular Answer

My colleague found very nice trick to overcome this issue on Entity Framework 4.0.
Works for smallint, I didn't try on tinyint.

Insteal of equals (==) - use Contains() operator which was implemented with EF 4.0.

For example:
say you have the column SmallIntColumn.

instead of:

short shortValue = 6;
var entityList = from r in rep.DataContext.FooTable
                 where r.SmallIntColumn == shortValue
                 select r;

use

short[] shortValue = new short[] { 6 };
var entityList = from r in rep.DataContext.FooTable
                 where shortValue.Contains(r.SmallIntColumn)
                 select r;

Check the SQL generated - it is now without the CAST!
And from my tests - the execution plan used my (filtered) index on the column just perfectly.

Hope it helped.
Shlomi

shlomiw

Related Questions

Select specified columns and return strongly typed without loosing cast using Entity Framework.

You are probably on the wrong way. At first I would assume that ...Action... should be an abstract class and you should not be able to create instances of it at all. If you then only fetch a subset of the properties and the subset does no longer allow to discriminate between events and activities, it is probably the wrong way to try making events a...
asp.net-mvc entity-framework inheritance linq linq-to-sql
asked by Daniel Brückner

Casting to a derived type in a LINQ to Entities query with Table Per Hierarchy inheritance

You can use EntityFramework.Extended to improve the performance of the query instead of doing 2 round trips to DB....var resultA = base.OfType<DerivedType1>().Select(d => d.DerivedProperty).Future(); var resultB = base.OfType<DerivedType2>().Select(d => default(int?)).Future(); var result = resultA.Concat(resultB).ToList(); ...In this case only one...
.net c# entity-framework linq linq-to-entities
asked by Miguel

Convert string to int in an Entity Framework linq query and handling the parsing exception

First way:...var numbers = Purchases.Select(x => x.Number).ToList(); int temp; int max = numbers.Select(n => int.TryParse(n, out temp) ? temp : 0).Max(); Console.WriteLine("Max: {0}", max); ...Second way:...int temp2; int max2 = Purchases.Select(x => x.Number).ToList().Select(n => int.TryParse(n, out temp2) ? temp2 : 0).Max(); Console.WriteLine(...
c# entity-framework linq
asked by Gromer

C# - when to use public int virtual, and when to just use public int

Could anybody explain to me:...The purpose of the term 'virtual' in this particular context...Other users here answered this well with very good references....Is using 'virtual' necessary?...It depends. Sometimes it is necessary, sometimes it is superfluous....Why do some people use 'virtual' and others do not?...They use it when they need it, or w...
asp.net-mvc asp.net-mvc-3 c# entity-framework
asked by danludwig

Cast() exception: LINQ to Entities only supports casting EDM primitive or enumeration types

Enumerable.Cast<TResult>... is trying to cast every element in the collection. And it fails in your scenario (no surprise) because you are trying to cast instances of ...parent... (...AIM.Police.DB.CriminalEvent...) class to objects of ...child... (...InvestigationSimulator.Models.CriminalEvent...) class. You cannot do such casting in C#....In orde...
asp.net-mvc c# casting entity-framework linq-to-entities
asked by SergeyS

Entity Framework - ridiculous Query, casting smallint to int for comparison

This behavior can be common for different LINQ providers and not only EF-specific, because of how C# compiler generates expression tree for your Where expression....When you specify condition as:....Where(x => x.BucketRef == bucketId) ...and both BucketRef and bucketId are shorts, compiler generates cast from short to int for both parts of comparis...
entity-framework entity-framework-6
asked by Ilya

How do I map a C# int to a SqlServer tinyint using Entity Framework Code First?

Short answer : You can't....The mappings "line up" like below....The property on the POCO should be "byte".... public byte CountryId{ get; set; } ...and the Mapping:... this.Property(t => t.CountryId).HasColumnName("CtryId"); ...You gotta play by the rules of EF....However, the good news is that you can make it work with a little magic...
c# entity-framework
asked by granadaCoder

Entity Framework 6 Code First int Identity column value is zero for first row

Your concrete migration class should look "something" like the following:...public class 201707132034165_MyAwesomeDbInitial : DbMigration { #region <Methods> public override void Up() { CreateTable( "dbo.HasOverdrive", c => new { HasOverdriveId = c.Int(nullable: false, identit...
c# ef-code-first entity-framework-6 sql-server
asked by Prisoner ZERO

Wrong select query generated with Entity Framework (mysterious extra column)

Try searching all code files for ...PerformerFolder... without the 'id' EF adds that to navigation properties on it's own....as @Giorgi mentioned, it must be a partial class somewhere, or maybe a left over from a previous build ?
c# entity-framework-6 linq
asked by Marty

convertion of ObjectResult<Nullable<int>> type to int

You have to loop over the ...ObjectResult<T>... or use ...ObjectResult<T>.ElementAt()... to retrieve individual elements. Then you can access the ...int... values:...ObjectResult<Nullable<int>> queryResult = query.Execute(...); foreach (Nullable<int> result in queryResult) Console.WriteLine("{0}", result.Value); ...You can check if a value is a...
asp.net-mvc-5 c# entity-framework-6
asked by Adrian

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow