LINQ to SQL in C# – Cheat Sheet
Database context & database create
var db = new MyDataContext(@"server=.\SQLEXPRESS;database=my;integrated security=SSPI"); if (!db.DatabaseExists()) db.CreateDatabase();
Select one | Insert |
var only = db.Customers.SingleOrDefault(c => c.CustID == "CHIPS"); var first = db.Customers.FirstOrDefault(c => c.CustID == "CHIPS"); |
var customer = new Customer() { CustID = "CHIPS", CompanyName = "Mr. Chips" }; db.Customers.InsertOnSubmit(customer); db.SubmitChanges(); |
– – – – – – – – – – – – – – – – – – – – – – – – – – – – | – – – – – – – – – – – – – – – – – – – – – – – – – – – – |
Where, null, contains & type | Update |
var r = new string[] { "WA", "OR" }; var customers = from c in db.Customers where c is Customer && (c.Region==null || r.Contains(c.Region)) select c; |
customer.ContactName = "Adam"; customer.Location = null; db.SubmitChanges(); |
– – – – – – – – – – – – – – – – – – – – – – – – – – – – | – – – – – – – – – – – – – – – – – – – – – – – – – – – – |
Paging & order | Delete |
var page3 = (from c in db.Customers orderby c.ContactName, c.City descending select c).Skip(10).Take(5); |
db.Customers.DeleteOnSubmit(customer);
db.SubmitChanges(); |
– – – – – – – – – – – – – – – – – – – – – – – – – – – – | – – – – – – – – – – – – – – – – – – – – – – – – – – – – |
Join, shape & distinct | Class/table attributes |
var labels = (from c in db.Customers join o in db.Orders on c.CustID equals o.CustID select new { name = c.ContactName, address = o.ShipAddress }).Distinct(); |
[Table(Name="dbo.Customers")] [InheritanceMapping(Code="C", Type=typeof(Customer), IsDefault=true)] [InheritanceMapping(Code="T", Type=typeof(TopCustomer)] |
– – – – – – – – – – – – – – – – – – – – – – – – – – – – | – – – – – – – – – – – – – – – – – – – – – – – – – – – – |
Group, count, sum & projection | Property/column attributes |
var totals = from c in db.Customers group c by c.Country into g select new Summary { Country = g.Key, CustomerCount = g.Count(), OrdCount = g.Sum(a=> a.Orders.Count)}; |
[Column(Storage="_CustomerID", DbType="Int NOT NULL", Name="custid", IsPrimaryKey=true)][Column(Storage=”_Category”, DbType="Char(1)", IsDiscriminator=true)] [Column(Storage="_Ver", IsVersion=true AutoSync=AutoSync.Always, UpdateCheck=UpdateCheck.Never, DbType="rowversion NOT NULL", CanBeNull=false, IsDbGenerated=true)] |
– – – – – – – – – – – – – – – – – – – – – – – – – – – – | – – – – – – – – – – – – – – – – – – – – – – – – – – – – |
Composite & outer join | Association/relationship attributes |
var r = from c in db.Customers join o in db.Orders on new { c=c.CustID, r=c.Country } equals new { c=o.CustID, r=o.ShipCountry } into j from jo in j.DefaultIfEmpty() select new { c, jo }; |
[Association(Name="Country_Customer", Storage="_Country", ThisKey="CoID",IsForeignKey=true)] |
– – – – – – – – – – – – – – – – – – – – – – – – – – – – | – – – – – – – – – – – – – – – – – – – – – – – – – – – – |