LINQ to SQL Cheat Sheet - PDF

Document Sample
LINQ to SQL Cheat Sheet - PDF Powered By Docstoc
					LINQ
to
SQL
in
C#
Cheat
Sheet                                                                    http://damieng.com


                               Data 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       var customer = new Customer() {
  => c.CustID == "CHIPS");                        CustID = "CHIPS",
var first = db.Customers.FirstOrDefault(c         CompanyName = "Mr. Chips" };
  => c.CustID == "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          customer.ContactName = "Adam";
  where c is Customer &&                        customer.Location = null;
  (c.Region==null || r.Contains(c.Region))      db.SubmitChanges();
  select c;

                                                                   Delete
              Paging & order
                                                db.Customers.DeleteOnSubmit(customer);
var page3 = (from c in db.Customers             db.SubmitChanges();
  orderby c.ContactName, c.City descending
  select c).Skip(10).Take(5);
                                                            Class/table attributes
           Join, shape & distinct               [Table(Name="dbo.Customers")]
                                                [InheritanceMapping(Code="C",
var labels = (from c in db.Customers              Type=typeof(Customer), IsDefault=true)]
  join o in db.Orders                           [InheritanceMapping(Code="T",
     on c.CustID equals o.CustID                  Type=typeof(TopCustomer)]
  select new { name = c.ContactName,
               address = o.ShipAddress
  }).Distinct();                                         Property/column attributes
                                                [Column(Storage="_CustomerID",
      Group, count, sum & projection              DbType="Int NOT NULL", Name="custid",
                                                  IsPrimaryKey=true)]
var totals = from c in db.Customers
  group c by c.Country into g                   [Column(Storage="_Category",
  select new Summary { Country = g.Key,           DbType="Char(1)",
    CustomerCount = g.Count(),                    IsDiscriminator=true)]
    OrdCount = g.Sum(a=> a.Orders.Count)};
                                                [Column(Storage="_Ver", IsVersion=true
                                                  AutoSync=AutoSync.Always,
          Composite & outer join                  UpdateCheck=UpdateCheck.Never,
                                                  DbType="rowversion NOT NULL",
var r = from c in db.Customers                    CanBeNull=false, IsDbGenerated=true)]
  join o in db.Orders on
    new { c=c.CustID, r=c.Country } equals
    new { c=o.CustID, r=o.ShipCountry }              Association/relationship attributes
    into j
  from jo in j.DefaultIfEmpty()                 [Association(Name="Country_Customer",
  select new { c, jo };                           Storage="_Country", ThisKey="CoID",
                                                  IsForeignKey=true)]
LINQ
to
SQL
in
VB.Net
Cheat
Sheet                                                                   http://damieng.com


                               Data context & database create
Dim db = New MyDataContext("server=.\SQLEXPRESS;database=my;integrated security=SSPI")
If Not db.DatabaseExists Then db.CreateDatabase()


                Select one                                          Insert
Dim only = db.Customers.SingleOrDefault(        Dim customer = New Customer() With {
  Function(c) c.CustID = "CHIPS")                 .CustID = "CHIPS",
Dim first = db.Customers.FirstOrDefault(          .CompanyName = "Mr. Chips" }
  Function(c) c.CustID = "CHIPS")               db.Customers.InsertOnSubmit(customer)
                                                db.SubmitChanges()

        Where, null, contains & type
                                                                   Update
Dim r = New String() {"WA", "OR"}
Dim customers = From c In db.Customers _        customer.ContactName = "Adam"
  Where TypeOf c Is Customer AndAlso _          customer.Location = Nothing
    (c.Region Is Nothing OrElse _               db.SubmitChanges()
    r.Contains(c.Region)) Select c

                                                                   Delete
              Paging & order
                                                db.Customers.DeleteOnSubmit(customer)
Dim page3 = From c In db.Customers _            db.SubmitChanges()
  Order By c.ContactName, c.City _
  Descending Skip 10 Take 5 Select c
                                                            Class/table attributes
           Join, shape & distinct               <Table(Name:="dbo.Customers")> _
                                                <InheritanceMapping(Code:="C",Type:=_
Dim labels = From c In db.Customers _             GetType(Customer),IsDefault:=True)> _
  Join o in db.Orders _                         <InheritanceMapping(Code="T",
     On c.CustID Equals o.CustID _                Type:=GetType(TopCustomer)> _
  Select name = c.ContactName, _
      address = o.ShipAddress _
  Distinct                                               Property/column attributes
                                                <Column(Storage:="_CustomerID", _
      Group, count, sum & projection              DbType:="Int NOT NULL", Name:="custid",_
                                                  IsPrimaryKey:=True)> _
Dim totals = From c In db.Customers _
  Group c By c.Country Into g = Group _         <Column(Storage:="_Category", _
  Select New Summary With _                       DbType:="Char(1)", _
  {.Country = Country,.CustomerCount = _          IsDiscriminator:=True)> _
  g.Count(),OrdCount = g.Sum(Function(a) _
  a.Orders.Count)}                              <Column(Storage:="_Ver", IsVersion:=True _
                                                  AutoSync:=AutoSync.Always, _
          Composite & outer join                  UpdateCheck:=UpdateCheck.Never, _
                                                  DbType:="rowversion NOT NULL", _
Dim z = From c In db.Customers _                  CanBeNull:=False, IsDbGenerated:=True)>_
  Group Join o In db.Orders On _
    New With {.c=c.CustID,.r=c.Country} _
    Equals New With _                                Association/relationship attributes
    {.c =o.CustID,.r =o.ShipCountry} _
    Into j = Group _                            <Association(Name:="Country_Customer", _
    From jo In j.DefaultIfEmpty() _               Storage:="_Country", ThisKey:="CoID", _
    Select New With {c, jo}                       IsForeignKey:=True)> _