Using a partial class property inside LINQ statement

I am trying to figure out the best way to do what I thought would be easy. I have a database model called Line that represents a line in an invoice. It looks roughly like so: ``` public partial class Line { public Int32 Id { get; set; } public Invoice Invoice { get; set; } public String Name { get; set; } public String Description { get; set; } public Decimal Price { get; set; } public Int32 Quantity { get; set; } } ``` This class is generated from the db model. I have another class that adds one more property: ``` public partial class Line { public Decimal Total { get { return this.Price * this.Quantity } } } ``` Now, from my customer controller I want to do something like this: ``` var invoices = ( from c in _repository.Customers where c.Id == id from i in c.Invoices select new InvoiceIndex { Id = i.Id, CustomerName = i.Customer.Name, Attention = i.Attention, Total = i.Lines.Sum( l => l.Total ), Posted = i.Created, Salesman = i.Salesman.Name } ) ``` But I can't thanks to the infamous ``` The specified type member 'Total' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. ``` What is the best way to refactor this so that it works? I have tried LinqKit, i.Lines.AsEnumerable(), and putting i.Lines in my InvoiceIndex model and having it calculate the sum for the view. That last solution 'works' but I cannot sort on that data. What I want to be able to do in the end is ``` var invoices = ( from c in _repository.Customers ... ).OrderBy( i => i.Total ) ``` Also I want to page my data, so I do not want to waste time converting the entire c.Invoices to a list with .AsEnumerable() Bounty I know this must be a somewhat big problem for some people. After hours of scouring the internet I have come to the conclusion that no happy conclusion has been made. Yet I believe this must be a fairly common roadblock for those who are trying to do paging and sorting with ASP MVC. I understand that the property can not be mapped to sql and therefore you cannot sort on it before paging, but what I am looking for is a way to get my desired result. Requirements for a perfect solution: - DRY, meaning my total calculations would exist in 1 place - Support both sorting and paging, and in that order - Not pull the whole table of data into memory with .AsEnumerable or .AsArray What I would be really happy to find is a way to specify the Linq to entities SQL in my extended partial class. But I have been told this is not possible. Note that a solution does not need to directly use the Total property. Calling that property from IQueryable is not supported at all. I am looking for a way to achieve the same result via a different method, yet equally simple and orthogonal. The winner of the bounty will be the solution with the highest votes at the end, unless someone posts a perfect solution :) Ignore below unless until you read the answer(s): {1} Using Jacek's solution I took it one step further and made the properties invokable using LinqKit. This way even the .AsQueryable().Sum() is enclosed in our partial classes. Here is some examples of what I am doing now: ``` public partial class Line { public static Expression<Func<Line, Decimal>> Total { get { return l => l.Price * l.Quantity; } } } public partial class Invoice { public static Expression<Func<Invoice, Decimal>> Total { get { return i => i.Lines.Count > 0 ? i.Lines.AsQueryable().Sum( Line.Total ) : 0; } } } public partial class Customer { public static Expression<Func<Customer, Decimal>> Balance { get { return c => c.Invoices.Count > 0 ? c.Invoices.AsQueryable().Sum( Invoice.Total ) : 0; } } } ``` First trick was the .Count checks. Those are needed because I guess you cannot call .AsQueryable on an empty set. You get an error about Null materialization. With these 3 partial classes laid out you can now do tricks like ``` var customers = ( from c in _repository.Customers.AsExpandable() select new CustomerIndex { Id = c.Id, Name = c.Name, Employee = c.Employee, Balance = Customer.Balance.Invoke( c ) } ).OrderBy( c => c.Balance ).ToPagedList( page - 1, PageSize ); var invoices = ( from i in _repository.Invoices.AsExpandable() where i.CustomerId == Id select new InvoiceIndex { Id = i.Id, Attention = i.Attention, Memo = i.Memo, Posted = i.Created, CustomerName = i.Customer.Name, Salesman = i.Salesman.Name, Total = Invoice.Total.Invoke( i ) } ) .OrderBy( i => i.Total ).ToPagedList( page - 1, PageSize ); ``` Very cool. There is a catch, LinqKit does not support the invocation of properties, you will get an error about trying to cast PropertyExpression to LambaExpression. There are 2 ways around this. Firstly is to pull the expression yourself like so ``` var tmpBalance = Customer.Balance; var customers = ( from c in _repository.Customers.AsExpandable() select new CustomerIndex { Id = c.Id, Name = c.Name, Employee = c.Employee, Balance = tmpBalance.Invoke( c ) } ).OrderBy( c => c.Balance ).ToPagedList( page - 1, PageSize ); ``` which I thought was kind of silly. So I modified LinqKit to pull out the get{} value when it encounters a property. The way it operates on the expression is similar to reflection, so its not like the compiler is going to resolve Customer.Balance for us. There is a 3 line change I made to TransformExpr in ExpressionExpander.cs. Its probably not the safest code and might break other things, but it works for now and I have notified the author about the deficiency. ``` Expression TransformExpr (MemberExpression input) { if( input.Member is System.Reflection.PropertyInfo ) { return Visit( (Expression)( (System.Reflection.PropertyInfo)input.Member ).GetValue( null, null ) ); } // Collapse captured outer variables if( input == null ``` In fact I pretty much guarantee that this code will break some things, but it works at the moment and that is good enough. :)