LINQ
 to 
SQL 
in 
C# – Cheat Sheet

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)]
   – –  – – – – – – – – – – – – – – – – – – – – – – – – – –    – –  – – – – – – – – – – – – – – – – – – – – – – – – – –