Inside a LINQ statement, using a partial class property

asp.net-mvc asp.net-mvc-3 c# entity-framework linq

Question

I'm attempting to determine the best strategy for what I first believed to be a simple task. A line on an invoice is represented by my database model Line, which is named Line.

It appears basically like follows:

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 was created using the database model.
Another class I have adds a further property:

public partial class Line
{
    public Decimal Total
    {
        get
        {
            return this.Price * this.Quantity
        }
    }
}

Now, I want to perform something along these lines from my customer controller:

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 because of the notorious, I can't.

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 approach to refactoring this to make it functional?

I've tried i.Lines and LinqKit. AsEnumerable(), adding i.Lines to my InvoiceIndex model, and having it figure out the total for the display are the steps I took.

The last option "works," but I am unable to sort the data with it. In the long run, I want to be able to:

var invoices = ( from c in _repository.Customers
                         ...
        ).OrderBy( i => i.Total )

Additionally, I want to page my data, thus I don't want to spend time making the complete list of c.Invoices into an enumerable format ()

Bounty

I understand that for some individuals, this must be a significant issue. I have spent hours researching online and have come to the sad realization that no pleasant conclusion has been reached. However, I think this must be a very typical barrier for individuals attempting to use ASP MVC for sorting and paging. I am aware that because the property cannot be mapped to SQL, you cannot sort on it prior to paging, but I am trying to find a solution to achieve my goal.

requisites for the ideal response:

  • DRY, so all of my computations would be in one spot.
  • Support paging as well as sorting, in that order
  • not load the whole data table into memory. or AsEnumerable. AsArray

Finding a means to define the Linq to entities SQL in my extended partial class would make me very pleased. However, I've been informed that this is impossible. It should be noted that a solution need not utilize the Total attribute directly. It is not supported at all to call that property from an IQueryable object. I'm trying to find an alternate, but as straightforward and orthogonal, technique to get the same outcome.

Unless someone offers a flawless answer, the solution that receives the most votes at the end will win the reward.

Until you read the answer(s), disregard the following:

{1} I advanced Jacek's approach by utilizing LinqKit to make the properties invokeable. Therefore, even the. AsQueryable(). Our partial classes include the sum() function. Here are a few samples of what I am currently doing:

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;
        }
    }
}

The.Count checks were the first trick. They are necessary since, I suppose, you can't call. On a blank set, use AsQueryable. You have a Null materialization error.

With these three incomplete classes set up, you can now do stunts 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 allow the invocation of properties; if you attempt to cast a PropertyExpression into a LambaExpression, an error will be returned. There are two alternatives to this. First, pull the phrase oneself in this manner.

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 found to be quite stupid. In order to take out the get value whenever a property is encountered, I changed LinqKit. It's not like the compiler will resolve Customer because of the way it manipulates the expression in a manner akin to reflection. Harmony for us. I changed TransformExpr in ExpressionExpander.cs in three lines. Even though the code is probably not the safest and might break other things, it now works, and I have informed the creator of the problem.

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 can almost promise that this code will break something, but for now, its functionality is sufficient. :)

1
22
8/4/2011 2:48:57 PM

Accepted Answer

There is another approach that is a little more difficult but allows you to summarize this reasoning.

public partial class Line
{
    public static Expression<Func<Line,Decimal>> TotalExpression
    {
        get
        {
            return l => l.Price * l.Quantity
        }
    }
}

Afterward, modify the query to

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.AsQueryable().Sum(Line.TotalExpression),
                         Posted = i.Created,
                         Salesman = i.Salesman.Name
                     }
               )

It works properly for me, executes server-side queries, and conforms with the DRY principle.

22
8/3/2011 1:45:29 PM

Popular Answer

Your additional property is only a computation using the data from the model; EF cannot automatically translate it. This computation can be easily done using SQL, and EF can translates to the analysis. If you need it for your query, use it in lieu of the property.

Total = i.Lines.Sum( l => l.Price * l.Quantity)


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow