Geek asks and answers

Improving Efficiency with Entity Framework

I am using Entity Framework using the POCO First approach. I pretty much followed the pattern described by Steve Sanderson in his book "Pro ASP.NET MVC 3 Framework", using the DI container and the DbContext class to connect to SQL Server.

The base tables on the SQL server contain very large datasets used by various applications. Because of this, I had to create views for the objects that 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 it seems to work great for most of my needs.

The problem is that some of these views contain a lot of data in them, so when I call something like:

var customers = _repository.Customers().Where(c => c.Location == location).Where(...);

, , , LINQ , . , , SQL.

, ,

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,...);
}

, , DbSet, , . , , , "", DbSet.

- SQL- , ?

+5
4
var customers = _repository.Customers().Where(c => c.Location == location).Where(...

Customers() IQueryable, "" - - Where IQueryable IQueryable, (, ToList FirstOrDefault), - .

, , Customers , , , .

, , , , IQueryable , .

+4

, , :

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>);

, :

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

, : (, linq IQueryable)...

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

, , ,

+2

, , Cusomters() GetAll() . LINQ SQL Server .

, , - :

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

, . , . , , .

On the other hand, you can upload all Clients to your repository once and directly use the resulting collection (instead of calling the method that fills the list). Remember to add, delete and modify clients.

+1
source

You need a LINQ query to return less data, such as sql paging, for example, topin sql or to perform manual queries using stored procedures. In any case, you need to rewrite your query engine. This is one of the reasons why I did not use EF because you do not have much control over the code that seems.

0
source

More articles:


All Articles