locked
IQueryable, IEnumerable and Lists in Linq RRS feed

  • Question

  • I'm experimenting with some queries to find out the best way to get performance gains.

    I know that using IQueryable is preferable to performing Linq to Sql or Linq to Entity database queries and that IEnumerable is best used for linq to Objects, Linq to xml, and in memory processing.

    I have a linq query as follows on my WCF service. When I try and modify the Controller method that calls this, I get the following design time compile error:

    Cannot implicitly convert type 'YeagerTechModel.DropDownLists.ProjectDescription[]' to 'System.Linq.IQueryable<YeagerTechModel.DropDownLists.ProjectDescription>'

    Note that the ProjectDescription object is defined as follows:

    using System;
    using System.Collections.Generic;
    using System.Runtime.Serialization;
    using System.ServiceModel;
    
    namespace YeagerTechModel.DropDownLists
    {
        [DataContract]
        [Serializable]
        public partial class ProjectDescription
        {
            [DataMember]
            public Int16 ProjectID { get; set; }
            [DataMember]
            public String Description { get; set; }
        }
    }

    Here is the code in the Controller method:

    public ActionResult ProjectsDescription()
            {
                IQueryable<ProjectDescription> projectDdl = db.GetProjectDropDownList();
                ViewBag.ProjectsCust = new SelectList(projectDdl, "ProjectId", "Name");
    
                return View("ProjectCustomer");
            }

    Here is the call in the DB method that sits on a WCF service:

    public IQueryable<ProjectDescription> GetProjectDropDownList()
            {
                try
                {
                    using (YeagerTechEntities DbContext = new YeagerTechEntities())
                    {
                        DbContext.Configuration.ProxyCreationEnabled = false;
                        DbContext.Database.Connection.Open();
    
                        IQueryable<ProjectDescription> project = DbContext.Projects.Where(w => w.Notes != null).Select(s =>
                            new ProjectDescription()
                            {
                                ProjectID = s.ProjectID,
                                Description = s.Description
                            }
                        );
                        return project;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

    Now, prior to this experimentation after reading up on the performance gains of IQueryable, etc, the original method to get the data from the database was as follows:

    public List<ProjectDescription> GetProjectDropDownList()
            {
                try
                {
                    using (YeagerTechEntities DbContext = new YeagerTechEntities())
                    {
                        DbContext.Configuration.ProxyCreationEnabled = false;
                        DbContext.Database.Connection.Open();
    
                        var project = DbContext.Projects.Where(w => w.Notes != null).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;
                }
            }

    The code in the Controller was as follows:

        IEnumerable<ProjectDescription> projectDdl = db.GetProjectDropDownList();

    First question is: Many queries use the var keyword to infer the type coming back. Which one to use when calling the DB to retrieve records? The "var" syntax or the "IQuerable" syntax"?

    The second thing I noticed is that on the Controller side, for a collection, it always expects a List object which is easily converted to IEnumerable. It can't accept an IQueryable type coming back.

    So, based on this premise, I gather that my optimum solution would be as follows: For the DB method call:

    public List<ProjectDescription> GetProjectDropDownList()
    		{
    			try
    			{
    				using (YeagerTechEntities DbContext = new YeagerTechEntities())
    				{
    					DbContext.Configuration.ProxyCreationEnabled = false;
    					DbContext.Database.Connection.Open();
    
    					IQueryable<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;
    			}
    		}

    For the code snippet in the Controller, it should be as follows and everything works fine:

    IEnumerable<ProjectDescription> projectDdl = db.GetProjectDropDownList();

    So, if IQueryable gives better performance (specifically on filtering and supports lazy loading), why not use the last DB method instead of the "var" keyword?

    I know my first method return a query.  The other two return the results of that query.

    When using IQueryable, the only way IQueryable will help you is if you are making a direct call to the database from the controller with the db method inside the controller to maintain scope which obviously breaks the rules of decoupling. So, if that's true, it's really impossible to use IQueryable in a decoupled fashion. Realistically, once you make a DB call from the controller to either a DLL or web service that contains the DB code, you only want to spin up a using statement right before the call and close it right after you're done (which "using" does) .

    In that scenario, you can pass whatever data you need down to the DB method and perform the filtering after the DB call, then return the result back. It's a matter of actually doing the filtering on the DLL or the web service, then returning the filtered results.

    Is it possible to use the below code snippet in the controller that calls the DB method in order to convert the results to a query and be able to filter on it? If that's the case, I'd be able to use my first implementation. This does not give me a design time compile error.

    IQueryable<ProjectDescription> projectDdl = db.GetProjectDropDownList().AsQueryable();
    Is also true that lazy loading only works with a query coming back defined as IQuerable and not IEnumerable or List?

    Can somebody help explain what should be the optimum scenario?

     

    


    Bill Yeager

    Saturday, October 19, 2013 10:00 PM

Answers

  • Hi Bill_Yeager,

    >>”how it could work in a decoupled environment.”

    Being confused about this.

    As we know that both List and IQuerable are inherited IEnumerable. We can see this as below:

    public interface IQueryable<out T> : IEnumerable<T>, IQueryable, IEnumerable
    public class List<T> : IList<T>, ICollection<T>, IList, ICollection, IReadOnlyList<T>, IReadOnlyCollection<T>, IEnumerable<T>, IEnumerable

    So it is easily for IQuerable or List converted to IEnumerable, however they cannot convert to each other.

    That is why “It can't accept an IQueryable type coming back.”

    And if we use the WCF Service, my idea is the same to yours, we should use List.

    If we use the edmx model in local project, for using IQuerable to accept the result, we need to define a global DbContext object like below:

    class TestForAddOD
    
        {
    
            private DataBaseFirstDBEntities db;
    
    
            internal void Execute()
    
            {
    
                db = new DataBaseFirstDBEntities();
    
                IQueryable<Order> projectDdl = GetOrderDropDownList(db);
    
    
                foreach (Order order in projectDdl)
    
                {
    
                    Console.WriteLine(order.OrderCode);
    
                    Console.WriteLine(order.OrderName);
    
                }
    
            }
    
    
            public IQueryable<Order> GetOrderDropDownList(DataBaseFirstDBEntities db)
    
            {
    
                try
    
                {
    
                    db.Configuration.ProxyCreationEnabled = false;
    
                    db.Database.Connection.Open();
    
    
                    IQueryable<Order> project = db.Orders.Where(w => w.OrderID != 3);
    
                    return project;
    
                }
    
                catch (Exception ex)
    
                {
    
                    throw ex;
    
                }
    
            }
    
        }

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred Bao Wednesday, October 23, 2013 5:52 AM Better
    • Marked as answer by Fred Bao Monday, October 28, 2013 8:33 AM
    Wednesday, October 23, 2013 3:23 AM

All replies

  • Hello,

    Welcome to this forum.

    >>First question is: Many queries use the var keyword to infer the type coming back. Which one to use when calling the DB to retrieve records? The "var" syntax or the "IQuerable" syntax"?

    As far as I know, when we do not know the returned type, we will use the "var" syntax, if we know the actual type, we should use the actual syntax.

    >> Is it possible to use the below code snippet in the controller that calls the DB method in order to convert the results to a query and be able to filter on it? If that's the case, I'd be able to use my first implementation. This does not give me a design time compile error.

    I think it is possible, when we use codes like below, the data will not be loaded to client, when we want to use and then the data will be loaded.

    IQueryable<ProjectDescription> projectDdl = db.GetProjectDropDownList().AsQueryable();

    >> Is also true that lazy loading only works with a query coming back defined as IQuerable and not IEnumerable or List?

    In my opinion, all of them can work with lazy loading.

    For lazy loading, there is a blog on MSDN regarding it:

    http://msdn.microsoft.com/en-US/data/jj574232

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 21, 2013 2:38 AM
  • Fred, after churning out this message from stackoverflow, I'm sticking with List unless someone can actually show me (IQueryable or IEnumerable) how it could work in a decoupled environment.

    Please see this link:

    http://stackoverflow.com/posts/19525306/revisions


    Bill Yeager

    Tuesday, October 22, 2013 6:19 PM
  • Hi Bill_Yeager,

    >>”how it could work in a decoupled environment.”

    Being confused about this.

    As we know that both List and IQuerable are inherited IEnumerable. We can see this as below:

    public interface IQueryable<out T> : IEnumerable<T>, IQueryable, IEnumerable
    public class List<T> : IList<T>, ICollection<T>, IList, ICollection, IReadOnlyList<T>, IReadOnlyCollection<T>, IEnumerable<T>, IEnumerable

    So it is easily for IQuerable or List converted to IEnumerable, however they cannot convert to each other.

    That is why “It can't accept an IQueryable type coming back.”

    And if we use the WCF Service, my idea is the same to yours, we should use List.

    If we use the edmx model in local project, for using IQuerable to accept the result, we need to define a global DbContext object like below:

    class TestForAddOD
    
        {
    
            private DataBaseFirstDBEntities db;
    
    
            internal void Execute()
    
            {
    
                db = new DataBaseFirstDBEntities();
    
                IQueryable<Order> projectDdl = GetOrderDropDownList(db);
    
    
                foreach (Order order in projectDdl)
    
                {
    
                    Console.WriteLine(order.OrderCode);
    
                    Console.WriteLine(order.OrderName);
    
                }
    
            }
    
    
            public IQueryable<Order> GetOrderDropDownList(DataBaseFirstDBEntities db)
    
            {
    
                try
    
                {
    
                    db.Configuration.ProxyCreationEnabled = false;
    
                    db.Database.Connection.Open();
    
    
                    IQueryable<Order> project = db.Orders.Where(w => w.OrderID != 3);
    
                    return project;
    
                }
    
                catch (Exception ex)
    
                {
    
                    throw ex;
    
                }
    
            }
    
        }

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred Bao Wednesday, October 23, 2013 5:52 AM Better
    • Marked as answer by Fred Bao Monday, October 28, 2013 8:33 AM
    Wednesday, October 23, 2013 3:23 AM
  • Fred, that is correct as long as it's in the same project, which, in a production environment is highly unlikely.

    Bill Yeager

    Wednesday, October 23, 2013 5:39 PM
  • >>in a production environment

    Is it a environment like using WCF Data Service?

    If it is, yes, I agree that we cannot do it like that and we should return a list set which would be better although it maybe lower the performance.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 24, 2013 6:49 AM