Using Entity Framework, which method is more efficient?

Question

I have some code that changes a value of some data within my database while within a loop. I'm just wondering what is the most efficient way of filtering my data first? I'll give an example:-

With the class:-

public class myObj 
{
    int id {get;set;}
    string product {get; set;}
    string parent{get;set;}
    bool received {get;set;}
}

And the DbContext:-

public class myCont:DbContext
{
    public DbSet<myObj> myObjs {get;set;}
}

Is it better to do this:-

int[] list;
/* Populate list with a bunch of id numbers found in myOBjs */
myCont data = new myCont();
myObj ob = data.myObjs.Where(o => o.parent == "number1");
foreach(int i in list)
{
    ob.First(o => o.id == i && o.received != true).received = true;
}

Or:-

int[] list;
/* Populate list with a bunch of id numbers found in myOBjs */
myCont data = new myCont();
foreach(int i in list)
{
    data.myObjs.First(o => o.parent == "number1" && o.id == i && o.received != true).received = true;
}

Or is there no difference?

The sql query will be generated only when the code iterates through the IQueryable. EF defers execution until an object needs to be materialized. — Jone Polvora, Jun 12 '12 at 10:13
Small readability improvement: `ob.First(o => o.id == i && o.received != true).received = true;` — david.s, Jun 12 '12 at 10:22
There is no difference. `ob` in the first example is an `IQueryable` and the same query gets executed once per every iteration in both code snippets (so, `list.Count()` times). A possible performance improvement (namely only one single DB query and then iteration in memory) is in @Asif's answer (which has been downvoted and deleted now - for whatever strange reason). — Slauma, Jun 12 '12 at 11:06

score 2 · Accepted Answer · edited Jun 12 '12 at 12:07

Not sure how you get to compile your code example above.

In your myObj object, the received property is an int, yet you are evaluating it against a bool which should cause this line o.received != true to results in an error Cannot apply operator '!=' to operands of type 'int' and 'bool'.

To Check the SQL
Once the code compiles use SQL Profiler to see what SQL is generated.

That will show you the constructed SQLs

Benchmarking
The below is a very crude description of only one possible way you can benchmark your code execution.

Wrap your code into a method, for example:

public void TestingOperationOneWay()
{
    int[] list;
    /* Populate list with a bunch of id numbers found in myOBjs */
    myCont data = new myCont();
    myObj ob = data.myObjs.Where(o => o.parent == "number1");
    foreach(int i in list)
    {
        ob.First(o => o.id == i && o.received != true).received = true;
    }
}

And:

public void TestingOperationAnotherWay()
{
    int[] list;
    /* Populate list with a bunch of id numbers found in myOBjs */
    myCont data = new myCont();
    foreach(int i in list)
    {
        data.myObjs.First(o => o.parent == "number1" && o.id == i && o.received != true).received = true;
    }
}

Crate a method which iterates x amount of times over each method using the Stopwatch similar to this:

private static TimeSpan ExecuteOneWayTest(int iterations)
{
    var stopwatch = Stopwatch.StartNew();

    for (var i = 1; i < iterations; i++)
    {
        TestingOperationOneWay();
    }

    stopwatch.Stop();

    return stopwatch.Elapsed;
}

Evaluate the results similar to this:

static void RunTests()
{
    const int iterations = 100000000;

    var timespanRun1 = ExecuteOneWayTest(iterations);
    var timespanRun2 = ExecuteAnotherWayTest(iterations);

    // Evaluate Results....
}

Thanks for this, I wasn't aware of the stopwatch object. As for the code not compiling well the above code was not from my project, just a bit of example code to demonstrate what I am trying to do in my application, but thanks for that too. — John C, Jun 12 '12 at 15:53

score 0 · Answer 2 · edited May 23 '17 at 10:24

In the case of a choice between your two queries, I agree that they would both execute similarly, and benchmarking is an appropriate response. However, there are some things you can do to optimize. For example, you could use the method 'AsEnumerable' to force evaluation using the IEnumerable 'Where' vice the LINQ 'Where' clause (a difference of translating into SQL and executing against the data source or handling the where within the object hierarchy). Since you appear to be manipulating only properties (and not Entity Relationships), you could do this:

int[] list;
/* Populate list with a bunch of id numbers found in myOBjs */
myCont data = new myCont();
myObj ob = data.myObjs.Where(o => o.parent == "number1").AsEnumerable<myObj>();
foreach(int i in list)
{
    ob.First(o => o.id == i && o.received != true).received = true;
}

Doing so would avoid the penalty of hitting the database for each record (possibly avoiding network latency), but would increase your memory footprint. Here's an associated LINQ further explaining this idea. It really depends on where you can absorb the performance cost.

Using Entity Framework, which method is more efficient?

2 Answers2