Entity Framework How to specify paramter type in generated SQL (SQLServer 2005) Nvarchar vs Varchar

Question

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?

Answer 1

the properties window for the field have 3 properties that should do what you are asking, Fixed Length, Max Length, and Unicode, they are in the Facets group in the properties window when you select the appropriate field in the designer.

I am currently looking at EF4 and am not sure if these exist in prior versions of EF.

it should end up looking like this in the XML.

<Property Name="Account" Type="String" MaxLength="6" Unicode="false" FixedLength="true" Nullable="false" />

Answer 2

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.