Solved

returning a list in C# IQueryable function

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

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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 39

Accepted Solution

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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.…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now