Refering to other entities when using Linq2Sql and Sprocs

Question

We are evaluation Linq2Sql for internal applications, and our development guidelines mean that we must always use stored procedures for all CRUD operations, from various blogs i have got together an application that does much of what we want.

However, what i would like to do is when we have a relationship between two entities the relationship can be fulfilled automatically.

For example if i had an instance of a Person object I would like to be able to say

var absenceCount = persons.Absence.Count()

but because we do not have select permission on the Absence table we get a Sql exception.

is there anyway around this?

Answer 1

By using stored procedures for all of your database operations, you lose most of the benefits of an ORM like Linq-To-SQL and using LINQ queries. For example, by using stored procedures to retrieve all your data, you lose the ability to have strong type checking on all your queries.

You can of course still use L2S with stored procedures, but I don't really see the point in it if all your query operations must be done via stored procedures.

To answer your second question, how do you expect L2S to be able to populate the Absence table if you don't have permission to do a Select against the table? If L2S were somehow able to do this, what would be the point of the security you have established?

Randy

Answer 2

By sheer chance I came across a couple of blog posts which prove this is possible, I have tested it and it works a treat

basically having methods called Load calling into your stored proc added into your DataContext class will allow this to work

links with examples here

Damien Guard

Msdn