Comparison of Code and T-SQL Queries for LINQ to SQL and Entity Framework Databound Forms

The Drag-and-Drop Master/Details Windows Forms Still Missing from Entity Framework SP1 Beta post of August 4, 2008 describes the need for sample code to demonstrate that drag-and-drop databinding with promised post-Visual Studio 2008 SP1 Beta improvements is as effective as that of LINQ to SQL for common usage patterns, such as fully editable master/detail/subdetail forms.

Note: This post will be updated shortly with code to change Product EntityRef[erence]s. 

LINQ to SQL Master/Detail/Subdetail Form Example

LINQ to SQL query execution for Customers/Orders/Order_Details form with the last five Orders and their Order_Details items displayed for a selected customer:

private void MainForm_Load(object sender, EventArgs e)
{
    ctxNwind = new NorthwindDataContext();
    DataLoadOptions options = new DataLoadOptions();
    // Load only the last five orders, newest first
    options.AssociateWith<Customer>(c => c.Orders.
        OrderByDescending(o => o.OrderDate).Take(5));
    options.LoadWith<Order>(o => o.Order_Details);
    ctxNwind.LoadOptions = options;
    customerBindingSource.DataSource = ctxNwind.Customers;
    employeeBindingSource.DataSource = ctxNwind.Employees;
    shipperBindingSource.DataSource = ctxNwind.Shippers;
    productBindingSource.DataSource = ctxNwind.Products;
    cboCustomer.DataSource = ctxNwind.Customers;
    cboCustomer.DisplayMember = "CustomerID";
}

The following four T-SQL batches run on startup to populate ComboBox and ComboBoxColumn lists:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]

SELECT [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[ReportsTo], [t0].[PhotoPath]
FROM [dbo].[Employees] AS [t0]

SELECT [t0].[ShipperID], [t0].[CompanyName], [t0].[Phone]
FROM [dbo].[Shippers] AS [t0]

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]

The following executes on startup and the first time the user selects a customer other than the first:

exec sp_executesql N'SELECT TOP (5) [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[CustomerID] = ((
    SELECT [t2].[CustomerID]
    FROM (
        SELECT TOP (1) [t1].[CustomerID]
        FROM [dbo].[Customers] AS [t1]
        WHERE [t1].[CustomerID] = @p0
        ) AS [t2]
    ))
ORDER BY [t0].[OrderDate] DESC',N'@p0 nvarchar(5)',@p0=N'ALFKI'

The following query runs up to five times (depending on the number of orders for the customer) after the preceding query:

exec sp_executesql N'SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]
FROM [dbo].[Order Details] AS [t0]
WHERE [t0].[OrderID] = @x1',N'@x1 int',@x1=11087

and @x1=11011, 10952, 10835, 10702 for ALFKI.

Another Customers query runs at this point for unknown reasons.

Note 1: Changes to Salesperson (EmployeeID) and ShipVia (ShipperID) don’t affect the Order entity until changes are saved and data is refreshed.

Note 2: You can’t edit the ProductID value of an Order_Detail entity because it’s a member of the composite primary key. You must delete and recreate the entity.

Entity Framework v1 SP1 Beta Emulated Master/Details/Subdetails Form Example

Entity Framework query execution for Customers/Orders/Order_Details form with last five orders and details displayed for selected customer.

The following code loads the data sources for the ComboBox and ComboBoxColumns and calls event handlers to fill the Orders and Order_Details DataGridViews with the first customer’s last five orders:

private void MainForm_Load(object sender, EventArgs e)
{
    ctxNwind = new NorthwindEntities();
    customerBindingSource.DataSource = ctxNwind.Customers.ToList<Customer>();
    employeeBindingSource.DataSource = ctxNwind.Employees.ToList<Employee>();
    shipperBindingSource.DataSource = ctxNwind.Shippers.ToList<Shipper>();
    productBindingSource.DataSource = ctxNwind.Products.ToList<Product>();
    isLoaded = true;
    customerBindingSource.MoveFirst();
    customerBindingSource_CurrentChanged(null, null);
    // Reset AllowNew = true to add a pending new order
    orderBindingSource.AllowNew = true;
    cboCustomer.DataSource = ctxNwind.Customers;
    cboCustomer.DisplayMember = "CustomerID";
}

