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);
}
}
 
 
Search WWH ::




Custom Search