Linq lazy loading of entities with enumerable

Hi!

When having a Linq query such as

    var query = from o in UnitOfWork.Table<Order>()
               select o;

And then iterating over the result set as

    foreach (var order in query)
    {
        // Do something
    }

it seems as if LightSpeed loads the whole result set into entities before starting the enumeration.

My query is somewhat more complex than the one above, but what I want to achieve is to do filtering of the entities that cannot be expressed in Linq (or SQL for that matter) so it has to be done client side.

Without going into details why I want to do this, is it possible to make LightSpeed only load the Entities as I actually access them? Right now it's a huge waste of resources. I can't use Linq's Take or Skip methods. Using my client side filter, I may only use the first N entities and then skip the rest. Still it seems as if LightSpeed loads the full result set into memory (which may be very large).

I'm using v 5.0.

Thanks a lot

Björn

Are you using UnitOfWork.Query or do you have a custom extension method named Table?

We do only trigger a load when enumeration begins as that is when LINQ will ask for the data and call into LightSpeed to execute the query. If you are doing something in a custom extension method which forces enumeration earlier (e.g. calling .ToList()) then you will want to either use our .Query extension method or avoid the call in your extension method.

Jeremy

Hi!

Sorry, I forgot to remove our Table extension method in the example. It's just a restriction to what kind of classes the underlying query will take.

Replacing Table with Query makes no difference.

I'm sure there is something trivial I'm doing wrong, but I can't see it. So again, the Linq query:

        var query = from o in UnitOfWork.Query<Order>()
                   select o;

even yields an out of memory exception if the database table is large enough simply by enumerating the results in a foreach loop (or actually even before the enumeration in the foreach loop begins).

Any ideas where I should start looking?

Björn

Let's rephrase this and get to what I'm really after.

My hope was that I could enumerate the result using a Linq query, and the entities would be materialized as I stepped through the enumeration, not before.

I guess this isn't possible since this is simply not how things work. So can I achieve this in another way?

What I did to get around this was to use the Linq query as is but only projecting the Id's of the order entity, and then load the entities using FindOne as I needed them. This made things a LOT faster, but it's hardly a very efficient way of doing it.

So, can this be done in a more efficient way using the ORM?

Thanks! Björn

No not really, if you want to load each entities 1 by 1 based on a set then fetching the set as a list of ID's first and then fetching the instances 1 by 1 is the way you will need to achieve this. If you can batch the entity fetching in some way you should look at that as that would reduce the database call count.

Jeremy

OK.

If we put Linq aside for a moment, is there any chance of you implementing a feature where objects could be loaded using a datareader? You already support findBySql, which is similar in that it circumvents the usual ORM data access.

Björn

Yes, we can surface something like this as we do this internally for hydrating the objects. I will have a look at where we can make this available and look at getting this made available in the nightlies next week.

Jeremy

Great!

Then I have only two issues left. Our subscription just ended so we need to renew that to get access to the nightlies again.

But the second is maybe more problematic: We build our Linq request partly dynamic (we essentially give some super-users via an API the possibility to provide additions to the "where" statement). We wouldn't want to do this in plain SQL due to the danger of SQL injections, but by using Microsoft's DynamicQuery library together with Linq we should be safe from that.

So if we need an SQL-statement to make this work, we can still have use for it for some cases, but not the most important ones. Since the Linq is dynamic, we can't extract the generated SQL from logs since this is "after the fact". We would somehow need to get the SQL the Linq is generating without executing it.

Is there a way to do this? This is obviously only needed if your solution can't use Linq directly.

Thanks, I really appreciate your help!

Björn

No this is not possible. The SQL is only generated in response to enumeration or a specific single object call. This is when the deferred query actually executes and then calls LightSpeed as the underlying provider for the data source being operated on.

Jeremy