How to return IEnumerable DbSet in web api get?

Question

"user" classs auto generated by entity framework model first desinger.

public partial class user
{
    public user()
    {
        this.distributorDevice = new HashSet<distributorDevice>();
        this.managerDevice = new HashSet<managerDevice>();
        this.logUserLogin = new HashSet<logUserLogin>();
        this.logUserOperates = new HashSet<logUserOperates>();
        this.clientApp = new HashSet<clientApp>();
    }

    public int Id { get; set; }
    public string name { get; set; }
    public byte[] password { get; set; }
    public bool isActive { get; set; }
    public System.DateTime RegisterTime { get; set; }
    public int permissionId { get; set; }
    public System.DateTime lastLoginTime { get; set; }

    public virtual permission permission { get; set; }
    public virtual ICollection<distributorDevice> distributorDevice { get; set; }
    public virtual ICollection<managerDevice> managerDevice { get; set; }
    public virtual ICollection<logUserLogin> logUserLogin { get; set; }
    public virtual ICollection<logUserOperates> logUserOperates { get; set; }
    public virtual ICollection<clientApp> clientApp { get; set; }
    public virtual customerDevice customerDevice { get; set; }
}

If I wrote like this

 public class UserController : ApiController
 {
 public IEnumerable<user> Get()
    {
        List<user> users = new List<user>();
        return user;
    }
 }

When I visit "localhost:3700/api/user" in explorer,it works.

<ArrayOfuser xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/UpdateServer.Model"/>

If I wrote like this

    public IEnumerable<user> Get()
    {
        using (var mydb = new ModelContainer())
        {
            return mydb.userSet.AsEnumerable();
        }
    }

When I visit "localhost:3700/api/user" in explorer, I get error message:

103 (net::ERR_CONNECTION_ABORTED)

Follow advices, I should use ToList() instead of asEnumerable, I change code like this

using (var mydb = new ModelContainer())
        {
            var users = mydb.userSet.ToList();
            return users;
        }

but still same error comes in explorer

103 (net::ERR_CONNECTION_ABORTED)

and I add a interrupt at

return users;

find that users already get the data from DB, but not correctly return to the client.

I do more test, I add an user with permission name "Admin", if I use code below, there is no user found, and it response to client correctly.

var users = mydb.userSet.Where(p=>p.permission.name == "Administrator").ToList();
return users;

if I write like this

var users = mydb.userSet.Where(p=>p.permission.name == "Admin").ToList();
return users;

it failed to return to client, give error

103 (net::ERR_CONNECTION_ABORTED)

thanks, I use ToList() instead of AsEnumerable(), still not work. — T_T
– T_T, Commented Apr 11, 2013 at 1:42
Do you have any navigation property used, I yes you ave to eager load them ? — tschmit
– tschmit, Commented Apr 11, 2013 at 6:35
thanks,when I delete navigation property,it works. After some google on how to deal with it, I found that I should add DTO layers. Then new question comes. Many writers say that POCO is good than DTO, but I think DbContext is POCO, So I should use DTO+POCO? or I'm totally on the wrong way. Is there a way to only use DbcontextAPI? — T_T
– T_T, Commented Apr 11, 2013 at 11:04
imho the most important is to perfectly control the scope of your database context. DTO is one way, the school way, to achieve that control. Always imho, a POCO is a type/class allowing to instanciate an object. An object (instance of the POCO class) can be an entity related to the database context or a DTO not related (detached) from the database context. You have to drive the transition from entity to DTO, using or not the same type/class. — tschmit
– tschmit, Commented Apr 11, 2013 at 11:17

pingoo · Accepted Answer · 2013-04-10 15:48:33Z

2

EF uses lazy loading. This means EF wont actually make a call to the database until you attempt to use the results.

To get around this you can use ToList(). Unlike AsEnumerable ToList will instantly execute the query.

The other option is to not dispose the context. Although this might seem wrong you can actually leave db context alive and let the Garbage collector get it when your no longer using any of the returned user objects.

answered Apr 10, 2013 at 15:48

pingoo

2,06414 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

T_T Over a year ago

thanks, I use ToList() instead of AsEnumerable(), still not work.

Søren Boisen Over a year ago

Leaving the db context open until garbage collection is an excellent way to exhaust your connection pool.

BrainSlugs83 Over a year ago

Also, ToList completely negates the performance benefit of exposing the IQueryable in the first place...

Collectives™ on Stack Overflow

How to return IEnumerable DbSet in web api get?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related