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. :)