17

I want to define a database query using LINQ and my EntityFramework context but I don't want entities returned; I want a datareader!

How can I do this? This is for exporting rows to a CSV.

Cheers, Ian.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
Ian Warburton
  • 15,170
  • 23
  • 107
  • 189
  • Why not just get the objects and serialize those to CSV? – Roman May 08 '11 at 01:51
  • I don't know how many objects there will be so a datareader stops the server's memory filling up. – Ian Warburton May 08 '11 at 02:06
  • From looking around a bit, you may not be able to get to it easily, there is an [ExecuteDbDataReader](http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.executedbdatareader(v=VS.90).aspx) method, but it's protected so you probably shouldn't be trying to get a hold of it. If this isn't done very often and/or if the query isn't overly expensive, you could try paging the results of your query to ensure that you don't have too many objects in memory at a time. – Roman May 08 '11 at 02:29

2 Answers2

22

If you need this you are more probably doing something unexpected. Simple iteration through materialized result of the query should be what you need - that is ORM way. If you don't like it use SqlCommand directly.

DbContext API is simplified and because of that it doesn't contain many features available in ObjectContext API. Accessing data reader is one of them. You can try to convert DbContext to ObjectContext and use the more complex API:

ObjectContext objContext = ((IObjectContextAdapter)dbContext).ObjectContext;
using (var connection = objContext.Connection as EntityConnection)
{
    // Create Entity SQL command querying conceptual model hidden behind your code-first mapping
    EntityCommand command = connection.CreateCommand();
    command.CommandText = "SELECT VALUE entity FROM ContextName.DbSetName AS entity";
    connection.Open();
    using (EntityDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
    {
        ...
    }
}

But pure ADO.NET way is much easier and faster because the former example still uses mapping of query to SQL query:

using (var connection = new SqlConnection(Database.Connection.ConnectionString))
{
    SqlCommand command = connection.CreateCommand();
    command.CommandText = "SELECT * FROM DbSetName";
    connection.Open();
    using(SqlDataReader reader = command.ExecuteReader())
    {

    }
}
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 3
    "If you need this you are more probably doing something unexpected." I don't think there's anything particularly strange about requesting a large number of objects for export or to generate emails. The ORM could still materialize objects but stream them from the database in a pure data reader kind of way. LLBLGen allows one to get hold of a data reader. http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/SelfServicing/gencode_datareadersprojections.htm – Ian Warburton May 08 '11 at 17:21
6

This question is around EF 4, but for anyone else with EF 6 or higher you can use the AsStreaming() extension method.

http://msdn.microsoft.com/en-us/library/dn237204(v=vs.113).aspx

jhilden
  • 12,207
  • 5
  • 53
  • 76
  • For anyone who is like me and has no idea what AsStreaming() does, here is the only passing reference I was able to find for it: http://entityframework.codeplex.com/wikipage?title=Design%20Meeting%20Notes%20-%20December%206%2C%202012. There seems to be no other documentation anywhere. – Mike Jan 26 '14 at 22:55
  • 3
    This method is now obsolete and calling it has no effect because LINQ queries are streaming by default. I am guessing that is only for EF6, though. You should still use Ladislav's answer for EF4. – NightOwl888 Feb 15 '15 at 13:23