CODEWITHSUNDEEP
c#.netsqlentity-framework
Faraday
Faraday

Reputation: 2954

How to stop EntityFramework from getting all rows

I tried to create a generic CreateOrUpdate function for EntityFramework where my classes/tables always have an ID field. Since that data is sent to a webpage and posted back to the server I am totally disconnected from the context and have to use the standard (set.Any / set.FirstOrDefault) way of checking to see if my object already exists.

The method works perfectly in terms of doing its job and creating/updating as required. But what I have found is that the db.Set<T>().FirstOrDefault(whereFunction); brings back all data from the DB and then performs the FirstOrDefault in memory. I would much prefer this to happen in SQL, but no matter what I have tried I can't get it to work.

Do you have any suggestions on how I might make the FirstOrDefault function translate to proper SQL so that I don't retrieve too much from the DB?

Also, I have tried First, Any & Count, all of which get all of the rows from the DB.

public void CreateOrUpdateEntity<T>(T entity) where T : class
{
    using (var db = new ProjectContext())
    {
        Func<T, bool> whereFunction = m => m.As<dynamic>().ID == entity.As<dynamic>().ID;
        var firstValue = db.Set<T>().FirstOrDefault(whereFunction);

        if (firstValue == null)
        {
            db.Set<T>().Attach(entity);
            db.ChangeTracker.Entries<T>().First(e => e.Entity == entity).State = EntityState.Added;
        }
        else
        {
            db.ChangeTracker.Entries<T>().First(e => e.Entity == entity).State = EntityState.Modified;
        }
    }
}

Upvotes: 2

Views: 122

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

You should use expression instead of Func<T, bool> delegate. Usage of delegate invokes Enumerable.FirstOrDefault(Func<T,TResult>) method which is executed in memory (and requires all data loaded to client), instead of Queryable.FirstOrDefault(Expression<Func<T, TResult>>) which is translated into SQL and executed on server side.

NOTE: I have doubts about whether your delegate can be translated into SQL.

BTW: In order to get entity by id you can use method Find: 

var firstValue = db.Set<T>().Find(entity.As<dynamic>().ID);

Upvotes: 4

Related Questions