tags:

views:

259

answers:

2

Hey, I'm trying to write a linq to sql method that handles sorting, paging, and filtering for an ajax grid. I created a partial class Employee that has a TotalRecordCount, as I need to pass this to the javascript for setting up the pager. The problem is that it won't build because I can't set the AnonymousType#1.TotalRecordCount, it's read-only. Yet if I do "select new Employee", then it will throw the Exception - "Explicit construction of entity type 'InVision.Data.Employee' in query is not allowed.".

Here's the code...

public string GetPageJSON(string sortColumn, string sortDirection, int pageNumber, int pageSize, EmployeeSearch search)
        {
            var query = from e in db.Employees
                        select new
                        {
                            EmployeeID = e.EmployeeID,
                            FirstName = e.FirstName,
                            LastName = e.LastName,
                            LoginName = e.LoginName,
                            IsLockedOut = e.IsLockedOut,
                            TotalRecordCount = e.TotalRecordCount
                        };
            //searching.
            if (search.FirstName.Length > 0) query = query.Where(e => e.FirstName.Contains(search.FirstName));
            if (search.LastName.Length > 0) query = query.Where(e => e.LastName.Contains(search.LastName));
            if (search.LoginName.Length > 0) query = query.Where(e => e.LoginName.Contains(search.LoginName));
            if (search.Status.Length > 0) query = query.Where(e => (search.Status == "Active" && !e.IsLockedOut) 
                || (search.Status == "Inactive" && e.IsLockedOut));
            //sorting.
            query = query.OrderBy(sortColumn, sortDirection);
            //get total record count.
            int totalRecordCount = query.Count();
            //paging.
            query = query.Skip((pageNumber - 1) * pageSize).Take(pageSize);
            //set total record count.
            var list = query.ToList();
            if (list.Count > 0)
            {
                list[0].TotalRecordCount = totalRecordCount; //throws exception
            }
            //return json.
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            return serializer.Serialize(list);
        }
+1  A: 

You'll want to select the original objects rather than mapping them to new objects (whether of the same type, or an anonymous type).

Replace this:

var query = from e in db.Employees
                        select new
                        {
                            EmployeeID = e.EmployeeID,
                            FirstName = e.FirstName,
                            LastName = e.LastName,
                            LoginName = e.LoginName,
                            IsLockedOut = e.IsLockedOut,
                            TotalRecordCount = e.TotalRecordCount
                        };

With this:

var query = db.Employees.AsQueryable();

Then later on replace this:

var list = query.ToList();
if (list.Count > 0)
{
    list[0].TotalRecordCount = totalRecordCount;
}

With this:

var list = from e in query
           select new
           {
               EmployeeID = e.EmployeeID,
               FirstName = e.FirstName,
               LastName = e.LastName,
               LoginName = e.LoginName,
               IsActive = !e.IsLockedOut,
               TotalRecordCount = totalRecordCount
           };

I think that should be everything. If the JavaScriptSerializer requires a List, just make sure you use it like this: return serializer.Serialize(list.ToList());

Ryan Versaw
Thanks but this doesn't work because I need to limit the columns I return so that the message size is smaller, also because it errors out when it tries to grab everything with errors like "String must be exactly one character long" and circular references.
Justin
The other alternative I would have brought up (given the info you just gave me) would be to do what I have above, then map it to a collection of new objects right before serializing. This would have prevented the need for a new class, but would have also caused linq to pull in every field from the DB as opposed to the limited set you have.
Ryan Versaw
What would the code look like with that approach? I would love to get rid of these extra view classes! :)
Justin
Let me know if these additions work - I think they should, but I may have missed something! (There may also be a much better way of accomplishing this, but hopefully someone will chime in if I'm missing it)
Ryan Versaw
That worked great, thanks!! ~10 less view classes, woohoo...
Justin
+1  A: 

I ended up using a custom view class to get this to work...

partial class EmployeeView
    {
        public int EmployeeID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string LoginName { get; set; }
        public bool IsActive { get; set; }
        public int TotalRecordCount { get; set; }
    }

public string GetPageJSON(string sortColumn, string sortDirection, int pageNumber, int pageSize, EmployeeSearch search)
        {
            var query = from e in db.Employees
                        select new EmployeeView
                        {
                            EmployeeID = e.EmployeeID,
                            FirstName = e.FirstName,
                            LastName = e.LastName,
                            LoginName = e.LoginName,
                            IsActive = !e.IsLockedOut,
                            TotalRecordCount = 0
                        };
            //searching.
            if (search.FirstName.Length > 0) query = query.Where(e => e.FirstName.Contains(search.FirstName));
            if (search.LastName.Length > 0) query = query.Where(e => e.LastName.Contains(search.LastName));
            if (search.LoginName.Length > 0) query = query.Where(e => e.LoginName.Contains(search.LoginName));
            if (search.Status.Length > 0) query = query.Where(e => (search.Status == "Active" && e.IsActive) 
                || (search.Status == "Inactive" && !e.IsActive));
            //sorting.
            query = query.OrderBy(sortColumn, sortDirection);
            //get total record count.
            int totalRecordCount = query.Count();
            //paging.
            query = query.Skip((pageNumber - 1) * pageSize).Take(pageSize);
            //set total record count.
            var list = query.ToList();
            if (list.Count > 0)
            {
                list[0].TotalRecordCount = totalRecordCount;
            }
            //return json.
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            return serializer.Serialize(list);
        }
Justin