returning a list in C# IQueryable function

I'm using Visual Studio 2013 and working on a C#, Razor ASP.NET MVC Application.
I'm using Employees table from the Northwind sql server sample database.

So right now I have code first data model I have the following files:

Models/Entities/Employee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MvcGrid.Models.Entities
{
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Title { get; set; }

    }
}

Open in new window


IRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MvcGrid.Models.Entities;

namespace MvcGrid.Models
{
    public interface IRepository
    {
        IQueryable<Employee> Employees();

    }
}

Open in new window





FakeEmployeesRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using MvcGrid.Models.Entities;

namespace MvcGrid.Models.Repositories
{
    public class FakeEmployeesRepository : IRepository
    {
        #region IRepository<Employee> Members

        public IQueryable<Employee> Employees()
        {
            return new List<Employee>()
            {
                new Employee { EmployeeID=1, LastName = "Davolio", FirstName = "Nancy", Title = "Sales Representative" },
                new Employee { EmployeeID=2, LastName = "Fuller", FirstName = "Andrew", Title = "Vice President, Sales" },
                new Employee { EmployeeID=3, LastName = "Leverling", FirstName = "Janet", Title = "Sales Representative" },
                new Employee { EmployeeID=4, LastName = "Peacock", FirstName = "Margaret", Title = "Sales Representative" },
                new Employee { EmployeeID=5, LastName = "Buchanan", FirstName = "Steven", Title = "Sales Manager" },
                new Employee { EmployeeID=6, LastName = "Suyama", FirstName = "Michael", Title = "Sales Representative" },
                new Employee { EmployeeID=7, LastName = "King", FirstName = "Robert", Title = "Sales Representative" },
                new Employee { EmployeeID=8, LastName = "Callahan", FirstName = "Laura", Title = "Inside Sales Coordinator" },
                new Employee { EmployeeID=9, LastName = "Dodsworth", FirstName = "Anne", Title = "Sales Representative" },

            }.AsQueryable();
        }

        #endregion

    }
}

Open in new window


My HomeController.cs looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using MvcGrid.Models;
using MvcGrid.Models.Grid;
//using MvcGrid.Models.Entities;
using MvcGrid.Models.Helpers;

namespace MvcGrid.Controllers
{
    public class HomeController : Controller
    {
        private IRepository _repository;
        public HomeController(IRepository repository)
        {
            _repository = repository;
        }

        //
        // GET: /Home/

        public ActionResult Index()
        {
            return View();
        }

