jqGrid & ASP.NET 4 MVC: How to make search implementation on a DBContext repository and 'calculated' properties?

Question

I'm trying to implement jqgrid search on MVC, following the interesting answer by @Oleg, regarding the question: ASP.NET MVC 2.0 Implementation of searching in jqgrid.

Actually I have a data repository based on EF & DBContext. Moreover, I have an entity with 'calculated' fields, I mean properties in DbSets that are calculated on the base of other fields.

I have two main problems, implementing the solution described in the first answer of the above link:

1st problem) The solution is based on ObjectQuery. I believe I have solved by creating a IObjectContextAdapter of my context and then casting with (ObjectQuery)...in my ignorance, I do not exactly know if this solution may be regarded as scalable or if there is a better solution...I am sure it exists, but it is beyond my knowledge!

2nd problem) At first query, the following EntitySqlException is raised: 'Calculated' is not a member of type 'Models.Ticket' in the currently loaded schemes

May you give me some kind of help or suggestion to above problems, please?

Here I put some parts of code I think could clarify:

PUBLIC ENUM public enum StatiTT : int { A = 1, B = 2, C = 3, D = 4, E = 5, F = 6, G = 7 };

'TICKET' ENTITY

 public class Ticket : IValidatableObject
 {

  public DateTime Data1 { get; set; }    
  public int StatoTicketID { get; set; }
....
  public int Calculated  // here's the problem...this is not a real field, it's a calculated property, as you see...
   {
      get
       {
           int mm=0;

           DateTime Ora = DateTime.Now;

           mm = (Data1 - Ora).Days*1440 + (Data1 - Ora).Hours * 60 + (Data1 - Ora).Minutes;

           if (StatoTicketID > (int)StatiTT.DI && mm < 0) mm = 10000000; 

           return mm;
       }
   }

CONTEXT

public class dbContext : DbContext
   {

       public DbSet<Ticket> Tickets{ get; set; }
     ........

**REPOSITORY (actually not used in the above solution) **

public class myRepository : ImyRepository, IDisposable
{
    private dbContext context;

    public myRepository(dbContext context)
    {
        this.context = context;
    }

    public IQueryable<Ticket> ListTicketsQ()
    {
        return (from e in context.Tickets select e);
    }
    ..........

CONTROLLER

     public JsonResult jqIndex(string sidx, string sord, int page, int rows, bool _search, string filters)
    {
        var context = new dbContext();
        var objectContext = ((IObjectContextAdapter)context).ObjectContext;
        var set = objectContext.CreateObjectSet<Ticket>();

        var serializer = new JavaScriptSerializer();
        Filters f = (!_search || string.IsNullOrEmpty(filters)) ? null : serializer.Deserialize<Filters>(filters);
        ObjectQuery<Ticket> filteredQuery =
            (f == null ? (ObjectQuery<Ticket>)set : f.FilterObjectSet((ObjectQuery<Ticket>)set));
        filteredQuery.MergeOption = MergeOption.NoTracking; // we don't want to update the data
        var totalRecords = filteredQuery.Count();

        var pagedQuery = filteredQuery.Skip("it." + sidx + " " + sord, "@skip",
                                            new ObjectParameter("skip", (page - 1) * rows))
                                     .Top("@limit", new ObjectParameter("limit", rows));
        // to be able to use ToString() below which is NOT exist in the LINQ to Entity
        var queryDetails = (from item in pagedQuery
                            select new {     
                                            item.Calculated, // << THIS 'property' RAISES EntitySqlException
                                            }).ToList();
         .....

Any help would be appreciated. Thank you very much!

Answer 1

It seems to me that you have some pure Entity Framework problems. I think that you can solve the problem by moving the calculation of the Ticket.Calculated property directly in the following statement

var queryDetails = (from item in pagedQuery
                    select new {
                        ...
                        (item.Amount + item.Tax), // Calculated directly
                        ...
                    }).ToList();

In the case the calculation of the property will not use Entity SQL and so you will have no EntitySqlException exception. Such approach should work. You can encapsulate the calculation of the property in any function if needed.

Another way can be the usage the calculation of the additional property directly in JavaScript code on the client side. For example if you need to display tree dependent columns in the grid: amount, tax and the total amount which is just the sum of amount and tax you can do this on the client side in the JavaScript code. jqGrid has beforeProcessing callback function which will be called before the data returned from the server will be processed. So You can enumerate items in the data.rows and set total property of every item as the sum of amount and tax (converted from String to Number). In the way you will reduce the size of data which will be send between server and the client.

Answer 2

The best working solution to my problem has been resolved following the precious hints of Mr @Oleg: I moved the calculated properties into SQL Server, creating Computed Columns for each property. Now it works fine and it is really fast!

I lost more time trying to get working the calculated properties with ObjectSet, than create new computed columns directly in the db! As rightly pointed by Oleg, simple things are always the best!

Just another hint, for who's using EF Codefirst: if you want to use computed properties, you must DROP COLUMNS after db creation and putting [DatabaseGenerated(DatabaseGeneratedOption.Computed)] attribute above property, as specified in here too.

Thank you very much Oleg! I hope this solution may help other people!