LINQ to SQL: Mapping a subset of an association

Question

During our initial development we haven't worried about scaling concerns, just getting the bare bones of the system working as a cohesive whole.

We are now looking at refining screens where the quantity of records will become too large to be displayed. For example, we have page for displaying the details of a Parent which currently involves displaying all the Child records. This is done by calling the Children property on the Parent object (we are trying to develop a rich domain). We want to change this to be RecentChildren.

The problem is that I can't find a way to limit the records returned by an EntitySet in any way. You can do a query against the EntitySet but it retrieves all the Children from the database and then uses LINQ to Objects to filter it. Obviously this is very inefficient.

We could restructure the code to remove the mapping of the Children property and retrieve them from a service instead but we would like to keep the association in the domain if at all possible.

Is there any way round this or would we have to look at a different ORM such as NHibernate?

Answer 1

Well, you could simply not use the navigation property - i.e. use the parent id directly and select from the child set?

i.e.

int id = parent.ParentID;
var qry = from child in db.Children
          where child.ParentID = id && child.Date > whatever
          select child;

I also suspect that doing a SelectMany by using the navigation properties inside a query would work:

using System;
using System.Linq;
using ConsoleApplication5;


class Program
{
    static void Main(string[] args)
    {
        string id;
        using (var ctx = new DataClasses1DataContext())
        {
            id = ctx.Customers.Select(x => x.CustomerID).First();
        }
        DateTime cutoff = DateTime.Today.AddMonths(-2);

        using (var ctx = new DataClasses1DataContext())
        {
            // parent id
            ctx.Log = Console.Out;
            var qry = from o in ctx.Orders
                      where o.CustomerID == id
                        && o.OrderDate > cutoff
                      select o;
            foreach (var order in qry)
            {
                Console.WriteLine(order.OrderID);
            }
        }

        using (var ctx = new DataClasses1DataContext())
        {
            // navigation property in query
            ctx.Log = Console.Out;
            var qry = from c in ctx.Customers
                      where c.CustomerID == id
                      from o in c.Orders
                      where o.OrderDate > cutoff
                      select o;
            foreach (var order in qry)
            {
                Console.WriteLine(order.OrderID);
            }
        }
    }
}

TSQL (sorry about the format - console dump!):

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [
t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[Sh
ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPosta
lCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE ([t0].[CustomerID] = @p0) AND ([t0].[OrderDate] > @p1)
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [ALFKI]
-- @p1: Input DateTime (Size = 0; Prec = 0; Scale = 0) [09/11/08 00:00:00]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

SELECT [t1].[OrderID], [t1].[CustomerID], [t1].[EmployeeID], [t1].[OrderDate], [
t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[Sh
ipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPosta
lCode], [t1].[ShipCountry]
FROM [dbo].[Customers] AS [t0], [dbo].[Orders] AS [t1]
WHERE ([t1].[OrderDate] > @p0) AND ([t0].[CustomerID] = @p1) AND ([t1].[Customer
ID] = [t0].[CustomerID])
-- @p0: Input DateTime (Size = 0; Prec = 0; Scale = 0) [09/11/08 00:00:00]
-- @p1: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [ALFKI]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

Note that it both cases the date condition went down to the database.

Answer 2

Add a RecentChildren property to the Parent partial class. Don't implement it as an EntitySet.

Answer 3

After blogging about my LINQ to SQL woes, a guy called Anders pointed me in the direction of a solution to this problem. Utilising DataLoadOptions.AssociateWith.