Stream, TextReader & XmlReader properties

[bricelam]

ADO.NET supports steaming values from the database using Stream, TextReader, and XmlReader objects. We should consider allowing properties of these types in entity classes.

There are a few things to think through here:

• How is the connection lifetime managed?
  • What happens if I try and read after the connection is closed?
  • If we automatically opened the connection to query, will we have to leave it open?
• How will these properties work in the update pipeline?

Related to #4573 & #6234
Depends on dotnet/SqlClient#27

[Zoxive]

What about inserting streams?

I'm working on a project that currently uses MsSql, but I am currently working on adding sqlite support.
We often have pooled byte[] arrays, and we wrap them MemoryStreams() to send to MsSql, works great.

Sqlite says it doesnt know how to map a stream to a known managed provider native type.

System.InvalidOperationException: No mapping exists from object type System.IO.MemoryStream to a known managed provider native type.
   at Microsoft.Data.Sqlite.SqliteValueBinder.Bind()
   at Microsoft.Data.Sqlite.SqliteParameter.Bind(sqlite3_stmt stmt)
   at Microsoft.Data.Sqlite.SqliteParameterCollection.Bind(sqlite3_stmt stmt)
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()```

[bricelam]

@Zoxive I debated whether to support Stream in SqliteParameter and UDFs, but there is no native SQLite API for streaming in these cases, so we'd just end up buffering the entire steam in memory anyway...

[bricelam]

The most efficient option on SQLite is to insert values using SqliteBlob. See Blob I/O in Microsoft.Data.Sqlite for more details.

[roji]

I'm not sure how something like this would work - except in Sqlite :) In the usual case of networked databases, the point of the streaming APIs is to actually stream large values over the network, usually also using CommandBehavior.SequentialAccess. This means that the reader must be kept open and on the same row which the column is being streamed out, which isn't compatible with the way EF does materialization...

@bricelam do you have other ideas in mind, or where you thinking about this as a Sqlite-only feature?

[bricelam]

I agree managing the lifetime of the stream is the biggest design obstacle. You'd have to stay in sync with the underlying DbDataReader. The feature might be pretty limited.

var zipFile = new BricesImaginaryZipFile();

foreach (var file in context.Files.Where(f => selectedFiles.Contains(f.Id))) // Mustn't buffer!
{
    zipFile.Add(file.Name, file.Stream);
}

return zipFile;

[bricelam]

But even that's kind of a bad example since you're just reading the entire stream anyway. (might save some memory though) Maybe you're looking at signatures to make sure the file extension matches... lol, maybe I'm grasping at straws.

[ajcvickers]

Note from triage: complex and potentially a pit-of-failure due to lifetime/scoping issues discussed above.