title: Using a partial class property inside LINQ statement
body: <p>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.</p>
  
  <p>It looks roughly like so:</p>
  
  <pre><code>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; }
  }
  </code></pre>
  
  <p>This class is generated from the db model.<br>
  I have another class that adds one more property:</p>
  
  <pre><code>public partial class Line
  {
      public Decimal Total
      {
          get
          {
              return this.Price * this.Quantity
          }
      }
  }
  </code></pre>
  
  <p>Now, from my customer controller I want to do something like this:</p>
  
  <pre><code>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 =&gt; l.Total ),
                               Posted = i.Created,
                               Salesman = i.Salesman.Name
                           }
          )
  </code></pre>
  
  <p>But I can't thanks to the infamous </p>
  
  <pre><code>The specified type member 'Total' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.
  </code></pre>
  
  <p>What is the best way to refactor this so that it works?  </p>
  
  <p>I have tried LinqKit, i.Lines.AsEnumerable(), and putting i.Lines in my InvoiceIndex model and having it calculate the sum for the view.</p>
  
  <p>That last solution 'works' but I cannot sort on that data.  What I want to be able to do in the end is</p>
  
  <pre><code>var invoices = ( from c in _repository.Customers
                           ...
          ).OrderBy( i =&gt; i.Total )
  </code></pre>
  
  <p>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()</p>
  
  <p><strong>Bounty</strong></p>
  
  <p>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.</p>
  
  <p>Requirements for a perfect solution:</p>
  
  <ul>
  <li>DRY, meaning my total calculations would exist in 1 place</li>
  <li>Support both sorting and paging, and in that order</li>
  <li>Not pull the whole table of data into memory with .AsEnumerable or .AsArray</li>
  </ul>
  
  <p>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.</p>
  
  <p>The winner of the bounty will be the solution with the highest votes at the end, unless someone posts a perfect solution :)</p>
  
  <p><strong>Ignore below unless until you read the answer(s):</strong></p>
  
  <p><strong>{1}</strong>
  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:</p>
  
  <pre><code>public partial class Line
  {
      public static Expression&lt;Func&lt;Line, Decimal&gt;&gt; Total
      {
          get
          {
              return l =&gt; l.Price * l.Quantity;
          }
      }
  }
  
  public partial class Invoice
  {
      public static Expression&lt;Func&lt;Invoice, Decimal&gt;&gt; Total
      {
          get
          {
              return i =&gt; i.Lines.Count &gt; 0 ? i.Lines.AsQueryable().Sum( Line.Total ) : 0;
          }
      }
  }
  
  public partial class Customer
  {
      public static Expression&lt;Func&lt;Customer, Decimal&gt;&gt; Balance
      {
          get
          {
              return c =&gt; c.Invoices.Count &gt; 0 ? c.Invoices.AsQueryable().Sum( Invoice.Total ) : 0;
          }
      }
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>With these 3 partial classes laid out you can now do tricks like</p>
  
  <pre><code>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 =&gt; 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 =&gt; i.Total ).ToPagedList( page - 1, PageSize );
  </code></pre>
  
  <p>Very cool.</p>
  
  <p>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</p>
  
  <pre><code>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 =&gt; c.Balance ).ToPagedList( page - 1, PageSize );
  </code></pre>
  
  <p>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.  </p>
  
  <pre><code>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
  </code></pre>
  
  <p>In fact I pretty much guarantee that this code will break some things, but it works at the moment and that is good enough. :)</p>