Is it better to use linq2sql to generate classes for stored procedures, or to call the stored procedures directly?

I see tons of questions on LINQ to SQL vs Stored Procs. I'm more curious about the benefits of using them in tandem as relates to object mapping.

I have my business objects defined, and I have stored procedures for all of my CRUD transactions.

Is it better to plop all the stored procs into a DBML file and call them from there, and then map the results to my business objects, or is it better to just use a DataReader 开发者_运维百科and map it from there?

It's annoying to me because I want my objects as I define them, rather than use MyStoredProcResult objects as linq2sql generates, so I feel I'm doing the same field by field mapping as I would with a data reader.

Performance isn't necessarily key here (unless it's ridiculously slow). I'm looking to create a standard way for all our developers to load data from a database into an object in the simplest fashion with the least amount of code.

---

Mapping to LINQ2SQL has a serious advantage in being type-safe - you don't really have to worry about parsing the results or adding command parameters. It does it all for you.

On the other hand with calling stored procedures directly with SQLcommand and DataReader proves to have better performance (especially when reading/changing a lot of data).

Regardless of which you choose it is better to build a separate Data Access Layer as it allows more flexibility. The logic of accessing/changing database should not be built into your business objects cos if you are forced to change means of storing you data it updating you software will be painful.

---

Not direct answer to your question, but if you want your objects as result of query, you probably have to consider code first schemas. Linq2SQL does not support this, but Entity Framework and NHibernate does.

Direct answer is that DataReader will obviously has less overhead, but at the same time it will have much more magic strings. Overhead is bad in terms of perfomance(in your case not that big). Magic strings are bad in terms maintaining code. So definetly this will be your personal choise.

---

LINQ2SQL can provide your objects populated with the results of the query. You will have to build child objects in such a way as to support either a List(Of T) or List depending on your language choice.

Suppose you have a table with an ID, a Company Name, and a Phone Number for fields. Querying that table would be straight-forward in either LINQ or a stored procedure. The advantage that LINQ brings is the ability to map the results to either anonymous types or your own classes. So a query of:

var doSomething = from sList in myTableRef select sList;

would return an anonymous type. However, if you also have a class like this:

public class Company
{
  public integer ID;
  public string Company;
  public string PhoneNumber;
}

changing your query to this will populate Company objects as it moves through the data:

List<Company> companies = (from sList in myTableRef select new Company 
                          { .ID = sList.id,
                            .Company = sList.company,
                            .PhoneNumber = sList.phonenumber }).ToList();

My C# syntax may not be 100% correct as I mainly code in VB, but it will be close enough to get you there.