Solved

returning a list in C# IQueryable function

Posted on 2014-09-03
3
403 Views
Last Modified: 2014-09-09
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?
0
Comment
Question by:maqskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40301583
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
 
LVL 1

Author Comment

by:maqskywalker
ID: 40301757
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
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40302247
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question