LINQ to SQL "aggregation" properties

Question

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

Each payment relates to a specific order: Payment.orderId

I want to request my orders:

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

But I also need the total amount 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 that I want.

My problem is that I am adding payment support to an existing application. Therefore, to minimize the impact of code, I would like totalPaid be a property of my Order class.

I thought about adding the "manual" property and tried to populate it during the request. But the record of the select clause is where I got 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 this?

Answer 1

Typically, your Order class will have a navigation property called Payments . Using this, your TotalPaid property will look like this:

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

This solves another problem: this property is always relevant. Your approach will be obsolete as soon as a new payment is added to the order.

If relevance is relevant, it is not so important, but reducing the number of return flights 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 EntitySet payments to the Orders class, which points to the Payments class, and also declare the TotalPaid property proposed by Daniel Hilgarth.

But when you request a database for orders, LinqToSql will make 1 request for each order to get the amount of payments. The workaround is to use the DataContext.LoadWith () method as follows:

 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