Use the LoadWith method to specify which data related to your main target should be retrieved at the same time. For example, if you know you will need information about customers' orders, you can use LoadWith to make sure the order information is retrieved at the same time as the customer information. This approach results in only one trip to the database for both sets of information.
NorthwindDataContext db = new NorthwindDataContext();
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith<Customer>(c => c.Orders);
opt.LoadWith<Order>(o => o.Order_Details);
db.LoadOptions = opt;
var data = (from c in db.Customers
select new { c.CompanyName, c.Orders }).Skip(startRowIndex).Take(maxRows);
return data.ToList();
Using ROW_NUMBER() to paginate your data
SELECT CompanyName, CustomerID
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY CompanyName) AS ROW_NUMBER, CompanyName, CustomerID
FROM dbo.Customers
) AS T
WHERE ROW_NUMBER BETWEEN 1 AND 10
ORDER BY ROW_NUMBER
SELECT t0.OrderID
, t0.CustomerID
, convert(char,t0.OrderDate,101) AS OrderDate
, t1.ProductID
, t1.UnitPrice
, t1.Quantity
, (
SELECT COUNT(*)
FROM dbo.[Order Details]
WHERE OrderID = t0.OrderID
) AS OrderCount
FROM dbo.Orders AS t0
LEFT JOIN dbo.[Order Details] AS t1 ON t1.OrderID = t0.OrderID
WHERE t0.CustomerID = 'ALFKI'
ORDER BY t0.OrderID, t1.ProductID
Using ROW_NUMBER() to paginate your data with SQL Server 2005 and ASP.NET