Tutorial 7: Querying with Linq To SQL

Description Article
Table of Contents
Summary

Querying a database using Linq follows the same rules as querying in-memory objects (Linq To Objects): the only difference is that the database context object must be instantiated.

Description

By default Linq To SQl uses deffered or lazy loading. Nevertheless it is possible to configure what data should be retrieved at the same time by setting data load options.

Operations
Specify which data related to the parent entity should be retrieved at the same time

In the sample code the orders for the specific customer will be retrieved in a single database access through a LEFT JOIN statement.

Whitch data should be retrieved is configured using DataLoadOptions:LoadWith(lambda) method

Remarks

If no data load options are specified then multiple SQL statements will be generated: first to retrieve the customer data and then to retrieve the customer’s orders

How to do it
using (TrainingDataContext dc = new TrainingDataContext(lvConnectionString))
{
	DataLoadOptions options = new DataLoadOptions();
	options.LoadWith<Customer>(c => c.Orders);
	dc.LoadOptions = options;

	Customer lvCustomer = dc.Customers.Single(c => c.CustomerID == "JPPS");
	foreach (var item in lvCustomer.Orders)
	{
		Console.WriteLine("\tOrder Id: {0}, Date: {1}", item.OrderID, item.RequiredDate);
	}
}
How to use it
SELECT [t0].[CompanyName], [t0].[CustomerID], [t1].[OrderID], [t1].[CustomerID] AS [CustomerID2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (
    SELECT COUNT(*)
    FROM [dbo].[Orders] AS [t2]
    WHERE [t2].[CustomerID] = [t0].[CustomerID]
    ) AS [value]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE [t0].[CustomerID] = @p0
ORDER BY [t0].[CustomerID], [t1].[OrderID]
-- @p0: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [JPPS]
Generated SQL statement
Filters objects retrieved for a particular relationship one-to-many

In the sample code the orders will be pre-filtered to consider sub-sets of data in the following WHERE clauses,

Whitch data should be pre-filtered is configured using DataLoadOptions:AssociateWith(lambda) method

How to do it
using (TrainingDataContext dc = new TrainingDataContext(lvConnectionString))
{

	DataLoadOptions options = new DataLoadOptions();
	options.AssociateWith<Customer>(c => c.Orders.Where(p => p.ShippedDate != DateTime.Today));
	db.LoadOptions = dlo;

	var custOrderQuery =
			from c in db.Customers
			where c.City == "London"
			select c;
	}
}