List vs IEnumerable vs IQueryable when defining Navigation property

Question

I want to create a new model object named Movie_Type in my ASP.NET MVC web application. What will be the differences if I define the navigation proprty of this class to be either List, ICollection or IQueryable as following?

public partial class Movie_Type
{ 
    public int Id { get; set; }
    public string Name { get; set; } 
    public string Description { get; set; } 
    public List<Movie> Movies { get; set; }
}

OR

public partial class Movie_Type
{ 
    public int Id { get; set; }
    public string Name { get; set; } 
    public string Description { get; set; } 
    public IQueryable<Movie> Movies { get; set; }
}

OR

public partial class Movie_Type
{
    public int Id { get; set; }
    public string Name { get; set; } 
    public string Description { get; set; } 
    public ICollection<Movie> Movies { get; set; }
}

Edit:- @Tomas Petricek. thanks for your reply. in my case i am using the database first approach and then i use DbContext template to map my tables, which automatically created ICollection for all the navigation properties, So my questions are:- 1. Does this mean that it is not always the best choice to use Icollection. And i should change the automatically generated classes to best fit my case. 2. Secondly i can manage to choose between lazy or Eager loading by defining .include such as

var courses = db.Courses.Include(c => c.Department);

Regardless of what i am using to define the navigation properties. So i can not understand ur point. 3. i did not ever find any examples or tutorials that use IQuerable to define the navigation properties ,, so what might be the reason? BR

Answer 1

You cannot use a navigation property of type IQueryable<T>. You must use ICollection<T> or some collection type which implements ICollection<T> - like List<T>. (IQueryable<T> does not implement ICollection<T>.)

The navigation property is simply an object or a collection of objects in memory or it is null or the collection is empty.

It is never loaded from the database when you load the parent object which contains the navigation property from the database.

You either have to explicitely say that you want to load the navigation property together with the parent which is eager loading:

var movieTypes = context.Movie_Types.Include(m => m.Movies).ToList();
// no option to filter or sort the movies collection here.
// It will always load the full collection into memory

Or it will be loaded by lazy loading (which is enabled by default if your navigation property is virtual):

var movieTypes = context.Movie_Types.ToList();
foreach (var mt in movieTypes)
{
    // one new database query as soon as you access properties of mt.Movies
    foreach (var m in mt.Movies)
    {
        Console.WriteLine(m.Title);
    }
}

The last option is explicit loading which comes closest to your intention I guess:

var movieTypes = context.Movie_Types.ToList();
foreach (var mt in movieTypes)
{
    IQueryable<Movie> mq = context.Entry(mt).Collection(m => m.Movies).Query();
    // You can use this IQueryable now to apply more filters
    // to the collection or sorting, for example:
    mq.Where(m => m.Title.StartWith("A"))   // filter by title
      .OrderBy(m => m.PublishDate)          // sort by date
      .Take(10)                             // take only the first ten of result
      .Load();                              // populate now the nav. property
    // again this was a database query

    foreach (var m in mt.Movies)    // contains only the filtered movies now
    {
        Console.WriteLine(m.Title);
    }
}

Answer 2

There are two possible ways of looking at things:

1. Is the result stored in memory as part of the object instance?
   If you choose ICollection, the result will be stored in memory - this may not be a good idea if the data set is very large or if you don't always need to get the data. On the other hand, when you store the data in memory, you will be able to modify the data set from your program.

2. Can you refine the query that gets sent to the SQL server?
   This means that you would be able to use LINQ over the returned property and the additional LINQ operators would be translated to SQL - if you don't choose this option, additional LINQ processing will run in memory.

If you want to store data in memory, then you can use ICollection. If you want to be able to refine the query, then you need to use IQueryable. Here is a summary table:

|                 | Refine query | Don't change query |
|-----------------|--------------|--------------------|
|  In-memory      |    N/A       |    ICollection     |
|  Lazy execution | IQueryable   |    IEnumerable     |