Using Entity Framework 5 to return an object equivalent to a datareader

Question

I have a project in MVC3 ADO.NET using Visual Studio 2010 and C#. I have a requirement for adhoc reports which are held in a database table (two columns - the report title and the SQL script to be executed to return the report). The resultant report can then either be displayed in a table or downloaded as a CSV file.

I want to execute the SQL script using EF but I do not know in advance what the entity to be returned is, for example, how many fields will be returned from the query. The 'best' method seems to be using something like:

var QueryResults = dataContext.ExecuteStoreQuery<object>(SQLText, String.Empty);

but then I need to use some reflection to get the report structure which is just a set of strings. This feels like a round about way of doing things and I'm not sure how to get the field/column names from the returned results.

If I were to use an ADO.NET SqlDataReader then I would now exactly what to do, but that feels like a backward step.

Quick reports using an SQL script loaded at run time is a powerful feature I have used in the past but I would like to bring it into the EF era.

Is there a straightforward way of doing this?

I find DataTables (on top of SqlDataReader) are still a good way to handle dynamic shaped results. While this approach should not be the common case, when you need the results of something pivoted or a dynamic SQL that generates unifiable shapes, you just gotta kick off the pretty statically-typed columns. The "problem" with ExecuteStoreQuery here is that it still works with statically-typed columns/entity-sets. — user2246674
– user2246674, Commented Apr 30, 2013 at 16:31
Of course, one can get tricky geekswithblogs.net/DavidBarrett/archive/2012/02/06/… .. (uses QueryTypeFactory from the Dynamic Linq Library) — user2246674
– user2246674, Commented Apr 30, 2013 at 16:34
If you're going to have any passed parameters, please don't forget about "little Bobby Tables"... xkcd.com/327 — Dan Esparza
– Dan Esparza, Commented Apr 30, 2013 at 16:43
Thanks for the suggestion - I've gone with the answer below which, in retrospect, seems the obvious thing to do. — Peter Smith
– Peter Smith, Commented Apr 30, 2013 at 20:06

Kirill Bestemyanov · Accepted Answer · 2013-04-30 17:36:41Z

2

You should use SqlDataReader for your task. There will be no advantages in using EF in this task due to untyped result of your query.

answered Apr 30, 2013 at 17:36

Kirill Bestemyanov

12k2 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dean Over a year ago

I upvoted but I think there is one small advantage... less code.

Kirill Bestemyanov Over a year ago

For dynamic query result SqlDataReader approach will be shorter in code.

Peter Smith Over a year ago

Thanks, I had hoped there was an EF solution, but this worked perfectly.

Collectives™ on Stack Overflow

Using Entity Framework 5 to return an object equivalent to a datareader

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related