LINQ to SQL: mapping a subset of an association

Question

During our initial development, we did not worry about scaling problems, just getting the bare bones of a system that works as a cohesive whole.

Now we look at the refinement screens, where the number of entries will become too large to display. For example, we have a page for displaying information about Parent, which currently includes a display of all entries Child. This is done by calling the properties of the Childrenobject Parent(we are trying to develop a rich domain). We want to change this as RecentChildren.

The problem is that I just can't find a way to limit the records returned EntitySet. You can query to EntitySet, but it retrieves everything Childrenfrom the database, and then uses LINQ to Objects to filter. Obviously, this is very inefficient.

We could restructure the code to remove the property mapping Childrenand get them from the service, but we would like to keep the association in the domain, if at all possible.

Is there a way around this, or should we look at another ORM like NHibernate?

Answer 1

LINQ to SQL, . DataLoadOptions.AssociateWith.

Answer 2

, - .. ?

.

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

, SelectMany :

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 ( - !):

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

, .

Answer 3

Add the RecentChildren property to the incomplete Parent class. Do not implement it as an EntitySet.