How to filter in Entity Framework Core without loading data on initialisation

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://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/classification-of-standard-query-operators-by-manner-of-execution

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