What should I know before moving from Datasets to Linq2Sql?

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 be 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.

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 be 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.

I almost forgot...you absolutely, positively, without question need to download a copy of LINQPad (linqpad.net). It is one of the best learning tools for LINQ available...and it's free. – Neil T.

Feb 7 '10 at 22:11.

For me, the ridiculous (and artificial) MSSQL tie-in was an immediate show stopper when starting with Linq2Sql. I opted for N.

Some not so exciting points (like the ones made by Neil): Linq2Sql has been discontinued in favor of EF But EF 3.5 is far behind Linq2Sql 3.5 Entity Framework 4 (the one that's looking very usable) depends on a 4.0 application pool If you like POCOs (and using 3.5) you will likely need a wrapper passing the DTOs content to your 'sanitized' version Nbernate has Linq2Nbernate quite mature in its current version.

Things that I bumped onto.. Linq2SQL objects are "**connected" to the datacontext. This can be very annoying.. esp. In multi-tier.It's very hard to fetch with one context and update with another.

Datasets are independent/disconnected from the adapter.. SQL server only For winforms, no good support for databinding (collectionchanges) This looks all very negative. However.. for web development. Linq2SQL is great..

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.