CODEWITHSUNDEEP
c#entity-framework
pjaaar
pjaaar

Reputation: 71

Entity Framework executes unnecessary SQL queries while all the data is already fetched in memory?

var logs = myContext.Logs.Where(x => ...).ToList();
var objectIds = logs.Select(x => x.ObjectId).Distinct().ToArray();
var objects = myContext.MyObjects.Where(x => objectIds.Contains(x.ID)).ToList();

foreach (MyObject myObject in objects)
{
    myObject.Logs = logs.Where(x => x.ObjectId == myObject.ID).ToList();
}

When I look at the generated queries, I have:

SELECT * FROM LOGS WHERE ...

and 

SELECT * FROM MYOBJECTS WHERE ID IN (...)

That's what I want but I have an unnecessary query for each iteration after

SELECT * FROM LOGS WHERE OBJECTID = ...

I assumed from my first two queries that all data was loaded so why run new queries in database?

Upvotes: 1

Views: 75

Answers (2)

TomTom
TomTom

Reputation: 62093

How, without assuming it is the ONLY instance ever working on it, is EF supposed to know that ALL data is loaded into memory nad no additional data has been created in the database?

That is the core problem. EF is not making assumptions on that, which is why it is making another query. There is time in between.

Upvotes: 1

Kieran Devlin
Kieran Devlin

Reputation: 1433

When Entity Framework loads tracked objects, it adds them to the local repository which can be accessed via context.Table.Local. Running queries outside of the local repo will always translate to SQL and will be run on the database engine. This being said, I do think a combined query with joins is more appropriate in your situation.

Upvotes: 1

Related Questions