LINQ navigation properties in the MVC controller action?

I'm kinda stuggling with this being best practice or not..

I have a repository that returns an IQueryable lets sa开发者_Python百科y. Is this correct usage in the controller?

var whatever = ObjectRepository.GetWhatever(id);
var videoId = whatever.UsersInObject1InObjects2.First().Object.Video.ExternalVideoId;

Where in the 2nd line above ".Object" and ".Video" are references to tables that are related to "whatever" table.

Or should I be making another function in a different repository to get the ExternalVideoId?

The way I usually do this is this. I create a separate model class that encapsulates data that the controller need from the database. (I.e. as a rule I do not use ORM class for this). This allows me annotating the model class members with all sort of attributes I might need. In the repository code I query the ORM and then return the model class (or IQueryable/IEnumerbale of model) as the result.

I've heard an opinion that returning IQueryable from an ORM such as Entity Framework is not advised, because by doing this, you risk executing a query against your back end several times if you are not careful with your model in controller/view. This is because in EF IQueryable represent a query that is not executed yet. By returning IEnumerable instead of IQueryable you make sure that query execution is confined to your repository class.

I however find, that sometimes it's more convenient to return IQueryable. For example, for table paging/sorting scenarios I can apply page number, sort direction, page size, etc to the IQueryable before executing it. I feel that this logic belongs more to controller rather then to repository. Some may disagree.

What you're actually doing is using a collection to do a query. I think you need to have a better look at linq queries. You don't want to add custom queries to your repository. You just want to expose the queryable interface like so..

    public IQueryable<T> Get( IUser identity )
    {
        return Context.Set<T>();
    }

    public IQueryable<IBindable> GetItem( IUser identity )
    {
        return Context.Set<T>().Cast<IBindable>();
    }

then you can use linq to sql