Manipulate data with LINQ to SQL

Content:
1. Benefits of using LINQ to SQL
2. Datacontext
   - Create LINQ to SQL class from Visual Studio
   - Create LINQ to SQL class from command line
3. Query data
4. Update data
5. Insert data
6. Delete data

---

Understanding of LINQ basics is recommended before learning Telerik Open Access.

With Linq to SQL Visual Studio can generate data model for you, which means that when you compile your code, the compiler will check syntax of LINQ expressions and generate SQL query for you (below). This prevents you from making a typo or syntax error in SQL command like "select * from persons" that you would use with SqlCommand class and that you will find later at runtime, not at compile time. On the other hand you need to be careful, as SQL command generator may not generate optimal code that may code that may cause that your query will run longer. You can use SQL Profiler to see what exact query was generated. In case the underlying SQL query is not optimal, you can replace the LINQ generated definition.

Datacontext is set of classed that map SQL database to you DB access layer. You can create it either from Visual Studio or by command line by executing.

From Visual Studio 2008 or later
-New project (optional)
-change target platform to .NET 3.5
-add\new "LINQ to SQL file"
-View\Server explorer
-Add connection
-drag a table onto the designer surface

From Visual Studio command line:
sqlmetal /server:(local) /database:basics /code:basics.cs

In order to use DataContext to need to initialize it.

C#
// class model represening SQL DB tables 
DataClasses1DataContext db = new DataClasses1DataContext(); // name of the class in DataModel.designer.cs

The syntax is exactly the same as in LINQ to Objects, just the source class comes from database model.

C#
var people = from p in db.persons
              where p.department_id > 0 &&  p.height>1
              orderby p.lastName
              select new
              {
                  p.firstName,
                  p.lastName,
              };

C#
var person = (from p in db.persons select p).FirstOrDefault();  // select the record that you want to update
person.lastName = "Cernin";  // make the update in memory
db.SubmitChanges();  // propagate changes back to database

C#
person newP = new person();
newP.firstName = "John";
newP.height = 189;

db.persons.InsertOnSubmit(newP);  
db.SubmitChanges();

C#
var personsToDelete = db.persons.Where(inst => inst.lastName == "Third");  // select who should be deleted
db.persons.DeleteOnSubmit(personsToDelete.First());  // execute the delete in memory  
db.SubmitChanges();  // propagate changes back to database