The orderBindingSource.AllowNew reset is required to enable adding a new order. Without resetting the property value, you can’t add a new order from the UI or with the orderBindingSource.New() method because the ObjectBindingSources.Items collection has become fixed-size. The instruction must be at this particular location in the code to prevent loss of the ComboBoxColumn’s selection.

The following two methods synchronize Orders and Order_Details with Customer and Order selections:

// Emulate behavior of OrdersBindingSource = CustomersBindingSource
// with Orders.DataMember = "Orders"
private void customerBindingSource_CurrentChanged(object sender, EventArgs e)
{
    if (isLoaded)
    {
        // Get the current Customer instance
        Customer cust = (Customer)customerBindingSource.Current;
        // Attach the Orders and Order_Details 
        cust.Orders.Attach(cust.Orders.CreateSourceQuery().
            OrderByDescending(o => o.OrderDate).Take(5));
        foreach (Order o in cust.Orders)
            o.Order_Details.Attach(o.Order_Details.CreateSourceQuery().
                Where(d => d.OrderID == o.OrderID));

        // Get the Customer’s associated Orders
        orderBindingSource.DataSource = 
            cust.Orders.Where(o => o.Customer.CustomerID == cust.CustomerID);
        orderBindingSource.MoveFirst();
        int row = 0;
        foreach (Order ord in orderBindingSource)
        {
            // Provide foreign-key values for SalesPerson and ShipVia ComboBoxes
            orderDataGridView.Rows[row].Cells[1].Value = ord.Employee.EmployeeID;
            orderDataGridView.Rows[row].Cells[5].Value = ord.Shipper.ShipperID;
            row++;
        }
        // Bind Order_Details to the first Order
        orderBindingSource_CurrentChanged(null, null);
    }
}

// Emulate behavior of Order_Details Binding Source = OrdersBindingSource
// with Order_Details.DataMember = "Order_Details" (called by highlighted line above)
private void orderBindingSource_CurrentChanged(object sender, EventArgs e)
{
    Order order = (Order)orderBindingSource.Current;
    if (order != null)
        order_DetailBindingSource.DataSource = order.Order_Details;
}

The following four T-SQL batches run on startup to populate ComboBox and ComboBoxColumn lists (similar to LINQ to SQL batches):

SELECT 
[Extent1].[CustomerID] AS [CustomerID], [Extent1].[CompanyName] AS [CompanyName], 
[Extent1].[ContactName] AS [ContactName], [Extent1].[ContactTitle] AS [ContactTitle], 
[Extent1].[Address] AS [Address], [Extent1].[City] AS [City], 
[Extent1].[Region] AS [Region], [Extent1].[PostalCode] AS [PostalCode], 
[Extent1].[Country] AS [Country], [Extent1].[Phone] AS [Phone], [Extent1].[Fax] AS [Fax]
FROM [dbo].[Customers] AS [Extent1]

