Matthieu MEZIL

EF: Why Include method is an anti-pattern IMHO? Conclusion

First, I admit that Anti-pattern word was excessive but I see so many catastrophic performances in my audits where developers use Include method.

I presented you a very « special » way to realize Include. If you use Include ONLY on the one side, performance difference is not huge and my code is really longer and more complex. But I consider that my solution would become very interesting if this code could be generated.

In a future post, I will share with you a T4 that do the job.

Note that if my DB was not local but far (SQL Azure for example), difference would be really better for my solution. Indeed, bytes received from server are really much important using Include method because you will probably have duplicated data if you have more than one row.

The main idea of these posts was not to give you a better way to write Include but to help you to realize what happens when you use Include method and what can we do if performance is something very important on your project.

Just note that, as I already mentioned, with my code, you will lose the IQueryable.

Note also that Include method is perfect for table splitting.

You also have to take in consideration that, because I don’t use DB lock, I can have incoherence with my way contrary to Include method. For example, if you first get Customers and the someone adds a new OnlineSale with a new Customer, and then you get the OnlineSales, you will get an OnlineSale without its Customer.

EF: Why Include method is an anti-pattern IMHO? 3/3

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.

EF: Why Include method is an anti-pattern IMHO even with many to one navigation properties? 2/3

In my yesterday post, I explained why I think Include method is an anti-pattern.

Darrel commented that the issue was because I use Include with one to many navigation property on many side.

In the sample, I have only one Include to side many and four to side (zero or) one.

But Darrel is right. If I use only one side, Include is not so bad. Not so bad but not the best one anyway.

I will take 2 samples to illustrate it.

In these samples, I will compare time to execute the query from the .NET code (so including SQL generation, SQL execution and EF materialization).

Using Include method my first sample is the following:

private static OnlineSale GetOnlineSaleWithGraph(ContosoRetailDWEntities context)

{

    return context.OnlineSales.Include(os => os.Customer).Include(os => os.Product.ProductSubcategory.ProductCategory).Include(os => os.Store).FirstOrDefault();

}

Now with my way, the code is the following:

private static OnlineSale GetOnlineSaleWithGraph(ContosoRetailDWEntities context)

{

    OnlineSale onlineSale = null;

    object lockObject = new object();

    Task onlineSaleTask = new Task(() =>

        {

            using (var context2 = new ContosoRetailDWEntities())

            {

                var query = context2.OnlineSales;

                ObjectQuery<OnlineSale> objectQuery = (ObjectQuery<OnlineSale>)query;

                objectQuery.MergeOption = MergeOption.NoTracking;

                onlineSale = objectQuery.FirstOrDefault();

            }

            if (onlineSale != null)

                lock (lockObject)

                {

                    context.OnlineSales.Attach(onlineSale);

                }

        });

    onlineSaleTask.Start();

    Task customerTask = new Task(() =>

        {

            Customer customer;

            using (var context2 = new ContosoRetailDWEntities())

            {

                var query = context2.OnlineSales.Select(os => os.Customer);

                ObjectQuery<Customer> objectQuery = (ObjectQuery<Customer>)query;

                objectQuery.MergeOption = MergeOption.NoTracking;

                customer = objectQuery.FirstOrDefault();

            }

            if (customer != null)

                lock (lockObject)

                {

                    context.Customers.Attach(customer);

                }

        });

    customerTask.Start();

    Task productTask = new Task(() =>

    {

        using (var context2 = new ContosoRetailDWEntities())

        {

            ObjectQuery<OnlineSale> objectQuery = context2.OnlineSales;

            objectQuery.MergeOption = MergeOption.NoTracking;

            var query = objectQuery.Select(os => new { os.Product, os.Product.ProductSubcategory, os.Product.ProductSubcategory.ProductCategory });

            var product = query.FirstOrDefault();

            if (product != null)

                lock (lockObject)

                {

                    context.Products.Attach(product.Product);

                    if (product.ProductSubcategory != null)

                    {

                        context.ProductSubcategories.Attach(product.ProductSubcategory);

                        if (product.ProductCategory != null)

                            context.ProductCategories.Attach(product.ProductCategory);

                    }

                }

        }

    });

    productTask.Start();

    Task storeTask = new Task(() =>

    {

        Store store;

        using (var context2 = new ContosoRetailDWEntities())

        {

            var query = context2.OnlineSales.Select(os => os.Store);

            ObjectQuery<Store> objectQuery = (ObjectQuery<Store>)query;

            objectQuery.MergeOption = MergeOption.NoTracking;

            store = objectQuery.FirstOrDefault();

        }

        if (store != null)

            lock (lockObject)

            {

                context.Stores.Attach(store);

            }

    });

    storeTask.Start();

    Task.WaitAll(onlineSaleTask, customerTask, productTask, storeTask);

    return onlineSale;

}

