Improving efficiency with Entity Framework
.net, c#, entity-framework
Solution
var customers = _repository.Customers().Where(c => c.Location == location).Where(...
If `Customers()` returns `IQueryable`, this statement alone won't actually be 'bringing back' anything at all - calling `Where` on an `IQueryable` gives you another `IQueryable`, and it's not until you do something that causes query execution (such as `ToList`, or `FirstOrDefault`) that anything will actually be executed and results returned.
If however this `Customers` method returns a collection of instantiated objects, then yes, since you are asking for all the objects you're getting them all.
I've never used either code-first or indeed even then repository pattern, so I don't know what to advise, other than staying in the realm of `IQueryable` for as long as possible, and only executing the query once you've applied all relevant filters.
Problem
I have been using the Entity Framework with the POCO First approach. I have pretty much followed the pattern described by Steve Sanderson in his book 'Pro ASP.NET MVC 3 Framework', using a DI container and DbContext class to connect to SQL Server. The underlying tables in SQL server contain very large datasets used by different applications. Because of this I have had to create views for the entities I need in my application: ``` class RemoteServerContext : DbContext { public DbSet<Customer> Customers { get; set; } public DbSet<Order> Orders { get; set; } public DbSet<Contact> Contacts { get; set; } ... protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Customer>().ToTable("vw_Customers"); modelBuilder.Entity<Order>().ToTable("vw_Orders"); ... } } ``` and this seems to work fine for most of my needs. The problem I have is that some of these views have a great deal of data in them so that when I call something like: ``` var customers = _repository.Customers().Where(c => c.Location == location).Where(...); ``` it appears to be bringing back the entire data set, which can take some time before the LINQ query reduces the set to those which I need. This seems very inefficient when the criteria is only applicable to a few records and I am getting the entire data set back from SQL server. I have tried to work around this by using stored procedures, such as ``` public IEnumerable<Customer> CustomersThatMatchACriteria(string criteria1, string criteria2, ...) //or an object passed in! { return Database.SqlQuery<Customer>("Exec pp_GetCustomersForCriteria @crit1 = {0}, @crit2 = {1}...", criteria1, criteria2,...); } ``` whilst this is much quicker, the problem here is that it doesn't return a DbSet and so I lose all of the connectivity between my objects, e.g. I can't reference any associated objects such as orders or contacts even if I include their IDs because the return type is a collection of 'Customers' rather than a DbSet of them. Does anyone have a better way of getting SQL server to do the querying so that I am not passing loads of unused data around?