I can see at least two indirect drawbacks:

• It really ties the whole application to SQL Server. If one day in the future, you want to change the persistence layer (for say Oracle, PostgreSQL, MySQL, etc.), you'll have a lot of work to do.
• The integration of the two worlds: OOP vs Relational poses difficult challenges in terms of integration of OOP developers vs DBAs. To oversimplify it, OOP developers don't like SQL, and DBAs don't like C#. So you may have trouble finding the right people with the right Relational+OOP competence, or even the will to tackle it. I personally think it's unfortunate, but that's often the reality in the enterprise world. If you're the only one maintaining the app, that's not a problem.

Otherwise, I think it's a good design choice.

Note you can also use SQL 2008 Table-Valued parameters with user-defined table types for the same kind of result w/o using the CLR/SQL Server integration facilities.

PS: you could also use code generators for all this, this would cut down the work to do as well.