Any big benefits of Linq to SQL/Entities if database allows stored procedures only?

Question

Linq to SQL and Linq to Entities depend on creating dynamic SQL to do a lot of their work, specially when you have classes represent database tables in some fashion. However if the database(s) does not allow ad hoc SQL queries and everything has to go through stored procedures, I don't see the big value of using L2Q or L2E if the developer has to write all the SP's upfront to do all the work AND know that these are the all SPs that will ever be used in all scenarios in the app.

Views might alleviate the situation but if creating views need DBA permissions, it is still a hassle.

Answer 1

LINQ-to-SQL's stored procedure implementations will still return your entity objects; you don't get the super-incredible filtering capabilities that L2S provides by creating dynamic SQL, but you still can use the extensions to parse through.

Like I said, you can still add your schema and when your stored procs return those entities, you get that object relationship mapped already.

I still think it's handy :)

Answer 2

If you are restricted to stored procedures than your best choice is Linq-to-Sql it works fairly well if your goal is to map classes one to one with result sets from stored procedures.

There are some quirks with the designer for sure but once you get them figured out it works very well for mapping result sets to classes.

There is a big difference between using "stored procedures" for ORM and using a tool like NHibernate and mapping straight to your tables.

Mapping straight to tables gives your all the awesome stuff like lazy loading and the repository pattern.

here is my blog post on setting up linq-to-sql using stored procedures, hope it helps

Answer 3

If I were your dba I too would not allow access through any type of dynamic SQL. It is a bad practice to allow direct permissions to the tables, it makes it far too easy for employees to commit fraud or to steal sensistive data. LINQ2SQl also seems to write inefficent code (at least all the examples of the code it writes that I have seen are code I wouldn't allow in my database) and it is much much easier to performance tune stored procedures.

Be grateful you have a good dba who actually cares about database security and performance.