1

I am using LinqKit to build a query using dynamically generated filters.

That query fetches a quite large object graph from the database to perform calculations and does some other modifications to that data. As long as I don't use Includes in my query, it doesn't take long to get the main entity I need from the database. But, unfortunately, I need many of it's related entities to perform the needed calculations.

Each time I am adding an Include, this translates into a new nested subquery in SQL. I have about 8 or 9 Inlcudes so this translates to a lot of nested subqueries. That queries takes forever to return the data.

I would like all these nested subqueries to disappear from the SQL, but it's kinda tough to shape the SQL just the way I want using LinqKit from the application side.

I thought that, for that specific case, it would be a good idea to write a stored procedure just the way I want the SQL to be, and call it from Entity Framework.

My problem is I don't know how to get the object graph on the application side using a stored procedure. I can make EF generate a ComplexType which will include all the data in a single "entity", but the code doing calculation awaits a specific entity object graph (the main entity and it's related entities) so that code has no idea what that ComplexType is. I really don't want to have to rewrite the calculation engine to use that ComplexType. I can map the result to a single Entity, but only that Entity will be returned. I want the related entities to be returned too.

Looks like I'm caught between a rock and a crazy place.

Can anybody suggest anything ?

1 Answers1

0

We had the similar problems too. We end up building SQL views, and query against the views instead. I also think it's idea from CQRS pattern, basically, you separate your read model from write model.

J.W.
  • 17,991
  • 7
  • 43
  • 76
  • Thanks for your input. I've thought about querying views too, but since my calculation engine needs that specific object graph, how could I map the queried view to what the engine expects as entity graph ? –  Jul 19 '13 at 13:14
  • Can it be computed in a back-end service? Sometimes, if there is computation involved, you can do it on a backend service and put it in a query table. – J.W. Jul 19 '13 at 13:56
  • Sadly, all the computation is made application-side. It's a LOT of code and it would be too much of a burden to rewrite it. That's why I would like to know of alternatives to fetch the needed object graph. –  Jul 19 '13 at 14:20