This is actually quite a complex question. Unfortunately, to answer it fully you would have to basically write it and test it, however - I strongly suggest not looking at any on-the-fly POCO generation until you have your answer! Basically, you should ignore that step for now.
The other essential question in performance is: how fast does it need to be? The absolute first thing I would do is the absolutely simplest thing that works, and measure that. And the simplest thing that works is: load it into a DataTable and serialize that datatable (using RemotingFormat = RemotingFormat.Binary;). In 10 lines of code that will give you a
line in the sand:
var dt = new DataTable();
dt.Load(yourDataReader);
//... any access tests
dt.RemotingFormat = SerializationFormat.Binary;
using (var file = File.Create(path))
{
var bf = new BinaryFormatter();
bf.Serialize(file, dt);
}
// ... also check deserialize, if that is perf-critical
Normally I wouldn't recommend either DataTable or BinaryFormatter, but... it doesn't seem far-fetched in this case.
Personally, I suspect you'll find that DataTable in binary-remoting-mode isn't actually terrible.
The next step is to see what else works without any huge effort. For example:
- loading a data-source into objects is a solved problem, with tools like dapper
- serializing a set of objects in a very efficient way is a solved problem, with tools like protobuf-net
So I would be tempted to create an illustrative class (purely to see if it is any better) along the lines of:
[DataContract]
public class Foo {
[DataMember(Order=1)] public int Id {get;set;}
[DataMember(Order=2)] public string Name {get;set;}
// ... more props
// IMPORTANT: make this representative - basically, the same data
// that you had in the data-table
// note also include any supporting info - any indexers and interface
// support that your core code needs
}
[DataContract]
public class FooWrapper { // just to help in the test
[DataMember(Order=1)] public List<Foo> Items {get;set;}
}
and do the same test (your main code would only use the indexer access, but let dapper use the .Query<Foo>(...) API for now):
var data = conn.Query<Foo>(...).ToList(); // dapper
//... any access tests, just using the indexer API
using (var file = File.Create(path))
{
var wrapper = new FooWrapper { Items = data };
Serializer.Serialize(file, wrapper); // protobuf-net
}
// note that you deserialize via Serializer.Deserialize<FooWrapper>(file)
The point of this is that this will give you some bounds on what is reasonable to expect in terms of what can be achieved. Feel free to use your own materializer/serializer in place of dapper/protobuf-net, but I humbly submit that these two have been heavily optimized for scenarios largely like this.
When you have a lower and upper bound, you have sensible data to answer the "is it worth it" question. Generating objects at run-time isn't massively hard, but it is more work than most people would need to do. You also want to be really careful to re-use the generated types as far as possible. Note that if you go that route, protobuf-net has a fully non-generic API, via Serializer.NonGeneric or RuntimeTypeModel.Default (all three options end up at the same core). Dapper doesn't, but I would be more than happy to add one (accepting a Type instance). In the interim, you could also use MakeGenericMethod / Invoke for that one step.
I realize I haven't directly answered "is it worth it", but that is deliberate: that cannot be answered without direct application to your scenario. Hopefully, I have instead provided some hints at how you can answer it for your scenario. I would be very interested in hearing your findings.
Only when you know that it is worth it (and with the above I would expect that to take about an hour's effort) would I go to the trouble of generating types. If you do, I recommend the use of Sigil - it will make your IL generation far less frustrating.
