[Solved] Fetch Strategy and Stored Procedures

[Morgan McCollough]

I've been evaluating Telerik ORM and trying to decide whether we can use it instead of the Entity Framework in a Silverlight Business app I am in charge of. I have run into an issue involving lazy loading. I was somewhat disappointed to see that it could not be turned off. Having it turned on by default when using RIA and Silverlight doesn't seem like a very good idea, because you easily have the potential for huge object graphs to be automatically fetched from the database during the serialization operations.

Anyway, I was trying to understand how to work around this with fetch strategies, but I'm not sure exactly how to approach this with stored procedures. Basically, we have a significant number of stored procedures that already return all information needed from various tables using multiple result sets. We also have a number of other instances where all necessary values are loaded in a single domain service by making several separate stored procedure calls.

However, with Telerik ORM, I'm not sure how to prevent the context from automatically loading all related items even after the stored procedure has already brought back all the necessary information. Is there a way to apply a fetch plan to a stored procedure?

For example, I have one SPROC that brings back information from two related tables in two different result sets. If I retrieve the reader and then translate the 2 result sets to the appropriate entities, I should have all the values I need. But, when I run a trace, I see that the Telerik ORM is automatically fetching all the related items again. There are very often more than 1000 results from this query (and many others in the app), and this kind of lazy loading results in more than 1000 calls to the DB. Our DBA especially gets very leary about this.

Below is an example of how I'm calling the stored procedure using the GetReader method generated along with the Domain Method. Can you advise on how to do this differently or apply a fetch plan to this?

public IQueryable<Costing> GetCostings()
        {
            IEnumerable<Costing> costings;
            using (var reader = DataContext.GetReaderForusp_search_default2(null, 1, 65, 1, "joeuser", true, 1, 1, 26))
            {
                costings = DataContext.Translate<Costing>(reader).ToList();
                reader.NextResult();
                var refs = DataContext.Translate<Reference>(reader).ToList();
                reader.NextResult();
                var prods = DataContext.Translate<Product>(reader).ToList();
            }
            return costings.AsQueryable();
        }

[Morgan McCollough]

The other issue I thought about is how to prevent Telerik ORM from loading ALL related items automatically. There are plenty of scenarios in the Silverlight app that we have where we want to load a certain subset of related items. This is where turning off lazy loading and doing explicit eager loading comes in very handy.

[Damyan Bogoev]

Hi Morgan,

I am afraid that fetch strategies cannot be applied on stored procedures. You could improve the behavior in your application by using materialization to persistent types. In this case the current item from the result set will be materialized only on demand. Otherwise in case of materialization to non-persistent type all the items will be translated on calling the CLR method which executes to stored procedure.
I am sorry for the inconvenience caused.

Greetings,
Damyan Bogoev
the Telerik team

[Morgan McCollough]

The types referenced in the code sample I posted are all persistent types. So, isn't materialization what I'm already doing?

What seems to be happening is that only what I explicitly load as a result of the stored procedure comes into memory on the server, but once the result gets returned from the DomainService method and serialization to the Silverlight client begins, Telerik ORM seems to be trying to load all other related items. Is this possibly because the property references done as a result of the serialization process trigger the lazy load? In my situation this results in a pretty large object model being sent down to the client and the whole process taking about 3-4 times longer than it would if it only sent down the original results returned from the SPROC.

If I'm right about the serialization process triggering lazy load, that seems like a major flaw to me for using this in the context of RIA services business apps.

Any other ideas?

[Damyan Bogoev]

Hello Morgan McCollough,

You can disable transporting the associations’ information through the RIA Service by removing the AssociationAttribute from the referenced properties in your domain model. This will avoid loading the all the information for the related items.
Hope that helps. 

Greetings,
Damyan Bogoev
the Telerik team

[Morgan McCollough]

Unfortunately, that's not an option. If I remove the association attributes, those associations are no longer reflected down to the Silverlight client. In other words, the entity code generation for the Silverlight stuff will not create properties for associated entities unless both the include and the association attributes are present in the metadata on the server.

Our current code really heavily relies upon those associations existing on the client, so removing them would imply massive changes. If there is no other way around this, I'm afraid this is kind of a deal breaker. I would like the use Telerik ORM. The designer is definitely nicer, among other things.

Is it on the roadmap to apply fetch strategies to stored procedures or turn off lazy loading? It would seem to me that without that, you are kind of excluding yourselves from use on enterprise business Silverlight apps unless most people are just using LINQ...

[Damyan Bogoev]

Hello Morgan McCollough,

Firstly I want to apologize for the inconvenience caused.

I am afraid that we do not plan to implement support for fetch strategies with stored procedures.

Actually you could use a Linq query to read the data from database instead of using the stored procedure. You could apply the necessary fetch strategies and used them in the Linq query. This will resolve the performance problems you are facing on your side.

Hope that helps. If any other questions arise, do not hesitate to contact us back.


Regards,
Damyan Bogoev
the Telerik team