Improving efficiency with Entity Framework

Question

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?

Answer 1

What I would have done to return just a set of data would have been the following:

var customers = (from x in Repository.Customers where <boolean statement> &&/|| <boolean statement select new {variableName = x.Name , ...).Take(<integer amount for amount of records you need>);

so for instance:

var customers = (from x in _repository.Customers where x.ID == id select new {variableName = x.Name} ).take(1000);

then Iterate through the results to get the data: (remember, the linq statement returns an IQueryable)...

foreach (var data in customers)
{
   string doSomething = data.variableName; //to get data from your query.
}

hope this helps, not exactly the same methods, but I find this handy in my code

Answer 2

Probably it's because your Cusomters() method in your repository is doing a GetAll() kind of thing and fetching the entire list first. This prohibits LINQ and your SQL Server from creating smart queries.

I don't know if there's a good workaround for your repository, but if you would do something like:

using(var db = new RemoteServerContext())
{
  var custs = db.Customers.Where(...);
}

I think that will be a lot quicker. If your project is small enough, you can do without a repository. Sure, you'll lose an abstraction layer, but with small projects this may not be a big problem.

On the other hand, you could load all Customers in your repository once and use the resulting collection directly (instead of the method-call that fills the list). Beware of adding, removing and modifying Customers though.

Answer 3

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.