I'm querying data from the database using Entity Framework Core using the following code:
CamicDbContext context = new CamicDbContext();
public override object Read(DataManagerRequest dm, string key = null)
{
IQueryable<Country> query = context.Set<Country>();
query = query.Where(x => x.IsDeleted == false);
query = query.Where(x => x.Code == "PT");
int count = query.Cast<Country>().Count();
return dm.RequiresCounts ? new DataResult() { Result = query, Count = count } : (object)query;
}
When the query variable is declared, it seems that it is getting all items in the table (image 1), which seems wrong as it is inefficient with larger entities; therefore, I want to initialise the variable without getting data, build the query and then execute the query only when it reaches return query so the server will only query and return the items matching the filters.
I've tried multiple solutions. This one - 
CodePudding user response：
When the query variable is declared, it seems that it is getting all items in the table (image 1),
It's not
I'm I missing something to use deferred execution?
You are
Here's a screenshot from a test app after I turn logging on. In the code of the test app I create a context, build a query, and run it.. A couple of times in fact..
x is the context. If I open the debugger and expand the Orders "table" in the debugger tooltip there's a message at the bottom saying that looking at the results will enumerate the enumerable. If I enumerate, then the SQL will be executed:
There aren't any clauses on this - we're enumerating Orders which represents the entire table, so the SQL run is effectively SELECT * FROM Orders. There isn't any data in my Orders test table, which is why we see "no results" after the enumeration is complete, but if there were 1000 orders the debugger tooltip would have 1000 items in
The next line of code runs a LINQ Where:
This hasn't actually executed any SQL, but it's supplied a clause that EF can translate to SQL, so if you take a look in the DebugView property of the IQueryable returned by the Where, you can see the SQL that EF will form (...WHERE Ref = '') when it's run.
Enumerating the queryable would actually run the SQL. Calling for a Results View in the debugger would enumerate, as would doing something like ToArray, ToList, ToDictionary (internally they all enumerate), or even a plain old foreach etc on your queryable. Whenever there's an enumeration, it triggers the running of the SQL.
If you use something like First, Count, Single then these trigger the execution of the SQL query also; they don't enumerate the entire set, they modify the SQL that is run but they do cause execution..
..which brings me to the point raised in the comments.
The code I've written enables EF to form a query with a WHERE clause, but then it asks the DB to execute it twice: once to get the Count (calling .Count causes a SELECT COUNT(*) ... WHERE ..) and then again to get the actual data items (calling ToList does a SELECT columns ... WHERE ...`):
Given that you have to retrieve e.g. 100 items during the enumeration it would make more sense to retrieve them all and then get the local count of them; if you've stashed them in a list then the list will be tracking the Count
Keep in mind that when you're working with EF's sets and you haven't done anything to enumerate them already, the queryable you;re passing around and calling operations on essentially represents an SQL (or a partly built one) and every time you do something to it that will deliver data, it'll run the SQL
CodePudding user response：
LINQ can execute in one of two following ways: immediate or deferred. Immediate execution means that the data source is read and the operation is performed at the point in the code where the query is declared. All the standard query operators that return a single, non-enumerable result execute immediately where as a Deferred execution basically means that the operation is not performed at the point in the code where the query is declared. The operation is performed only when the query variable is enumerated.
Expending and viewing the IQueryable when debugging will act as immediate Query Execution, hence bringing that query in memory. in a non debugging environment this won't happen and the query will act as deferred query execution, meaning that it will actually be executed once the Count() method will get called
More about classification of Standard Query Operators by Manner of Execution can be found here - https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/classification-of-standard-query-operators-by-manner-of-execution
CodePudding user response：
see https://weblogs.asp.net/zeeshanhirani/using-asqueryable-with-linq-to-objects-and-linq-to-sql
1.When you use AsQueryable operator then further transformations are allowed: filtering, sorting, or aggregating 2.The additional lambda statements are converted to Expression trees. I illustrate an expression filter using a lambda function. 3.If you use AsQueryable operator on a query that does not implement IQueryable and only implements IEnumerable< T >,than any transformations that you apply on the query would automatically fall back on IEnumerable specification. 4. IQueryable if deferred until a foreach is called and the expression tree is evaluated
Product[] products =
{
new Product {ProductId=1, Name="Kayak",Category="Watersports",Price=275m, Company=new Company{ CompanyName="Abc Corp",States=new String[]{"Ut","Id" } } },
new Product {ProductId=2, Name="Lifejacket", Category="Watersports",Price=48.95m, Company=new Company{ CompanyName="X Corp",States=new String[]{"Ca","Az" } }},
new Product {ProductId=3, Name="Soccer Ball", Category="Soccer",Price=19.50m, Company=new Company{ CompanyName="Y Corp",States=new String[]{"Tx","Wa" } }},
new Product {ProductId=4, Name="Corner Flag", Category="Soccer",Price=34.95m, Company=new Company{ CompanyName="Z Corp",States=new String[]{"Co","Wy" } }}
};
Transaction[] transactions =
{
new Transaction{ Name ="Bob Smith", ProductId=1, Cost=10.95M, Quantity=10},
new Transaction{ Name ="Dan Brown", ProductId=3, Cost=5.99M, Quantity=1}
};
IQueryable<Product> query = products.AsQueryable<Product>();
Expression<Func<Product, bool>> filter = x => x.Price > 100;
query = query.Where(filter);
int count = query.Cast<Product>().Count();
var result = query.Join(transactions,
product => new { product.ProductId },
transaction => new { transaction.ProductId },
(product, transaction) =>
new
{
CustomerName = transaction.Name,
ProductName = product.Name,
CompanyName = product.Company.CompanyName,
Price = product.Price
}) ;
foreach (var item in result)
{
_output.WriteLine($"Count: {count} Company: {item.CompanyName} Customer: {item.CustomerName} Product : {item.ProductName} Price : {String.Format("{0:C}", item.Price)}");
}