LINQ to SQL Web Application Best Practices?

I'm afraid you've indeed seen overly simplified examples. LINQ to SQL (System.Data. Linq) is your DAL layer.

The classes L2S generates is your domain (but not to confuse with Domain-Driven Design ). On top of that you can still write your Business Layer I always try to prevent leaking the LINQ to SQL DataContext into the presentation layer (your web app). So it shouldn't be able to create or commit a DataContext Neither should you return IQueryable AsDTO(IQueryable customers) { return from customer in customers select new CustomerDTO() { CustomerId = customer.Id, Name = customer.

Name, City = customer.Address. City }; } } This DTO defines an internal AsDTO method, which is able to convert a collection of Customer domain objects to a collection of CustomerDTO DTOs. This makes conversion of domain objects to DTOs much easier.

Look for instance at this BL method: public static CustomerDTO GetCustomersByCountry(string country) { using (var db = ContextFactory.CreateContext()) { IQueryable customers = (from customer in db. Customers where customer.Address. Country == country orderby customer.

Name, customer. Id); return CustomerDTO. AsDTO(customers).ToArray(); } } The nice thing about this approach is that when you look at the SQL query, you'll see that only the customer Id, Name and the City of the Address table will be retrieved from the database.

This is because the AsDTO method translates one IQueryable to another, allowing LINQ to SQL to perform the total operation in the database I hope this gives some ideas of what you can do. Of course, this is my view on the subject and the things I've found practical in my situations.

I'm afraid you've indeed seen overly simplified examples. LINQ to SQL (System.Data. Linq) is your DAL layer.

The classes L2S generates is your domain (but not to confuse with Domain-Driven Design). On top of that you can still write your Business Layer. I always try to prevent leaking the LINQ to SQL DataContext into the presentation layer (your web app).

So it shouldn't be able to create or commit a DataContext. Neither should you return IQueryable objects to the presentation layer. IMO the business layer should have full control over the lifetime of the DataContext (unit of work) and the shape of the SQL queries.

However, there are several flavors. Some people tent to relax these constraints. Others even go much much further.It depends on your own taste and the size of the application.

The bigger the application, the more it is justified to add layers of abstraction. When disallowing IQueryables and other data related stuff from leaving the business layer, you'll end up having some interesting challenges. For instance, the presentation layer must instruct the business layer how to sort results.

While you could let the presentation layer sort the results itself, this would mean that you would have to get all data from the database and page at the presentation layer, what would lead to a very badly performing system. There are several solutions to this problem.In all cases you will need to inform the business layer how to sort the results for you. Solutions can be found here at SO when you search for LINQ dynamic sort.

I've written such a solution myself, here. Another challenge that disallowing IQueryables from leaving your BL will bring is that also domain objects can often not leave your BL. Most of your LINQ to SQL domain objects will contain lazy loaded properties (for instance, collections to other domain objects).

However, when the DataContext is in control of the Business Layer, it will be disposed, before you return results to the presentation layer. When the presentation than accesses a lazy loaded property, an exception will occur, because the DataContext has already been disposed. When you dispose the DataContext in your business layer, this behavior is of course 'by design'.

Allowing the presentation layer to get lazy loaded properties means the BL loses control over the queries that are sent to the database, thus losing control over performance.To resolve this issue, you should return Data Transfer Objects (DTO) from the BL to the presentation layer. A DTO will contain just data and no internal DataContext, and no lazy loaded properties. A DTO can be specially formatted for the actual request at hand.

DTOs of course lead to coding overhead themselves, so the size of your system and the performance needs must justify it. To make it easier for myself, I tend to put static projection methods on the a DTO. While this doesn't conform to the separation of concerns principle, I foudn it to be a very practical solution.

Look for instance at this CustomerDTO: public class CustomerDTO { public int CustomerId { get; set; } public string Name { get; set; } // City is flatterned from Address.City. Public string City { get; set; } internal static IQueryable AsDTO(IQueryable customers) { return from customer in customers select new CustomerDTO() { CustomerId = customer. Id, Name = customer.Name, City = customer.Address.

City }; } } This DTO defines an internal AsDTO method, which is able to convert a collection of Customer domain objects to a collection of CustomerDTO DTOs. This makes conversion of domain objects to DTOs much easier. Look for instance at this BL method: public static CustomerDTO GetCustomersByCountry(string country) { using (var db = ContextFactory.CreateContext()) { IQueryable customers = (from customer in db.

Customers where customer.Address. Country == country orderby customer.Name, customer. Id); return CustomerDTO.

AsDTO(customers).ToArray(); } } The nice thing about this approach is that when you look at the SQL query, you'll see that only the customer Id, Name and the City of the Address table will be retrieved from the database. This is because the AsDTO method translates one IQueryable to another, allowing LINQ to SQL to perform the total operation in the database. I hope this gives some ideas of what you can do.

Of course, this is my view on the subject and the things I've found practical in my situations.

Thanks for the thorough response. I cringe at doing data access in the presentation layer. I tend to do most of my sorting at the datalayer currently so that won't be a issue.

I haven't heard of DTO's before, sounds like something to look into further. – derek May 7 '10 at 22:37 DTOs are often considered to have a lot of overhead. They are especially useful when sending data across the wire (for instance, when having WCF or ASMX web services).

For instance, when you read Dino Esposito's "Microsoft . NET: Architecting Applications for the Enterprise", you'll notice that Dino thinks they normally give to much overhead when you're transferring objects between layers of the same AppDomain. While he’s right about this, I still found them to be useful in that particular scenario and I see the overhead getting smaller when technology improves... – Steven May 8 '10 at 16:11 For instance, new C# language constructs such as automatic properties make it easier to define DTOs and a refactoring tool such as Refactor!

Pro allows to automatically generate a DTO from an anonymous type definition inside a LINQ query. – Steven May 8 '10 at 16:12.

The LINQ to SQL is the DB access in the DAL implementation, if you want to separate between DAL and BLL. If you have less complex Web Application (and also never intend to switch DB backends), then you can get away without explicit DAL/BLL and do everything in the code behind. LINQ to SQL works great for readonly operations, but feels like a bit more work to implement write operations.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.