CODEWITHSUNDEEP
entity-frameworkef-code-first
Nicholas Head
Nicholas Head

Reputation: 3726

Why does code first/EF use 'nvarchar(4000)' for strings in the raw SQL command?

Essentially I have a table with zip codes in it. The zipcode field is defined as 'char(5)'. I'm using code first, so I've put these attributes on my ZipCode property:

[Key, Column( Order = 0, TypeName = "nchar"), StringLength(5)]
public string ZipCode { get; set; }

Now if I query against this in EF:

var zc = db.ZipCodes.FirstOrDefault(zip => zip.ZipCode == "12345");

The generated SQL uses nvarchar(4000) to inject the parameters. Huh? Is it because "12345" is technically a string of unknown length? Shouldn't EF be smart enough to just use the proper "nchar(5)" when querying that table?

I ask because the nvarchar(4000) query takes half a second whereas the properly-scoped query is much faster (and less reads).

Any assistance/advice would be appreciated.

Upvotes: 7

Views: 4653

Answers (2)

Robert Horvick
Robert Horvick

Reputation: 4036

This is to take advantage of auto parameterization. The following article explains the general concept as well as why specifically nvarchar(4000) is used.

http://msdn.microsoft.com/en-us/magazine/ee236412.aspx

Upvotes: 2

Bobby D
Bobby D

Reputation: 2159

Have you tried using the MaxLength attribute? This article gives a decent summary of how the various data annotation attributes are interpreted by EF.

Upvotes: 1

Related Questions