Some Simple LINQ To SQL Queries

by Dave Andrews

Given a table MyTable, with the rows ID int, Value1 varchar(100), Value2 varchar(100) and a data context (created automatically by visual studio) you can create/update/delete data very easily. I will be using LINQ To SQL here, which is somewhat out of date now with the introduction of the Entity Framework, but I will post up some EF examples in a later post (they’re not much different in the code.) The language is C#.

Simple L2S Examples

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
using (DBDataContext db = new DBDataContext())
{
        // get all
        var search = from t in dc.MyTable
                           select t;
 
        // this will return all the records
        List<MyTable> all = search.ToList();
 
        // get all (example 2) This one doesn't use a search variable.
        List<MyTable> all2 = dc.MyTable.ToList();
 
        // get single
        var search2 = from t in dc.MyTable
                              where t.ID == 1
                              select t;
 
        // if none is found, then record will be NULL.
        MyTable record = search2.SingleOrDefault();
 
        // get single (example 2) This one uses a predicate which defines
        // 's' as a placeholder, and then sets the conditions for s to be s.ID == 1.
        MyTable record2 = dc.MyTable.SingleOrDefault(s => s.ID == 1);
 
        // update a value in the record
        record2.Value1 = "Hello!";
        db.SubmitChanges();
 
        // delete the record
        db.MyTable.DeleteOnSubmit(record2);
        db.SubmitChanges();
}