In entity framework I have an Entity 'Client' that was generated from a database. There is a property called 'Account' it is defined in the storage model as:
<Property Name="Account" Type="char" Nullable="false" MaxLength="6" />
And in the Conceptual Model as:
<Property Name="Account" Type="String" Nullable="false" />
When select statements are generated using a variable for Account i.e.
where m.Account == myAccount...
Entity Framework generates a paramaterized query with a paramater of type NVarchar(6). The problem is that the column in the table is data type of char(6). When this is executed there is a large performance hit because of the data type difference. Account is an index on the table and instead of using the index I believe an Index scan is done.
Anyone know how to force EF to not use Unicode for the paramater and use Varchar(6) instead?
I think I found the answer to my own question. Here http://blogs.msdn.com/b/adonet/archive/2010/05/10/improvements-to-generated-sql-in-net-4-0.aspx
It talks about improvments to generated sql in EF 4.0. Specifically it says
Provide mechanism for efficient queries on non-Unicode columns
In .NET 3.5, whenever a constant or a parameter was used in LINQ to Entities query, we treated it as being Unicode. As a result, when comparing a constant to a property stored in a non-unicode column on SQL Server, if there was an index on that column, it was not being used.
To address the issue, we now generate non-unicode constants and parameters when these are used in LINQ to Entities queries in comparisons with non-unicode columns.
I guess I need to upgrade.