DataSets to POCOs - an inquiry regarding DAL architecture

I have to develop a fairly large ASP.NET MVC project very quickly and I would like to get some opinions on my DAL design to make sure nothing will come back to bite me since the BL is likely to get pretty complex. A bit of background: I am working with an Oracle backend so the built-in LINQ to SQL is out; I also need to use production-level libraries so the Oracle EF provider project is out; finally, I am unable to use any GPL or LGPL code (Apache, MS-PL, BSD are okay) so NHibernate/Castle Project are out. I would prefer - if at all possible -开发者_开发问答 to avoid dishing out money but I am more concerned about implementing the right solution. To summarize, there are my requirements:

1. Oracle backend
2. Rapid development

3. (L)GPL-free

4. Free

I'm reasonably happy with DataSets but I would benefit from using POCOs as an intermediary between DataSets and views. Who knows, maybe at some point another DAL solution will show up and I will get the time to switch it out (yeah, right). So, while I could use LINQ to convert my DataSets to IQueryable, I would like to have a generic solution so I don't have to write a custom query for each class.

I'm tinkering with reflection right now, but in the meantime I have two questions:

1. Are there any problems I overlooked with this solution?
2. Are there any other approaches you would recommend to convert DataSets to POCOs?

Thanks in advance.

There's no correct answer, though you'll find people who will try to give you one. Some things to keep in mind:

• Since you can't get the advantages of EF or Linq-to-SQL, don't worry about using the IQuerable interface; you won't be getting the main advantage of it. Of course, once you've got your pocos, LINQ to object will be a great way of dealing with them! Many of your repository methods will return IQueryable<yourType>.

• As long as you have a good repository to return your pocos, using reflection to fill them out is a good strategy, at first. If you have a well-encapsulated repository, I say again. You can always switch out the reflection-filled entity object code for more efficient code later, and nothing in you BL will know the difference. If you make yourself dependent on straight reflection (not optimized reflection like nHibernate), you might regret the inefficiency later.

• I would suggest looking into T4 templates. I generated entity classes (and all the code to populate them, and persist them) from T4 templates a few months ago, for the first time. I'm sold! My code in my T4 template is pretty horrible this first try, but it spits out some nice, consistent code.

• You will have to have a plan for your repository methods, and closely monitor all the methods your team creates. You can't have a general .GetOrders() method, because it will get all the customers every time, and then your LINQ to object will look nice, but will be covering some bad data access! Have methods like .GetOrderById(int OrderID) and .GetOrderByCustomer(int CustomerID). Make sure each method that returns entities uses an index at least in the DB. If the basic query returns some wasted records, that's fine, but it can't do table scans and return thousands of wasted records.

An example:

var Order = From O in rOrders.GetOrderByCustomer(CustID)    
            Where O.OrderDate > PromoBeginDate    
            Select O

In this example, all the Order for a customer would be retrieved, just to get some of the orders. But there won't be a huge amount of waste, and CustomerID should certainly be an indexed field on Orders. You have to decide whether this is acceptable, or whether to add a date distinction to your repository, either as a new method or with overloading other methods. There's no shortcut to this; you have walk the line between efficiency and maintaining your data abstraction. You don't want to have a method in your repository for every single data inquiry in your entire solution.

Some recent articles I've found where people are wrestling with how exactly to do this.:

• http://mikehadlow.blogspot.com/2009/01/should-my-repository-expose-iqueryable.html
• http://www.west-wind.com/WebLog/posts/160237.aspx

Devart dotConnect for Oracle supports the entity framework, you could then use LINQ to Entities.

Don't worry about using reflection to build DTOs from Datasets. They just work great.

An area of pain will be implementation of IComparer for each business object. Only load the data that is minimum requirement at the presentation layer. I burnt my fingers badly on in-memory sorting.

Also, plan in advanced for lazy-loading on DTOs.

We wrote our on Generic library to convert datatable/datarow into entitycollection/entityobjects. And they work pretty fast.