Because my company standardizes on SQL Server, adopting LINQ-to-SQL was not a big deal. I have to admit that I've been burned by Microsoft in the past, and almost passed on LINQ-to-SQL when the rumor mill was buzzing about them dropping the technology once Entity Framework 2.0 came out. I've been working with LINQ-to-SQL for about nine months. For more than a few years, I've been looking for a .NET data layer solution that would finally allow me to not have to write or manage another stored procedure in order to access my data. Based on my current project, I think I've found a winner.
LINQ-to-SQL abstracts your database schema into .NET objects. Using the LINQ-to-SQL query syntax, the internal engine converts your compiled queries into SQL statements. The few things you know so far are correct. I've never been a fan of ADO.NET...especially DataSets. The footprint is way too large and you have to deal with so much code just to get a result set. LINQ-to-SQL does take some getting used to, but once you've got it figured out, it just works. In my experience, there were a few hurdles I had to overcome in order to feel truly comfortable with the technology.
LINQ-to-SQL works best if your database schema is sound. Stick to identity primary keys wherever possible and limit the number of composite primary keys. LINQ-to-SQL can work with composites, but it's just easier in the long run if you stick to single-field ints. If you have foreign key relationships that you want to manage yourself...DON'T! Set them up and let them go...because...
You need to let LINQ-to-SQL manage your keys. That's primary AND foreign...it will figure them out. Traditional SQL application development expects the developer to assign keys to child records and to perform joins with them. LINQ-to-SQL is the absolute opposite. If you explicitly specify a join expression (as in pkValue = fkValue), you're doing something wrong. The LINQ engine uses the key values to construct the SQL statements behind the scenes. If you manually change the value, the LINQ engine won't know what it needs to do, so leave the keys alone.
Learn and use lambdas as soon as you can. The comprehension queries work just fine, and it makes for a very smooth transition into the technology. However, because of the strong syntax similarity between comprehension queries and the SQL language, you may have difficulty letting go of some bad habits that could delay the success of your transition. Lambda queries are shorter and allow for a little more flexibility in my opinion.
Don't split up your databases into small DataContexts. Open the Visual Studio model designer and drag ALL of your tables into a single DataContext. If you need to change the definition for a table structure down the line, make the table change in the database, delete the corresponding entity from the model, and simply drag the table back in from the Server Explorer. Your table structure and associations will be automatically updated, and you're ready to roll again.
LINQ-to-SQL doesn't directly support many-to-many relationships, but you can still make it work.
For example, to get a list of addresses for a single company:
// Structure
// Company
// =======
// Company_ID int not null identity (1, 1)
//
// Address
// =======
// Address_ID int not null identity (1, 1)
// Company_ID int (FK to Company)
// Comprehension
var result = from a in DataContext.Companies
from b in DataContext.Addresses
where (a.Company_ID == 7)
select b.Addresses;
// Lambda
var result = DataContext.Companies.Where(a => a.Company_ID == 7).Addresses;
To solve the many-to-many problem, you need to use the association table as the parent. Using the above example, let's add a Customer table and retrieve a list of customers for a particular company:
// Structure
// Customer
// ========
// Customer_ID int not null identity (1, 1)
//
// Customer-Company
// ================
// CustomerCompany_ID int not null identity (1, 1)
// Customer_ID int (FK to Customer)
// Company_ID int (FK to Company)
var customers = DataContext.CustomerCompanies
.Where(a => a.Company_ID == 7)
.Select(a.Customer);
Now, let's create a new customer and associate her with a brand new company:
Customer customer = new Customer();
Company company = new Company();
company.CustomerCompanies.Add(new CustomerCompany { Customer = customer });
DataContext.Companies.InsertOnSubmit(company);
DataContext.SubmitChanges();
I think I might have gone a little fast, so let me explain what just happened. I created a new Customer object (or entity) and a new Company entity. Because LINQ-to-SQL is managing the foreign key relationships, I can directly refer to the association entity set (CustomerCompanies) from the newly-created Company entity. I added a new CustomerCompany entity, where I set the Customer property to our new Customer object. Since I added the CustomerCompany entity to the new Company entity, it knows to update the foreign key reference between those two entities. Calling InsertOnSubmit() flags the Company entity for insertion, in addition to whatever other changes the LINQ-to-SQL engine discovers when it examines the object. Calling SubmitChanges() tell the engine to resolve the current ChangeSet, which includes:
- the Company entity we explicitly inserted
- the Customer entity that was implicitly inserted when we added it to the CustomerCompany entity, and
- the CustomerCompany entity we created to hold the new Customer entity.
In addition, the LINQ engine automatically assigned and resolved the primary and foreign keys (see #2 above).
Regarding the validation framework, I've had good experience with the Castle Validator library. It's worked very well with my current MVC project, and I plan to incorporate it in future projects.
I've always taken Microsoft technology with a grain of salt. For the most part, they make good stuff, but it's been a long time since I've seen something from them which I consider to be great technology. I think LINQ-to-SQL qualifies as great, based on the simple fact that I haven't had to write or maintain a stored procedure in over a year.