Linq to SQL question - specifying columns then modifying column

Question

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

Answer 1

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());

Answer 2

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