I will continue on my sample with Contoso.
I won’t use a Repository and I will directly querying my DB on my ViewModel. The main reason is because it is easier for demo and because the main topic is the Include method.
I will use Include method with the best case for it: navigation property to one side.
Imagine that you want to use a WPF DataGrid read only based on OnlineSales where you want to see the OnlineSale.SalesQuantity, OnlineSale.SalesAmount, Customer.LastName, Customer.FirstName, Store.StoreName, Product.ProductName, ProductSubcategory.ProductSubcategoryName and ProductCategory.ProductCategoryName.
This is typically a case where most of developers (and sessions I saw) use the Include method.
public class OnlineOrdersViewModel : INotifyPropertyChanged, IDisposable
{
private ContosoRetailDWEntities _context = new ContosoRetailDWEntities();
private ObservableCollection<OnlineSale> _onlineSales;
public ObservableCollection<OnlineSale> OnlineSales
{
get
{
if (_onlineSales == null)
{
Stopwatch sw = new Stopwatch();
sw.Start();
_onlineSales = new ObservableCollection<OnlineSale>(_context.OnlineSales.Take(100).Include(os => os.Customer).Include(os => os.Product.ProductSubcategory.ProductCategory).Include(os => os.Store));
sw.Stop();
ElapsedMilliseconds = sw.ElapsedMilliseconds;
}
return _onlineSales;
}
}
private long _elapsedMilliseconds;
public long ElapsedMilliseconds
{
get { return _elapsedMilliseconds; }
set
{
_elapsedMilliseconds = value;
if (PropertyChanged != null)
PropertyChanged(this, new PropertyChangedEventArgs("ElapsedMilliseconds"));
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (_context != null)
{
_context.Dispose();
_context = null;
}
}
}
public event PropertyChangedEventHandler PropertyChanged;
}
The OnlineSales loading needs 1135ms.
IMHO, this is an anti-pattern. Why loading complete entities only for one or two properties?
Here you can use a OnlineSaleDTO that has only these properties. It would be the easier.
You can also use your entities. But in this case, how to do it?
There is one restriction with L2E: you can’t use a new on en entity (I hope it will be fix in the future).
So I use this code:
_onlineSales = new ObservableCollection<OnlineSale>(
_context.OnlineSales.Take(100).
Select(os =>
new
{
os.OnlineSalesKey,
os.SalesQuantity,
os.SalesAmount,
os.Customer.LastName,
os.Customer.FirstName,
os.Product.ProductName,
os.Product.ProductSubcategory.ProductSubcategoryName,
os.Product.ProductSubcategory.ProductCategory.ProductCategoryName,
os.Store.StoreName
}).
AsEnumerable().
Select(os =>
new OnlineSale
{
OnlineSalesKey = os.OnlineSalesKey,
SalesQuantity = os.SalesQuantity,
SalesAmount = os.SalesAmount,
Customer = new Customer
{
LastName = os.LastName,
FirstName = os.FirstName,
},
Product = new Product
{
ProductName = os.ProductName,
ProductSubcategory = (os.ProductSubcategoryName == null ? null : new ProductSubcategory
{
ProductSubcategoryName = os.ProductSubcategoryName,
ProductCategory = (os.ProductCategoryName == null ? null : new ProductCategory
{
ProductCategoryName = os.ProductCategoryName
})
})
},
Store = new Store
{
StoreName = os.StoreName
}
}));
Note that because ProductSubcategory.ProductSubcategoryName and ProductCategory.ProductCategoryName properties are not nullable, I can used it in order to know if ProductSubCategory or ProductCategory ar null.
Mine needs only 518 ms so more than twice fast!
Note that if you compare performance with my previous post, you can see that these performances are very bad comparing to what they were. In fact it’s because in my previous post, query execution plans were on SQL Server cache. Here it was not the case. In this case the comparison is really on favor of my way.
When execution plans are in SQL Server cache, Include method needs 507ms and mine 350. So the ratio is less important in this case.
Now what happens if the grid is not read only. In 90 % of cases, you won’t modify the related entities but only the OnlineSale entity.
So we will use a TextBox for SalesQuantity and SalesAmount, a ComboBox for Customer, Product and Store.
<DataGrid ItemsSource="{Binding OnlineSales}"
AutoGenerateColumns="False">
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding SalesQuantity}" />
<DataGridTextColumn Binding="{Binding SalesAmount}" />
<DataGridTemplateColumn>
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<ComboBox ItemsSource="{Binding Path=DataContext.Customers, RelativeSource={RelativeSource AncestorType={x:Type Window}}}"
SelectedItem="{Binding Customer}">
<ComboBox.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Horizontal">
<TextBlock Text="{Binding LastName}"
Margin="0,0,5,0" />
<TextBlock Text="{Binding FirstName}" />
</StackPanel>
</DataTemplate>
</ComboBox.ItemTemplate>
</ComboBox>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
<DataGridTemplateColumn>
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<ComboBox ItemsSource="{Binding Path=DataContext.Stores, RelativeSource={RelativeSource AncestorType={x:Type Window}}}"
DisplayMemberPath="StoreName"
SelectedItem="{Binding Store}" />
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
<DataGridTemplateColumn>
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<ComboBox ItemsSource="{Binding Path=DataContext.Products, RelativeSource={RelativeSource AncestorType={x:Type Window}}}"
SelectedItem="{Binding Product}">
<ComboBox.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Horizontal">
<TextBlock Text="{Binding ProductSubcategory.ProductCategory.ProductCategoryName}"
Margin="0,0,5,0" />
<TextBlock Text="/"
Margin="0,0,5,0" />
<TextBlock Text="{Binding ProductSubcategory.ProductSubcategoryName}"
Margin="0,0,5,0" />
<TextBlock Text="/"
Margin="0,0,5,0" />
<TextBlock Text="{Binding ProductName}" />
</StackPanel>
</DataTemplate>
</ComboBox.ItemTemplate>
</ComboBox>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
</DataGrid.Columns>
</DataGrid>
On my audits I often see this code for ViewModel:
public class OnlineOrdersViewModel : INotifyPropertyChanged, IDisposable
{
private ContosoRetailDWEntities _context = new ContosoRetailDWEntities();
public OnlineOrdersViewModel()
{
Stopwatch sw = new Stopwatch();
sw.Start();
// In Real life don't do it in the constructor but in another thread in order to not freeze UI
_onlineSales = new ObservableCollection<OnlineSale>(_context.OnlineSales.Take(100).Include(os => os.Customer).Include(os => os.Product.ProductSubcategory.ProductCategory).Include(os => os.Store));
_customers = new ObservableCollection<Customer>(_context.Customers);
_stores = new ObservableCollection<Store>(_context.Stores);
_products = new ObservableCollection<Product>(_context.Products.Include(p => p.ProductSubcategory.ProductCategory));
sw.Stop();
ElapsedMilliseconds = sw.ElapsedMilliseconds;
}
private ObservableCollection<OnlineSale> _onlineSales;
public ObservableCollection<OnlineSale> OnlineSales
{
get { return _onlineSales ?? (_onlineSales = new ObservableCollection<OnlineSale>()); }
}
private ObservableCollection<Customer> _customers;
public ObservableCollection<Customer> Customers
{
get { return _customers ?? (_customers = new ObservableCollection<Customer>()); }
}
private ObservableCollection<Product> _products;
public ObservableCollection<Product> Products
{
get { return _products ?? (_products = new ObservableCollection<Product>()); }
}
private ObservableCollection<Store> _stores;
public ObservableCollection<Store> Stores
{
get { return _stores ?? (_stores = new ObservableCollection<Store>()); }
}
private long _elapsedMilliseconds;
public long ElapsedMilliseconds
{
get { return _elapsedMilliseconds; }
set
{
_elapsedMilliseconds = value;
if (PropertyChanged != null)
PropertyChanged(this, new PropertyChangedEventArgs("ElapsedMilliseconds"));
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (_context != null)
{
_context.Dispose();
_context = null;
}
}
}
public event PropertyChangedEventHandler PropertyChanged;
}
This is useless to use Include method when you load OnlineSales because, with foreign keys, EF ObjectContext will attach navigation property alone because we load all related elements (when we load customers, stores, products with ProductSubcategory with ProductCategory).
This first sample needs 2338 ms for loading and 1244 when SQL Server uses its cache.
Now if we remove the useless Include:
_onlineSales = new ObservableCollection<OnlineSale>(_context.OnlineSales.Take(100));
_customers = new ObservableCollection<Customer>(_context.Customers);
_stores = new ObservableCollection<Store>(_context.Stores);
_products = new ObservableCollection<Product>(_context.Products.Include(p => p.ProductSubcategory.ProductCategory));
With it, loading needs 1685 ms and 1152 ms when SQL Server uses its cache.
Now I will try to do it with my way loading needs 1459 ms and 1007 ms when SQL Server uses its cache.
object lockObject = new object();
Task onlineSalesTask = new Task(() =>
{
using (var context2 = new ContosoRetailDWEntities())
{
_onlineSales = new ObservableCollection<OnlineSale>(
context2.OnlineSales.Take(100).
Select(os =>
new
{
os.OnlineSalesKey,
os.SalesQuantity,
os.SalesAmount,
os.CustomerKey,
os.ProductKey,
os.StoreKey
}).
AsEnumerable().
Select(os =>
new OnlineSale
{
OnlineSalesKey = os.OnlineSalesKey,
SalesQuantity = os.SalesQuantity,
SalesAmount = os.SalesAmount,
CustomerKey = os.CustomerKey,
ProductKey = os.ProductKey,
StoreKey = os.StoreKey,
}));
}
lock (lockObject)
{
foreach (var os in _onlineSales)
_context.OnlineSales.Attach(os);
}
});
onlineSalesTask.Start();
Task customersTask = new Task(() =>
{
using (var context2 = new ContosoRetailDWEntities())
{
_customers = new ObservableCollection<Customer>(
_context.Customers.
Select(c => new
{
c.CustomerKey,
c.LastName,
c.FirstName
}).
AsEnumerable().
Select(c =>
new Customer
{
CustomerKey = c.CustomerKey,
LastName = c.LastName,
FirstName = c.FirstName
}));
}
lock(lockObject)
{
foreach (var c in _customers)
_context.Customers.Attach(c);
}
});
customersTask.Start();
Task productsTask = new Task(() =>
{
_products = new ObservableCollection<Product>(
_context.Products.
Select(p => new
{
p.ProductKey,
p.ProductName,
p.ProductSubcategoryKey
}).
AsEnumerable().
Select(p =>
new Product
{
ProductKey = p.ProductKey,
ProductName = p.ProductName,
ProductSubcategoryKey = p.ProductSubcategoryKey
}));
lock(lockObject)
{
foreach (var p in _products)
_context.Products.Attach(p);
}
});
productsTask.Start();
Task productSubCategoriesTask = new Task(() =>
{
List<ProductSubcategory> productSubCategories;
using (var context2 = new ContosoRetailDWEntities())
{
productSubCategories = _context.ProductSubcategories.
Select(psc => new
{
psc.ProductSubcategoryKey,
psc.ProductSubcategoryName,
psc.ProductCategoryKey
}).
AsEnumerable().
Select(psc =>
new ProductSubcategory
{
ProductSubcategoryKey = psc.ProductSubcategoryKey,
ProductSubcategoryName = psc.ProductSubcategoryName,
ProductCategoryKey = psc.ProductCategoryKey
}).
ToList();
}
foreach (var psc in productSubCategories)
lock (lockObject)
{
_context.ProductSubcategories.Attach(psc);
}
});
productSubCategoriesTask.Start();
Task productCategoriesTask = new Task(() =>
{
List<ProductCategory> productCategories;
using (var context2 = new ContosoRetailDWEntities())
{
productCategories = _context.ProductCategories.
Select(psc => new
{
psc.ProductCategoryKey,
psc.ProductCategoryName
}).
AsEnumerable().
Select(psc =>
new ProductCategory
{
ProductCategoryKey = psc.ProductCategoryKey,
ProductCategoryName = psc.ProductCategoryName
}).
ToList();
}
lock (lockObject)
{
foreach (var psc in productCategories)
_context.ProductCategories.Attach(psc);
}
});
productCategoriesTask.Start();
Task storesTask = new Task(() =>
{
using (var context2 = new ContosoRetailDWEntities())
{
_stores = new ObservableCollection<Store>(
_context.Stores.
Select(s => new
{
s.StoreKey,
s.StoreName
}).
AsEnumerable().
Select(s =>
new Store
{
StoreKey = s.StoreKey,
StoreName = s.StoreName
}));
}
lock (lockObject)
{
foreach (var s in _stores)
_context.Stores.Attach(s);
}
});
storesTask.Start();
Task.WaitAll(onlineSalesTask, customersTask, productsTask, productSubCategoriesTask, productCategoriesTask, storesTask);
You have to take in consideration that ratio between my way and Include method one would be more important if the DB was not local.
Using Include, we have 7755210 bytes received from server. With my way, we only have 853693 bytes.