MaxLength and how it affects queries

Question

I am using Entity Framework 4.3.1, code first and am looking in SQL Profiler at the queries that are being produced. Here is one for example:

As you can see - it is specifying the arguments as nvarchar(max) but in my configuration I have specified a MaxLength value. I would expect, therefore, that the parameter would be passed as nvarchar(n) Since it does not do this, we are finding the queries are running slowly and this simple change would speed them up.

Does anyone know how we can convince the Entity Framework to generate the correct nvarchar lengths?

Here is my entity and configuration code:

Entity

public class StorageTransportOrderItem 
{
    public string StorageTransportOrderItemNumber { get; set; }
    public string StorageNumber { get; set; }
    [...]
}

Configuration

ToTable("StorageTransportOrderItem");

Property(p => p.StorageTransportOrderNumber)
    .HasMaxLength(10);
Property(d => d.StorageTransportOrderItemNumber)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
    .HasMaxLength(4);

HasKey(d => new
{
    d.StorageTransportOrderItemNumber,
    d.StorageNumber,
    d.StorageTransportOrderNumber
 });

Example Query

FROM [dbo].[StorageTransportOrder] AS [Extent1]
WHERE ([Extent1].[StorageNumber] = @p__linq__0) AND ([Extent1]
[StorageTransportOrderNumber] = @p__linq__1)
)  AS [Limit1]',N'@p__linq__0 nvarchar(max) ,@p__linq__1 nvarchar(max)  
',@p__linq__0=N'200',@p__linq__1=N'0001374850'

Notice the nvarchar(max), this is what makes it slow.

Answer 1

Tl:dr; I don't think HasMaxLength(), or in-fact anything you have control over when using Entity Framework, can change this so you probably should look at the queries.

What I Think

I am a bit confused as to why you are getting nvarchar(max) on your query - I would expect nvarchar(4000) as that is what I am getting on my test system. And this question asks about that: Why does code first/EF use 'nvarchar(4000)' for strings in the raw SQL command? (pretty interesting reading, that one). Maybe something to do with ADO .Net versions, I am not sure.

Anyhoo, having said that I am going to suggest that you will not be able to influence this value (at least, I am sure you can convince it to be nvarchar(4000) instead of nvarchar(max) of course but not anything else).

I think that Entity Framework is using the autoparameterization feature in ADO .NET when generating these queries (see: How Data Access Code Affects Database Performance). Essentially, I am saying that I suspect that they are simply adding the string value to an ADO .NET DbParameter and being done with it.

This is a real shame because, as you have specified the MaxLength it does know how long that field is but I can also imagine how complicated it would be to tally up the parameter in your Lambda expression with the column it relates to, so I am not totally surprised.

So What Can You Do?

Given you cannot change this, and although it does affect performance, as you suggest, I am going to suggest you concentrate on reducing the number of just such queries that run. Usually the best performance improvement is made by adjusting the structure of your query and the count rather than little things like this (just my experience, don't take it as gospel).

For example, if you are doing something like a SingleOrDefault(i => i.id ==x) inside a loop you will probably want to convert that into a single Where(i => xs.Contains(i.id)) outside the loop. That kind of thing. I can't really go into specifics but I am sure you will be able to use some other approach to get to adequate performance.

Good Luck!