        public JsonResult GetData(GridSettings grid)
        {

            var query = _repository.Employees();

            //filtring
            if (grid.IsSearch)
            {
                //And
                if (grid.Where.groupOp == "AND")
                    foreach (var rule in grid.Where.rules)
                        query = query.Where<Employee>(
                            rule.field, rule.data,
                            (WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
                else
                {
                    //Or
                    var temp = (new List<Employee>()).AsQueryable();
                    foreach (var rule in grid.Where.rules)
                    {
                        var t = query.Where<Employee>(
                        rule.field, rule.data,
                        (WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
                        temp = temp.Concat<Employee>(t);
                    }
                    //remove repeating records
                    query = temp.Distinct<Employee>();
                }
            }

            //sorting
            query = query.OrderBy<Employee>(grid.SortColumn,
                grid.SortOrder);

            //count
            var count = query.Count();

            //paging
            var data = query.Skip((grid.PageIndex - 1) * grid.PageSize).Take(grid.PageSize).ToArray();

            //converting in grid format
            var result = new
            {
                total = (int)Math.Ceiling((double)count / grid.PageSize),
                page = grid.PageIndex,
                records = count,
                rows = (from host in data
                        select new
                        {
                            //IsOnline = host.IsOnline.ToString(),
                            //Name = host.Name,
                            //IP = host.IP,
                            //User = host.User,
                            EmployeeID = host.EmployeeID.ToString(),
                            LastName = host.LastName,
                            FirstName = host.FirstName,
                            Title = host.Title,
                        }).ToArray()
            };

            //convert to JSON and return to client
            return Json(result, JsonRequestBehavior.AllowGet);

        }


    }
}

Open in new window



In my controller I have a function that calls the Employees() function from IRepository.cs and then converts that to JSON format and then I'm feeding that json to a jquery jqgrid.

Right now for my model I'm using the above files and it works fine.
If you notice, my file FakeEmployeesRepository.cs contains a static data representation of the first four columns of the Employees table.

So now I want to revise my model to use the actual Employees table from the Northwind sql server database. I created an Entity Framework 6 database first model for my Employees table.

My model called northwindEF.edmx looks like this:
northwindEF.edmx model
So now in my FakeEmployeesRepository.cs instead of using the static data as shown above I want to call my Employee entity from my database model.

This my revised FakeEmployeesRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using MvcGrid.Models;

namespace MvcGrid.Models.Repositories
{
    public class FakeEmployeesRepository : IRepository
    {
        #region IRepository<Employee> Members

        public IQueryable<Employee> Employees()
        {
            return new List<Employee>()
            {
                //new Employee { EmployeeID=1, LastName = "Davolio", FirstName = "Nancy", Title = "Sales Representative" },
                //new Employee { EmployeeID=2, LastName = "Fuller", FirstName = "Andrew", Title = "Vice President, Sales" },
                //new Employee { EmployeeID=3, LastName = "Leverling", FirstName = "Janet", Title = "Sales Representative" },
                //new Employee { EmployeeID=4, LastName = "Peacock", FirstName = "Margaret", Title = "Sales Representative" },
                //new Employee { EmployeeID=5, LastName = "Buchanan", FirstName = "Steven", Title = "Sales Manager" },
                //new Employee { EmployeeID=6, LastName = "Suyama", FirstName = "Michael", Title = "Sales Representative" },
                //new Employee { EmployeeID=7, LastName = "King", FirstName = "Robert", Title = "Sales Representative" },
                //new Employee { EmployeeID=8, LastName = "Callahan", FirstName = "Laura", Title = "Inside Sales Coordinator" },
                //new Employee { EmployeeID=9, LastName = "Dodsworth", FirstName = "Anne", Title = "Sales Representative" },

            }.AsQueryable();
        }

        #endregion

    }
}

Open in new window


I commented out the static items, but not sure on the syntax to use in my Employees() function to call the Employees entity from my model.

Anyone know the syntax to call my Employees entity inside my IQueryable function?
LVL 1
maqskywalkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
from here:
http://blogs.msdn.com/b/wriju/archive/2010/06/09/ado-net-entity-framework-4-0-loading-data-in-4-ways.aspx
using (var ctx = new northwindEF())

{

    var q = from em in ctx.Employees

            select em;

List<Employee> emps = new List<Employee>
 

    foreach (var emp in q)
    {
       emps.Add(emp);
    }

return emps;

}

Open in new window

0
maqskywalkerAuthor Commented:
this is what I have for FakeEmployeesRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using MvcGrid.Models;

namespace MvcGrid.Models.Repositories
{
    public class FakeEmployeesRepository : IRepository
    {
        #region IRepository<Employee> Members

        public IQueryable<Employee> Employees()
        {

                 using (var ctx = new MvcGrid.Models.NorthwindEntities())

                {

                    var q = from em in ctx.Employees

                            select em;

                List<Employee> emps = new List<Employee>
 

                    foreach (var emp in q)
                    {
                       emps.Add(emp);
                    }

                return emps;

                }
            

        }

        #endregion
        
    }
}

Open in new window


I'm getting an error on the foreach as shown with red underlines in the pic below.  
I'm still doing something wrong. This is what my directory looks like:
Do you know where I misunderstood you?

my error
0
Kyle AbrahamsSenior .Net DeveloperCommented:
What does the error say?

Also note move the declaration before the using and the return after the using:

  List<Employee> emps = new List<Employee>();

   //using

  return emps;

Sorry I did this free hand.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.