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. :)
See Question&Answers more detail:os
