Cannot return type IQueryable or IEnumerable

Question

Since I cannot return either of these types from a Linq to Entities query, I am sticking with returning a List.

On return from the DB call (which is in a separate WCF service or DLL), the code in the Controller fails because the dbcontext connection is closed.

Note the code in the following. For both IEnumerable and IQueryable, the data does not come back because of the above description.

Method in MVC Controller

// Controller
IEnumerable<ProjectDescription> projectDdl = db.GetProjectDropDownList().AsEnumerable();
// Error coming back because dbcontext connection was closed.
ViewBag.ProjectsCust = new SelectList(projectDdl, "ProjectId", "Name");

Method in WCF service or DLL

// WCF Service or DLL 
public IEnumerable<ProjectDescription> GetProjectDropDownList()
{
    try
    {
        //IQueryable<ProjectDescription> project = null;

        using (YeagerTechEntities DbContext = new YeagerTechEntities())
        {
            DbContext.Configuration.ProxyCreationEnabled = false;
            DbContext.Database.Connection.Open();

            IEnumerable<ProjectDescription> project = DbContext.Projects.Select(s =>
                new ProjectDescription()
                {
                    ProjectID = s.ProjectID,
                    Description = s.Description
                }
            );


            return project;
        }


    }
    catch (Exception ex)
    {
        throw ex;
    }
}

I even tried the following establishing a DbContext instance before the DB call which still didn't work actually trying to pass down the DbContext to the DB method.

Controller:

DbContext = new YeagerTechEntities();
IEnumerable<ProjectDescription> projectDdl = db.GetProjectDropDownList(DbContext).AsEnumerable();
ViewBag.ProjectsCust = new SelectList(projectDdl, "ProjectId", "Name");

DbContext.Dispose();

DB method:

public IEnumerable<ProjectDescription> GetProjectDropDownList(YeagerTechEntities DbContext)
{
    try
    {
        //IQueryable<ProjectDescription> project = null;


            DbContext.Configuration.ProxyCreationEnabled = false;
            DbContext.Database.Connection.Open();

            IEnumerable<ProjectDescription> project = DbContext.Projects.Select(s =>
                new ProjectDescription()
                {
                    ProjectID = s.ProjectID,
                    Description = s.Description
                }
            );


            return project;
        }


    catch (Exception ex)
    {
        throw ex;
    }
}

The only thing that worked besides using List was to actually place the DB method inside the Controller, which obviously is not good practice whatsoever.

Here is the List convention that works fine:

Controller:

IEnumerable<ProjectDescription> projectDdl = db.GetProjectDropDownList();
                ViewBag.ProjectsCust = new SelectList(projectDdl, "ProjectId", "Name");

DB method:

public List<ProjectDescription> GetProjectDropDownList()
{
    try
    {
        using (YeagerTechEntities DbContext = new YeagerTechEntities())
        {
            DbContext.Configuration.ProxyCreationEnabled = false;
            DbContext.Database.Connection.Open();

            IEnumerable<ProjectDescription> project = DbContext.Projects.Select(s =>
                new ProjectDescription()
                {
                    ProjectID = s.ProjectID,
                    Description = s.Description
                }
            );

            List<ProjectDescription> myProjects = new List<ProjectDescription>();

            myProjects = project.ToList();

            return myProjects;
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

If anyone can explain to me what the correct model should be when using IQueryable or IEnumerable, that would be fine. However, after reading this link, it definitely seems like List is the way to go: IEnumerable vs IQueryable for Business Logic or DAL return Types

Answer 1

The reason why your code failed when using IEnumerable/IQueryable is because the EF query is deferred; that is to say it doesn't actually get executed against the database until you start to enumerate (foreach, etc) or "materialise" (ToList(), etc) the results.

• Therefore because you create an effectively temp DbContext with your using{}, the IEnumerable that you pass out of the function is a landmine - the underlying DbContext ceases to exist after the using block, and any attempt to enumerate or materialise the collection will fail.

• Even when you created the DBContext without a using block, you then went ahead and Dispose'd of it before you executed the query - hence getting the same error.

• This is one of the main reasons for not returning IQueryable from a DAL/Repository - because the recipient cannot know if the underlying context that the query relies on has been closed before they try to consume the results. IEnumerable is much better (List<T> implements IEnumerable<T> of course) and IMHO List is even better because it forces you to return a fully materialised collection and makes it clear to the caller that the result is not connected to the database any more.

• So the working version that you have is probably fine - create your context, execute the query and then immediately materialise it which executes the actual SQL and then disconnects the result set from the database. So that pattern will work fine for you.

• However I would recommend looking at having things like DbContext injected for you using an IoC container such as Ninject as it frees you from having to worry about the lifecycle of your DbContexts.

Hope that helps.

Answer 2

As you already knows, your DbContext enables you to query your database and so it has to be alive when SQL requests are sent to your SGBD.

The key point here is to know exactly how IQueryable works :

The IQueryable interface inherits the IEnumerable interface so that if it represents a query, the results of that query can be enumerated. Enumeration causes the expression tree associated with an IQueryable object to be executed. The definition of "executing an expression tree" is specific to a query provider. For example, it may involve translating the expression tree to an appropriate query language for the underlying data source.

Which means that as long as your Linq query has not been enumerated (with .ToList() or foreach), no query is sent to the database. The execution is deferred!

In your first attempt, you're :

• Calling the GetProjectDropDownList method
• Returning an IEnumerable (which is a Ling query) without "enumerating" it
• Disposing the DbContext
• Enumerating your Linq query through new SelectList when your view is generated but... Your DbContext has already been disposed at that time.

The same applies for your second attempt, the DbContext is disposed a little bit later but also already disposed when the view is generated.

In your last attempt, everything works fine because your DbContextis still alive when you're enumerating your Linq query (with project.ToList();)

In my opinion, isolating your DbContext calls and instances into a Data Access Layer and returning Lists or simple disconnected objects is not a bad practice at all.