Returns "System.Collections.Generic.List" instead of recordset in XML or JSON

Question

User629837836 posted

I have a table of employees that I am wanting to return all to the browser. I have followed the tutorial here "http://csharp-video-tutorials.blogspot.com/2016/09/aspnet-web-api-and-sql-server.html" and did the same thing. However, when I run this I do not get the list in XML at all.

I put this into the browser address bar:
http://somewebsite.net/Employee/GetAll

{CONTROLLER CODE}
// GET: ALL Employee
public List<DimEmployee> GetAll()
{
using (AdventureWorks_MBDEV_DW2008Entities entities = new AdventureWorks_MBDEV_DW2008Entities())
{
return entities.DimEmployees.ToList();
}
}

I get this returned in the browser window instead of XML or JSON:
System.Collections.Generic.List`1[AdventureWorksDataAccess.DimEmployee]

I have seen two tutorials where this returns XML but for some reason not for me. I am using MVC5 Web API; can anyone tell me why this isn't working?

Thanks.

Answer 1

User629837836 posted

Found one issue and fixed it:

I created a WebApi controller that for some reason was inheriting Controller and not ApiController
public class EmployeeController : ApiController


I fixed that and now I just get an error.
Server Error in '/' Application.

The resource cannot be found.

Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.

Requested URL: /Employee/Get

Answer 2

User1881638666 posted

Hi,

Only the url '/Employee' is sufficient in this case.

Got through the followings to understand how the routing works in Web Api2.

https://www.asp.net/web-api/overview/web-api-routing-and-actions/routing-in-aspnet-web-api

https://www.asp.net/web-api/overview/web-api-routing-and-actions/routing-and-action-selection

Further you could enable attribute routing and add [Route] attribute to the action.

Enable attribute routing in webapiconfig.cs,

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        // Attribute routing.
        config.MapHttpAttributeRoutes();

        // Convention-based routing.
        config.Routes.MapHttpRoute(
            name: "DefaultApi",
            routeTemplate: "api/{controller}/{id}",
            defaults: new { id = RouteParameter.Optional }
        );
    }
}

Then add the required route to the action method.

eg: 

[Route("All")]
public List<DimEmployee> GetAll()

Then the url is, '/Employee/All'

https://www.asp.net/web-api/overview/web-api-routing-and-actions/attribute-routing-in-web-api-2

Thanks & Regards,

Wenushka

Answer 3

User629837836 posted

Hi,

I fixed the routing, I can actually get an individual employee. But now when I got to grab all 200 or so the it just keeps loading and loading and loading with no error but never gets the employees.

Code that grabs them all, I even disabled lazy loading and Proxy.

        // GET: ALL Employee
        public IEnumerable<DimEmployee> Get()
        {
            using (AdventureWorks_MBDEV_DW2008Entities entities = new AdventureWorks_MBDEV_DW2008Entities())
            {
                entities.Configuration.LazyLoadingEnabled = false;
                entities.Configuration.ProxyCreationEnabled = false;
                //CurrentDbContext.Configuration.ProxyCreationEnabled = false;
                return entities.DimEmployees.ToList();
            }
        }

I figured I would try to just grab certain columns and see if that would work; I can't seem to convert a list to a list?:

        // GET: ALL Employee
        public List<DimEmployee> Get()
        {
            using (AdventureWorks_MBDEV_DW2008Entities entities = new AdventureWorks_MBDEV_DW2008Entities())
            {
                entities.Configuration.LazyLoadingEnabled = false;
                entities.Configuration.ProxyCreationEnabled = false;
                var qry = from b in entities.DimEmployees
                          orderby b.StartDate descending
                          select new { b.FirstName, b.LastName, b.Title, b.EmailAddress };

                return qry.ToList();
            }
        }

Get Single employee works fine:

        public DimEmployee Get(int id)
        {

            using (AdventureWorks_MBDEV_DW2008Entities entities = new AdventureWorks_MBDEV_DW2008Entities())
            {
                entities.Configuration.ProxyCreationEnabled = false;
                var employee = entities.DimEmployees.FirstOrDefault(e => e.EmployeeKey == id);
                if (employee == null)
                {
                    //return HttpNotFoundResult();
                }
                return (employee);
            }
        }

Thanks for the help with the routing, I am just stuck and pulling a list of employees now; an ideas?

Answer 4

User753101303 posted

Hi,

Use F12 maybe to see what happens for your http query. Usually you try to select the rows you want rather than to select all rows from a table. Still it seems unexpected that it "nevers ends". Usually when something is wrong you always something that ends up happening. If using js are you sure you don't have a JavaScript error.

For the second problem it should tell quite clearly that this is a type problem. On one side you have a list of employee object while your function returns a list of anonymous type objects so the compiler complains about this discrepancy.

Answer 5

User629837836 posted

This code gets run to pull all employees:

// GET: ALL Employee
public HttpResponseMessage Get()
{
using (AdventureWorks_MBDEV_DW2008Entities entities = new AdventureWorks_MBDEV_DW2008Entities())
{
entities.Configuration.LazyLoadingEnabled = false;
entities.Configuration.ProxyCreationEnabled = false;
//return entities.DimEmployees.ToList();
return Request.CreateResponse(HttpStatusCode.OK, entities.DimEmployees.ToList());
}
}

Found out why it goes on and on and on; seems that an error is happening on the Windows 2012 server when this code is run:

Faulting application name: w3wp.exe, version: 8.5.9600.16384, time stamp: 0x5215df96
Faulting module name: clr.dll, version: 4.6.1085.0, time stamp: 0x57a4fd94
Exception code: 0xc00000fd
Fault offset: 0x000000000015171b
Faulting process id: 0x108
Faulting application start time: 0x01d22c01813fdfa8
Faulting application path: c:\windows\system32\inetsrv\w3wp.exe
Faulting module path: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\clr.dll
Report Id: 4b99c77c-97f9-11e6-80e3-000c294c4a77
Faulting package full name:
Faulting package-relative application ID:

I am not sure what to do to fix this issue; it appears that the framework itself is having a problem on the Windows 2012 server.

Any Ideas?

Answer 6

User1404573039 posted

Hi mbelcher,

To check whether it is related with your entities, I suggest you make a test with below code:
 

       // GET: ALL Employee
        public HttpResponseMessage GetAuthors()
        {
            Author a1 = new Author { Id=1,Name="Tom" };
            Author a2 = new Author { Id=2,Name="Jim"};
            Author a3 = new Author { Id=3,Name="Tony"};
            List<Author> la = new List<Author>();
            la.Add(a1);
            la.Add(a2);
            la.Add(a3);
            return Request.CreateResponse(HttpStatusCode.OK,la.ToList());
        }

If you could get expected json file, I assume your issue is related with entities.DimEmployees. I suggest you put a breakpoint to debug your method and check whether entities.DimEmployees returns the expected records.

Best Regards,

Tony

Answer 7

User629837836 posted

I actually figured out what was going on and ended up with this as the controller action and it works:

    public class DimCustomersController : Controller
    {
        private AdventureWorks_MBDEV_DW2008Entities db = new AdventureWorks_MBDEV_DW2008Entities();

        // GET: DimCustomers With DimCustomersIndexViewModel
        public ActionResult CustomersIndexVM()// Add some parameters here for filtering and records to return.
        {
            List<DimCustomersIndexViewModel> CustomerList = new List<DimCustomersIndexViewModel>();
            var CustomerCount = db.DimCustomers.Count();
            //DimCustomersIndexViewModel CustomerItem = new DimCustomersIndexViewModel();
            //for (int m = 0; m < CustomerCount; m++)
            foreach (var m in db.DimCustomers.Take(50).ToList())// db.DimCustomers.Take(100) to limit results.. this can be a variable that is sent or in a post.
            {
                DimCustomersIndexViewModel CustomerItem = new DimCustomersIndexViewModel();
                CustomerItem.Title = m.Title;
                CustomerItem.FirstName = m.FirstName;
                CustomerItem.MiddleName = m.MiddleName;
                CustomerItem.LastName = m.LastName;
                CustomerItem.BirthDate = m.BirthDate;
                CustomerItem.MaritalStatus = m.MaritalStatus;
                CustomerItem.Suffix = m.Suffix;
                CustomerItem.Gender = m.Gender;
                CustomerItem.EmailAddress = m.EmailAddress;
                CustomerItem.AddressLine1 = m.AddressLine1;
                CustomerItem.AddressLine2 = m.AddressLine2;
                CustomerItem.Phone = m.Phone;
                //other columns go here
                CustomerList.Add(CustomerItem);
            }
            return View("CustomersIndexVM", CustomerList.ToList());
        }

Thanks for all the help.