LINQ to SQL "aggregating" property

Question

Let's say I have a table of Orders and a table of Payments.

Each Payment relates to a given order: Payment.orderId

I want to query my orders:

var query = from o in db.Orders where o.blah = blah select o;

But I also need the total paid for each order:

var query =  from o in db.Orders 
             where o.blah = blah 
             select new
             {
                 Order = o,
                 totalPaid =  (from p in db.Payments
                               where p.orderId == o.id
                               select p.Amount).Sum()
             };

LINQ to SQL generates exactly the SQL query I want.

My problem is that I'm adding payments support to an existing app. Therefore, to minimize code impact, I'd like totalPaid to be a property of my Order class.

I thought of adding a "manual" property and tried to fill it at query time. But writing the select clause is where I'm stuck:

var query =  from o in db.Orders 
             where o.blah = blah 
             select "o with o.totalPaid = (from p in db.Payments <snip>).Sum()"

How can I do that?

Answer 1

Normally, your Order class will have a navigation property called Payments. Utilizing this, your TotalPaid property would look like this:

public double TotalPaid
{
    get { return Payments.Sum(x => x.Amount); }
}

This solves another problem: This property is always up-to-date. Your approach would be outdated as soon as a new payment is added to the order.

If being up-to-date isn't so important but reducing the number of round trips to the database, you can use this code:

private double? _totalPaid;

public double TotalPaid
{
    get
    { 
        if(!_totalPaid.HasValue)
            _totalPaid = Payments.Sum(x => x.Amount);
        return _totalPaid.Value;
    }
}

Answer 2

You can add a payments EntitySet int the Orders class that points to the Payments class, as well as declare the TotalPaid property as suggested from Daniel Hilgarth.

But when you query the database for the Orders, LinqToSql will make 1 query for each order, in order to get the sum of payments. The workaround is to use the DataContext.LoadWith() method like this:

DataContext db = new Datacontext(connStr);
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Orders>(o=>o.Payments);
db.LoadOptions = dlo;
var query =  from o in db.Orders 
             where o.blah = blah 
//this will load the payments with the orders in a single query