Solved

returning a list in C# IQueryable function

Posted on 2014-09-03
3
382 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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 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