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.
As you've seen in the previous examples, you call the ExecuteReader method of the Command object, which returns an instance of the DataReader.
As explained earlier, the SqlDataReader returns data via a sequential stream. To read this data, you must pull data from a table row-by-row Once a row has been read, the previous row is no longer available.
The way to make sure you close your datareaders (and database connections) is to always open them in a using block, like so: using (SqlDataReader rdr = MySqlCommandObject. ExecuteReader()) { while (rdr. Read()) { //... } } // The SqlDataReader is guaranteed to be closed here, even if an exception was thrown.
ExecuteReader to retrieve rows from a data source. The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when you're retrieving large amounts of data because the data is not cached in memory.
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())
{
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With