As you can see, I use a sort of Include to load ProductSubCategory and ProductCategory with the Product. I do it because as I get only one product, I’m sure that I won’t duplicate ProductSubCategory or ProductCategory.

In my tests, the code using Include run on 962 ms and 611 ms when the execution plan is on SQL Server cache and mine in 749 ms (22 % faster) and 548 ms when SQL Server uses its cache (10% faster).

Now I will do the same getting 100 OnlineSales

private static List<OnlineSale> GetTenLastOnlineSalesWithGraph(ContosoRetailDWEntities context)

{

    return context.OnlineSales.Take(100).Include(os => os.Customer).Include(os => os.Product.ProductSubcategory.ProductCategory).Include(os => os.Store).ToList();

}

Now my code is the following:

private static List<OnlineSale> GetTenLastOnlineSalesWithGraph(ContosoRetailDWEntities context)

{

    List<OnlineSale> onlineSales = null;

    object lockObject = new object();

    Task onlineSalesTask = new Task(() =>

        {

            using (var context2 = new ContosoRetailDWEntities())

            {

                var query = context2.OnlineSales.Take(100);

                ObjectQuery<OnlineSale> objectQuery = (ObjectQuery<OnlineSale>)query;

                objectQuery.MergeOption = MergeOption.NoTracking;

                onlineSales = objectQuery.ToList();

            }

            lock (lockObject)

            {

                foreach (var onlineSale in onlineSales)

                    context.OnlineSales.Attach(onlineSale);

            }

        });

    onlineSalesTask.Start();

    Task customersTask = new Task(() =>

        {

            List<Customer> customers;

            using (var context2 = new ContosoRetailDWEntities())

            {

                var query = context2.OnlineSales.Take(100).Select(os => os.Customer).Distinct();

                ObjectQuery<Customer> objectQuery = (ObjectQuery<Customer>)query;

                objectQuery.MergeOption = MergeOption.NoTracking;

                customers = objectQuery.ToList();

            }

            lock (lockObject)

            {

                foreach (var customer in customers)

                    context.Customers.Attach(customer);

            }

        });

    customersTask.Start();

    Task productsTask = new Task(() =>

    {

        List<Product> products;

        using (var context2 = new ContosoRetailDWEntities())

        {

            var query = context2.OnlineSales.Take(100).Select(os => os.Product).Distinct();

            ObjectQuery<Product> objectQuery = (ObjectQuery<Product>)query;

            objectQuery.MergeOption = MergeOption.NoTracking;

            products = objectQuery.ToList();

        }

        lock (lockObject)

        {

            foreach (var product in products)

                context.Products.Attach(product);

        }

    });

    productsTask.Start();

    Task productSubCategoriesTask = new Task(() =>

    {

        List<ProductSubcategory> productSubCategories;

        using (var context2 = new ContosoRetailDWEntities())

        {

            var query = context2.OnlineSales.Take(100).Select(os => os.Product.ProductSubcategory).Distinct();

            ObjectQuery<ProductSubcategory> objectQuery = (ObjectQuery<ProductSubcategory>)query;

            objectQuery.MergeOption = MergeOption.NoTracking;

            productSubCategories = objectQuery.ToList();

        }

        lock (lockObject)

        {

            foreach (var productSubCategory in productSubCategories)

                context.ProductSubcategories.Attach(productSubCategory);

        }

    });

    productSubCategoriesTask.Start();

    Task productCategoriesTask = new Task(() =>

    {

        List<ProductCategory> productCategories;

        using (var context2 = new ContosoRetailDWEntities())

        {

            var query = context2.OnlineSales.Take(100).Select(os => os.Product.ProductSubcategory.ProductCategory).Distinct().Distinct();

            ObjectQuery<ProductCategory> objectQuery = (ObjectQuery<ProductCategory>)query;

            objectQuery.MergeOption = MergeOption.NoTracking;

            productCategories = objectQuery.ToList();

        }

        lock (lockObject)

        {

            foreach (var productCategory in productCategories)

                context.ProductCategories.Attach(productCategory);

        }

    });

    productCategoriesTask.Start();

    Task storesTask = new Task(() =>

    {

        List<Store> stores;

        using (var context2 = new ContosoRetailDWEntities())

        {

            var query = context2.OnlineSales.Take(100).Select(os => os.Store).Distinct();

            ObjectQuery<Store> objectQuery = (ObjectQuery<Store>)query;

            objectQuery.MergeOption = MergeOption.NoTracking;

            stores = objectQuery.ToList();

        }

        lock (lockObject)

        {

            foreach (var store in stores)

                context.Stores.Attach(store);

        }

    });

    storesTask.Start();

    Task.WaitAll(onlineSalesTask, customersTask, productsTask, productSubCategoriesTask, productCategoriesTask, storesTask);

    return onlineSales;

}