SELECT 1 AS [C1], 
[Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[LastName] AS [LastName], 
[Extent1].[FirstName] AS [FirstName], [Extent1].[Title] AS [Title], 
[Extent1].[TitleOfCourtesy] AS [TitleOfCourtesy], [Extent1].[BirthDate] AS [BirthDate], 
[Extent1].[HireDate] AS [HireDate], [Extent1].[Address] AS [Address], 
[Extent1].[City] AS [City], [Extent1].[Region] AS [Region], 
[Extent1].[PostalCode] AS [PostalCode], [Extent1].[Country] AS [Country], 
[Extent1].[HomePhone] AS [HomePhone], [Extent1].[Extension] AS [Extension], 
[Extent1].[Photo] AS [Photo], [Extent1].[Notes] AS [Notes], 
[Extent1].[PhotoPath] AS [PhotoPath], [Extent1].[ReportsTo] AS [ReportsTo]
FROM [dbo].[Employees] AS [Extent1]

SELECT 
[Extent1].[ShipperID] AS [ShipperID], [Extent1].[CompanyName] AS [CompanyName], 
[Extent1].[Phone] AS [Phone]
FROM [dbo].[Shippers] AS [Extent1]

SELECT 
1 AS [C1], 
[Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], 
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[UnitPrice] AS [UnitPrice], 
[Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 
[Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[Discontinued] AS [Discontinued], 
[Extent1].[CategoryID] AS [CategoryID], [Extent1].[SupplierID] AS [SupplierID]
FROM [dbo].[Products] AS [Extent1]

The following executes on startup and the first time the user selects a customer other than the first:

exec sp_executesql N'SELECT TOP (5) 
[Project1].[C1] AS [C1], 
[Project1].[OrderID] AS [OrderID], [Project1].[OrderDate] AS [OrderDate], 
[Project1].[RequiredDate] AS [RequiredDate], [Project1].[ShippedDate] AS [ShippedDate], 
[Project1].[Freight] AS [Freight], [Project1].[ShipName] AS [ShipName], 
[Project1].[ShipAddress] AS [ShipAddress], [Project1].[ShipCity] AS [ShipCity], 
[Project1].[ShipRegion] AS [ShipRegion], 
[Project1].[ShipPostalCode] AS [ShipPostalCode], 
[Project1].[ShipCountry] AS [ShipCountry], [Project1].[CustomerID] AS [CustomerID], 
[Project1].[EmployeeID] AS [EmployeeID], [Project1].[ShipVia] AS [ShipVia]
FROM ( SELECT 
    [Extent1].[OrderID] AS [OrderID], [Extent1].[CustomerID] AS [CustomerID], 
    [Extent1].[EmployeeID] AS [EmployeeID], [Extent1].[OrderDate] AS [OrderDate], 
    [Extent1].[RequiredDate] AS [RequiredDate], 
    [Extent1].[ShippedDate] AS [ShippedDate], [Extent1].[ShipVia] AS [ShipVia], 
    [Extent1].[Freight] AS [Freight], [Extent1].[ShipName] AS [ShipName], 
    [Extent1].[ShipAddress] AS [ShipAddress], [Extent1].[ShipCity] AS [ShipCity], 
    [Extent1].[ShipRegion] AS [ShipRegion], 
    [Extent1].[ShipPostalCode] AS [ShipPostalCode], 
    [Extent1].[ShipCountry] AS [ShipCountry], 
    1 AS [C1]
    FROM [dbo].[Orders] AS [Extent1]
    WHERE ([Extent1].[CustomerID] IS NOT NULL) AND ([Extent1].[CustomerID] = @EntityKeyValue1))  AS [Project1]
ORDER BY [Project1].[OrderDate] DESC',N'@EntityKeyValue1 nchar(5)',@EntityKeyValue1=N'ALFKI'

Like LINQ to SQL, the following query runs up to five times (depending on the number of orders for the customer) after the preceding query:

exec sp_executesql N'SELECT 
1 AS [C1], 
[Extent1].[OrderID] AS [OrderID], [Extent1].[ProductID] AS [ProductID], 
[Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[Quantity] AS [Quantity], 
[Extent1].[Discount] AS [Discount]
FROM [dbo].[Order Details] AS [Extent1]
WHERE ([Extent1].[OrderID] = @EntityKeyValue1) AND ([Extent1].[OrderID] = @p__linq__1)',N'@EntityKeyValue1 int,@p__linq__1 int',@EntityKeyValue1=11087,@p__linq__1=11087

and @p__linq__1=11011, 10952, 10835, 10702 for ALFKI.

Note 1: Deleting and recreating Order entities to accommodate changes to Salesperson (EmployeeID) and ShipVia (ShipperID) aren’t acceptable because OrderID is an int identity field.

Technorati tags: Databinding, LINQ to SQL, LINQ, Entity Framework, VS 2008 SP1 Beta, Entity Framework v2, Object/Relational Modeling, O/RM