Database Reference
In-Depth Information
/* Adding configuration to context class by overriding the method */
public class EFDbContext : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Configurations.Add(new CustomerMap());
}
}
For queries that select data, Entity Framework generates string parameters as
nvarchar(4000)
or
varchar(8000)
when the length of string does not exceed 4000 or 8000 bytes respectively. For larger strings, Entity Framework
generates
nvarchar(max)
or
varchar(max)
parameters. For queries that modify data, the length of the parameters
matches the properties' length defined in the model.
IN Lists
One of the areas where Entity Framework does not perform well is working with lists of rows. Let's try to load multiple
customers based on the list of their
CustomerId
values. Listing 16-17 shows the client code and generated SQL for doing this.
Listing 16-17.
Loading a list of rows
/*C# Code*/
var list = new List<int>();
for (int i = 1; i < 100; i++)
list.Add(i);
using (var context = new EFDbContext())
{
var q = context.Customers.Where(t => list.Contains(t.CustomerId))
.Select(t => new {t.CustomerID, t.FirstName, t.LastName});
var result = q.ToList();
}
/* Generated SQL */
SELECT
[Extent1].[CustomerId] AS [CustomerId],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[CustomerId] IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26,
27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42,
43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58,
59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74,
75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90,
91, 92, 93, 94, 95, 96, 97, 98, 99)'
There are two problems with this code. First, SQL Server does not parameterize the statement, which, as we
already discussed, adds the load to SQL Server. Moreover, SQL Server does not work efficiently with large IN lists.
Those statements take a longer time to compile, execution plans consume large amounts of memory, and they are
not always efficient.
