c# - How to abstract a field in the Select list of a Linq query? -

i have calculated field regularly want return in "select" fields of linq query, e.g. customer order total year, along other demographic info of customer.

public class customer {   public decimal totalpurchasesthisyear(mydatacontext db) {     return db.orders.where(o => o.customerid == id)                     .sum(o => o.ordertotalamt);   } }  public class somereport {   public void getcustomerinfobysalesperson(long salespersonid) {     using (var db = new mydatacontext()) {       var q = db.customers.where(c => c.salespersonid == salespersonid)                           .select(c => new { c.name, c.address, thisyearpurchases = c.totalpurchasesthisyear(db) })                           .tolist();       // etc..     }   } } 

obviously, doesn't work, because totalpurchasesthisyear has no sql translation. inside have sql translation. don't want include code directly in query, because i'm doing same calculation in lots of places. gut tells me should done expression i've played around , can't quite work out right syntax.

help, anyone? thanks!

ok, i've found 1 way this, though i'm not sure it's best way:

public class customerorderinfo {   public long customerid;   public decimal totalpurchases; }  public class customer {   public static expression<func<customer, customerorderinfo>> getcustomerorderinfo() {     return c => new customerorderinfo {                   customerid = c.id,                   totalpurchases = c.orders.where(o => o.orderdate.year == datetime.now.year)                                     .sum(o => o.ordertotalamt)                 };   } }  public class somereport {   public void getcustomerinfobysalesperson(long salespersonid) {     using (var db = new mydatacontext()) {       var q = db.customers                 .join(db.customers.select(customer.getcustomerorderinfo()),                       c => c.id, => i.customerid,                       (c, i) => new { c.name, c.address, i.totalpurchases })                 .tolist();   // etc..     }   } } 

it works... underlying sql ends join on nested query, isn't ideal. want see underlying sql simple, like:

select c.name, c.address, sum(o.ordertotalamt) totalpurchases customer c  left join [order] o on o.customerid = c.id year(o.orderdate) = @year group c.name, c.address