In my tests, the code using Include run on 1118 ms and 657 ms when SQL Server uses its cache and mine in 941 ms (16% faster) and 587 ms (11% faster).

It’s interesting to note that, for performance aspect, in many to one navigation property, I prefer starting from my base query and use a Distinct but when I have a many to one navigation property, I prefer starting with my result EntitySet and use a Where with an Any on my base query.

context2.ProductCategories.Where(ca => context2.Customers.Take(50).SelectMany(c => c.OnlineSales).Any(os => os.Product.ProductSubcategory.ProductCategoryKey == ca.ProductCategoryKey));

So even like this you can see that Include method has not the best performance even if it is not catastrophic contrary to yesterday.

Note that my computer is “only” a dual core and the DB is local. Else, my way would be more better.

In fact, I think that Include is very good if you have a one to one property and if the Include has only one branch per graph depth.

Just note that the Include has something very interesting comparing to my code: Include method returns an IQueryable<T> after using it.

EF: Why Include method is an anti-pattern IMHO?

On different session I saw, eager loading with Include method is presented as a good pattern.

However, IMHO, it’s an anti-pattern.

That’s right, Include method like lazy loading is very easy to use. But performance with these is often very bad or even catastrophic.

One guy of the EF team told me last year, like a joke, that they should named Include IncludeButYouShouldNotUseIt and LazyLoadingEnabledButYouShouldNot.

For a developer, it’s a surprise because Include seems very good. Indeed we have only one query executed on the DB.

However, you have to understand that SQL is not C#.

If you use the Include method, you will get the full graph in only one SQL query.

But, in SQL, we don’t get some objects with relationships. You get a resultset with some datarows with some columns.

This is very important because it means that Include methods generated SQL gets flat data with many duplications.

Take a sample. I will use ContosoRetailDW DB with the following EDM:

Then I will use the following query:

var customers = context.Customers.Take(50).Include(c => c.OnlineSales.Select(os => os.Product.ProductSubcategory.ProductCategory)).Include(c => c.OnlineSales.Select(os => os.Store)).ToList();

EF generates the following SQL query:

SELECT

[Project1].[CustomerKey] AS [CustomerKey],

[Project1].[GeographyKey] AS [GeographyKey],

[Project1].[CustomerLabel] AS [CustomerLabel],

[Project1].[Title] AS [Title],

[Project1].[FirstName] AS [FirstName],

[Project1].[MiddleName] AS [MiddleName],

[Project1].[LastName] AS [LastName],

[Project1].[NameStyle] AS [NameStyle],

[Project1].[BirthDate] AS [BirthDate],

[Project1].[MaritalStatus] AS [MaritalStatus],

[Project1].[Suffix] AS [Suffix],

[Project1].[Gender] AS [Gender],

[Project1].[EmailAddress] AS [EmailAddress],

[Project1].[YearlyIncome] AS [YearlyIncome],

[Project1].[TotalChildren] AS [TotalChildren],

[Project1].[NumberChildrenAtHome] AS [NumberChildrenAtHome],

[Project1].[Education] AS [Education],

[Project1].[Occupation] AS [Occupation],

[Project1].[HouseOwnerFlag] AS [HouseOwnerFlag],

[Project1].[NumberCarsOwned] AS [NumberCarsOwned],

[Project1].[AddressLine1] AS [AddressLine1],

[Project1].[AddressLine2] AS [AddressLine2],

[Project1].[Phone] AS [Phone],

[Project1].[DateFirstPurchase] AS [DateFirstPurchase],

[Project1].[CustomerType] AS [CustomerType],

[Project1].[CompanyName] AS [CompanyName],

[Project1].[ETLLoadID] AS [ETLLoadID],

[Project1].[LoadDate] AS [LoadDate],

