Database Reference
In-Depth Information
As with all current development environments, .Net treats strings as unicode and generates
nvarchar
parameters
by default. Listing 16-14 demonstrates this behavior.
Listing 16-14.
String parameters
/* C# code */
string email = "test@outlook.com";
var q = context.Customers.Where(t => t.Email == email)
.Select(t => t.FirstName);
/* Generated SQL */
exec sp_executesql N'SELECT
[Extent1].[FirstName] AS [FirstName]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[Email] = @p__linq__0'
,N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'test@outlook.com'
As you know,
nvarchar
parameters lead to implicit data type conversions and are not SARGable for indexes on
the
varchar
column. SQL Server would not be able to utilize
Index Seek
operations on such indexes, which lead to
suboptimal execution plans.
There are two ways to address such a situation. First, you can use the
EntityFunctions.AsNonUnicode
function
as part of the call, as shown in Listing 16-15.
Listing 16-15.
Forcing Entity Framework to generate a varchar parameter
string email = "test@outlook.com";
var q = context.Customers
.Where(t => t.Email == EntityFunctions.AsNonUnicode(email))
.Select(t => t.FirstName);
■
Make sure to use the
EntityFunction
class defined in the
System.Data.Entity.Core.Objects
namespace.
there is a different class with the same name defined in the
System.Data.Objects
namespace.
Tip
Alternatively, you can let Entity Framework know about the non-unicode data type of the column by explicitly
specifying mapping using the Fluent API, as shown in Listing 16-16. This approach allows you to avoid calling the
AsNonUnicode
function in every method call.
Listing 16-16.
Setting up mapping in the model
/* Setting up mapping in configuration */
internal class CustomerMap : EntityTypeConfiguration<Customer>
{
public CustomerMap()
{
Property(t => t.Email).IsUnicode(false);
}
}