Solved

returning a list in C# IQueryable function

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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

810 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