[Project1].[UpdateDate] AS [UpdateDate],

[Project1].[C1] AS [C1],

[Project1].[OnlineSalesKey] AS [OnlineSalesKey],

[Project1].[DateKey] AS [DateKey],

[Project1].[StoreKey] AS [StoreKey],

[Project1].[ProductKey] AS [ProductKey],

[Project1].[PromotionKey] AS [PromotionKey],

[Project1].[CurrencyKey] AS [CurrencyKey],

[Project1].[CustomerKey1] AS [CustomerKey1],

[Project1].[SalesOrderNumber] AS [SalesOrderNumber],

[Project1].[SalesOrderLineNumber] AS [SalesOrderLineNumber],

[Project1].[SalesQuantity] AS [SalesQuantity],

[Project1].[SalesAmount] AS [SalesAmount],

[Project1].[ReturnQuantity] AS [ReturnQuantity],

[Project1].[ReturnAmount] AS [ReturnAmount],

[Project1].[DiscountQuantity] AS [DiscountQuantity],

[Project1].[DiscountAmount] AS [DiscountAmount],

[Project1].[TotalCost] AS [TotalCost],

[Project1].[UnitCost] AS [UnitCost],

[Project1].[UnitPrice] AS [UnitPrice],

[Project1].[ETLLoadID1] AS [ETLLoadID1],

[Project1].[LoadDate1] AS [LoadDate1],

[Project1].[UpdateDate1] AS [UpdateDate1],

[Project1].[ProductKey1] AS [ProductKey1],

[Project1].[ProductLabel] AS [ProductLabel],

[Project1].[ProductName] AS [ProductName],

[Project1].[ProductDescription] AS [ProductDescription],

[Project1].[ProductSubcategoryKey] AS [ProductSubcategoryKey],

[Project1].[Manufacturer] AS [Manufacturer],

[Project1].[BrandName] AS [BrandName],

[Project1].[ClassID] AS [ClassID],

[Project1].[ClassName] AS [ClassName],

[Project1].[StyleID] AS [StyleID],

[Project1].[StyleName] AS [StyleName],

[Project1].[ColorID] AS [ColorID],

[Project1].[ColorName] AS [ColorName],

[Project1].[Size] AS [Size],

[Project1].[SizeRange] AS [SizeRange],

[Project1].[SizeUnitMeasureID] AS [SizeUnitMeasureID],

[Project1].[Weight] AS [Weight],

[Project1].[WeightUnitMeasureID] AS [WeightUnitMeasureID],

[Project1].[UnitOfMeasureID] AS [UnitOfMeasureID],

[Project1].[UnitOfMeasureName] AS [UnitOfMeasureName],

[Project1].[StockTypeID] AS [StockTypeID],

[Project1].[StockTypeName] AS [StockTypeName],

[Project1].[UnitCost1] AS [UnitCost1],

[Project1].[UnitPrice1] AS [UnitPrice1],

[Project1].[AvailableForSaleDate] AS [AvailableForSaleDate],

[Project1].[StopSaleDate] AS [StopSaleDate],

[Project1].[Status] AS [Status],

[Project1].[ImageURL] AS [ImageURL],

[Project1].[ProductURL] AS [ProductURL],

[Project1].[ETLLoadID2] AS [ETLLoadID2],

[Project1].[LoadDate2] AS [LoadDate2],

[Project1].[UpdateDate2] AS [UpdateDate2],

[Project1].[ProductSubcategoryKey1] AS [ProductSubcategoryKey1],

[Project1].[ProductSubcategoryLabel] AS [ProductSubcategoryLabel],

[Project1].[ProductSubcategoryName] AS [ProductSubcategoryName],

[Project1].[ProductSubcategoryDescription] AS [ProductSubcategoryDescription],

[Project1].[ProductCategoryKey] AS [ProductCategoryKey],

[Project1].[ETLLoadID3] AS [ETLLoadID3],

[Project1].[LoadDate3] AS [LoadDate3],

[Project1].[UpdateDate3] AS [UpdateDate3],

[Project1].[ProductCategoryKey1] AS [ProductCategoryKey1],

[Project1].[ProductCategoryLabel] AS [ProductCategoryLabel],

[Project1].[ProductCategoryName] AS [ProductCategoryName],

[Project1].[ProductCategoryDescription] AS [ProductCategoryDescription],

[Project1].[ETLLoadID4] AS [ETLLoadID4],

[Project1].[LoadDate4] AS [LoadDate4],