Hi,

for some introduction see here

The next odd behavior is:

3. Query on same adapter in in-memory method execution throws exception

If I run a query inside of an in-memory executed method on the same adapter instance like the outer query (e.g. transaction is open) it throws an exception that the DataReader is already closed. The reason for this seems to be the the nested in-memory executed query closes the connection prematurely. It looks like that the adapter when fetching the projection does not the KeepConnectionOpen to true. If I do that manually (adapter.KeepConnectionOpen = true; ...code below ... adapter.CloseConnection(); ) the whole query works.

Here's the sample code:

bool CustomerHasOrders(CustomerEntity customer, IDataAccessAdapter adapter)
{
    var metaData = new LinqMetaData(adapter);
    return metaData.Customer
    .With(c => c.Orders)
    .First(c=> c.CustomerId == customer.CustomerId)
    .Orders
    .Any();
}

var customer = (from c in metaData.Customer
  select new
  {
    Customer = c,
    HasOrders = CustomerHasOrders(c, adapter)
  })
  .First();

The exception is this:

InvalidOperationException: Invalid Operation on a closed object
   at Oracle.ManagedDataAccess.Client.OracleDataReader.Read()
   at SD.Tools.OrmProfiler.Interceptor.ProfilerDbDataReader.Read()
   at SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.FetchProjectionFromReader(List`1 valueProjectors, IGeneralDataProjector projector, IDataReader dataSource, Int32 rowsToSkip, Int32 rowsToTake, Boolean clientSideLimitation, Boolean clientSideDistinctFiltering, Boolean clientSidePaging, Boolean performValueProjectionsOnRawRow, Boolean postProcessDBNullValues, Dictionary`2 typeConvertersToRun, IRetrievalQuery queryExecuted)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Boolean performValueProjectionsOnRawRow, Boolean postProcessDBNullValues, Dictionary`2 typeConvertersToRun)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>n__21(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass22_0.<FetchProjection>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.PerformExecute(Expression expression, Type resultType)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression)

A method call in a projection is compiled into the projection lambda and executed when the row of the outer query has been fetched. We have to look into why this happens; adapters check if the connection they need to be open is already open and will keep it open when they're done with it. You're fetching an entity in the projection which requires entity materialization so it might be a case of scheduling (where the outer query, which started the connection and thus will close it when it's done with the reader) closes it while it should have been kept open for the nested method. Be aware that the outer query has no idea what the nested method is about, the nested method is on its own in this.

What you want to do won't work in Linq: linq works with 1 expression tree that's evaluated when it's enumerated (or when e.g. a method like First() is called on it) however you start another one when the projection is executed which is the result of evaluating the outer expression tree. It only works in a multi-method system if your nested method returns an Expression<Func<T>> object so it's part of the outer expression tree (which isn't possible here).

Looking at this method, I think it's wiser to rewrite this to a scalar query that's nested; this is then converted to a SQL select in the projection and you don't need to fetch all orders of each customer, which can be super slow, to determine if there's an order at all.:

[Test]
public void HasOrdersWithSeparateMethodTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        adapter.KeepConnectionOpen = true;
        var metaData = new LinqMetaData(adapter);
        var customer = (from c in metaData.Customer
                        orderby c.CustomerId ascending 
                        select new
                               {
                                   Customer = c,
                                   HasOrders = c.Orders.Any()
                               })
            .First();
        Assert.IsTrue(customer.HasOrders);
        Assert.AreEqual("ALFKI", customer.Customer.CustomerId);
    }
}

SELECT TOP(@p4) [LPLA_1].[Address],
                [LPLA_1].[City],
                [LPLA_1].[CompanyName],
                [LPLA_1].[ContactName],
                [LPLA_1].[ContactTitle],
                [LPLA_1].[Country],
                [LPLA_1].[CustomerID] AS [CustomerId],
                [LPLA_1].[Fax],
                [LPLA_1].[Phone],
                [LPLA_1].[PostalCode],
                [LPLA_1].[Region],
                @p2                   AS [LPFA_4],
                CASE
                  WHEN EXISTS
                       (SELECT [LPLA_2].[ShipVia]
                        FROM   [Northwind].[dbo].[Orders] [LPLA_2]
                        WHERE  ([LPLA_1].[CustomerID] = [LPLA_2].[CustomerID])) THEN 1
                  ELSE 0
                END                   AS [HasOrders]
FROM   [Northwind].[dbo].[Customers] [LPLA_1]
ORDER  BY [LPLA_1].[CustomerID] ASC 

I'm totally with you on that and we already having methods which return Expression<Func<T>> in order to be translated correctly to SQL instead of being lazy loaded. Unfortunately we also currently still have a load of methods which are called in-memory and are way more complex than my example and are not easy to convert.

The main things to consider:

• prefetch paths are for fetching graphs, not for query filtering
• lazy loading is for fetching data on-demand, not for query filtering. (And, if I'm being honest: avoid lazy loading altogether)

If you want to filter a resultset based on another set or query, formulate that query without prefetch paths/lazy loading as that's the most inefficient way to do querying and likely will fail in Linq scenarios. In-memory queries are to be avoided at all cost. It might work on sets of data used in development but you never know what the size of the production database will become, resulting in slow software that could have been avoided.

For fetching related data for client-side purposes (e.g. data for screens), prefetch paths are useful. For all other situations, try to write the query without them, as they aren't meant for that.