Barefoot learning LINQ (017): cross-link query (C #): manual encoding

Http://u.115.com/file/f2e338988d demo

This walkthrough shows how to use the join clause of LINQ to SQL to represent the foreign key relationship in the database.

This demo is based on the previous demo. Barefoot learning LINQ (016): [Drill] to create a simple object model and LINQ query (C #). You can use this link to go to the previous demo.

Cross-Table ing
After the definition of the customer class, create an order entity class definition that contains the following code. These codes indicate that order. Customer is used as a foreign key and is related to customer. mermerid.
Type or paste the following code after the customer class:

[Table(Name = "Orders")]   public class Order   {       private int _OrderID = 0;       private string _CustomerID;       private EntityRef <Customer> _Customer;         public Order() { this._Customer = new EntityRef<Customer>(); }         [Column(Storage = "_OrderID", DbType = "Int NOT NULL IDENTITY",           IsPrimaryKey = true, IsDbGenerated = true)]       public int OrderID       {           get { return this._OrderID; }           // No need to specify a setter because IsDBGenerated is           // true.       }         [Column(Storage = "_CustomerID", DbType = "NChar(5)")]       public string CustomerID       {           get { return this._CustomerID; }           set { this._CustomerID = value; }       }         [Association (Storage = "_Customer", ThisKey  = "CustomerID")]       public Customer Customer       {           get { return this._Customer.Entity; }           set { this._Customer.Entity = value; }       }   }  [Table(Name = "Orders")]public class Order{    private int _OrderID = 0;    private string _CustomerID;    private EntityRef <Customer> _Customer;    public Order() { this._Customer = new EntityRef<Customer>(); }    [Column(Storage = "_OrderID", DbType = "Int NOT NULL IDENTITY",        IsPrimaryKey = true, IsDbGenerated = true)]    public int OrderID    {        get { return this._OrderID; }        // No need to specify a setter because IsDBGenerated is        // true.    }    [Column(Storage = "_CustomerID", DbType = "NChar(5)")]    public string CustomerID    {        get { return this._CustomerID; }        set { this._CustomerID = value; }    }    [Association (Storage = "_Customer", ThisKey  = "CustomerID")]    public Customer Customer    {        get { return this._Customer.Entity; }        set { this._Customer.Entity = value; }    }}

Annotate the customer class
In this step, you need to annotate the customer class to indicate its relationship with the order class. (This annotation addition operation is not absolutely necessary, because defining the upward relationship between either party is sufficient to meet the need to create a link. However, adding this annotation makes it easy for you to locate the object on either side .)
Type or paste the following code into the customer class:

private EntitySet <Order> _Orders;   public Customer()   {       this._Orders = new EntitySet<Order>();   }     [Association(Storage = "_Orders", OtherKey  = "CustomerID")]   public EntitySet<Order> Orders   {       get { return this._Orders; }       set { this._Orders.Assign(value); }   }  private EntitySet <Order> _Orders;public Customer(){    this._Orders = new EntitySet<Order>();}[Association(Storage = "_Orders", OtherKey  = "CustomerID")]public EntitySet<Order> Orders{    get { return this._Orders; }    set { this._Orders.Assign(value); }}

Create and run a query across the customer-order relationship
Now you can access the order object directly from the customer object or vice versa. You do not need to have an explicit connection between the customer and the order.
Use the customer object to access the order object
1. Modify the method by typing or pasting the following code into the main method:

// Query for customers who have placed orders.   var CustomersHasOrders =       from CustomerObject in Customers       where CustomerObject.Orders.Any()       select CustomerObject;     foreach (var CustomerObject in CustomersHasOrders)   {       Console.WriteLine("ID={0}, Qty={1}",           CustomerObject.CustomerID,           CustomerObject.Orders.Count);   }  // Query for customers who have placed orders.var CustomersHasOrders =    from CustomerObject in Customers    where CustomerObject.Orders.Any()    select CustomerObject;foreach (var CustomerObject in CustomersHasOrders){    Console.WriteLine("ID={0}, Qty={1}",        CustomerObject.CustomerID,        CustomerObject.Orders.Count);}

 

2. Press F5 to debug the application.
Description
You can comment out dB. log = console. Out; to eliminate the SQL code in the console window.
3. Press enter in the console window to stop debugging.

Create a strongly typed view of the database
It is much easier to start with the strongly typed view of the database. By strongly classifying datacontext objects, you do not need to call gettable. When you use a strongly typed datacontext object, you can use strongly typed tables in all queries.
In the following steps, you create a MERs table as a strongly typed table mapped to the customers table in the database.
Strongly typed datacontext objects
1. Add the following code to the top of the customer class declaration.

public class Northwind : DataContext   {       // Table<T> abstracts database details per table/data type.       public Table<Customer> Customers;       public Table<Order> Orders;         public Northwind(string connection) : base(connection) { }   }  public class Northwind : DataContext{    // Table<T> abstracts database details per table/data type.    public Table<Customer> Customers;    public Table<Order> Orders;    public Northwind(string connection) : base(connection) { }}

 

2. Change the main method to a strongly typed datacontext, as shown below:

// Use a connection string.   Northwind db = new Northwind(@"C:\linqtest5\Northwind.mdf");     // Query for customers from Seattle.    var SeattleCustomers =       from CustomerObject in db.Customers       where CustomerObject.City == "Seattle"      select CustomerObject;     foreach (var CustomerObject in SeattleCustomers)   {       Console.WriteLine("ID={0}", CustomerObject.CustomerID);   }     // Freeze the console window.   Console.ReadLine();  // Use a connection string.Northwind db = new Northwind(@"C:\linqtest5\Northwind.mdf");// Query for customers from Seattle. var SeattleCustomers =    from CustomerObject in db.Customers    where CustomerObject.City == "Seattle"    select CustomerObject;foreach (var CustomerObject in SeattleCustomers){    Console.WriteLine("ID={0}", CustomerObject.CustomerID);}// Freeze the console window.Console.ReadLine();

 

The complete DEMO code is as follows:

Program. CS

Using system; using system. collections. generic; using system. LINQ; using system. text; using system. data. LINQ; using system. data. LINQ. mapping; namespace demo02 {[Table (name = "MERs")] public class customer {private string _ customerid; [column (isprimarykey = true, storage = "_ customerid")] public String customerid {get {return this. _ customerid;} set {This. _ customerid = value ;}} private s Tring _ city; [column (storage = "_ City")] Public String city {get {return this. _ city;} set {This. _ city = value ;}} private entityset <order> _ orders; public customer () {This. _ orders = new entityset <order> ();} [Association (storage = "_ orders", thiskey = "mermerid", otherkey = "customerid")] public entityset <order> orders {get {return this. _ orders;} set {This. _ orders. assign (value );}}} [Table (name = "orders")] public class order {private int _ orderid = 0; private string _ mermerid; private entityref <customer> _ customer; public order () {This. _ customer = new entityref <customer> ();} [column (storage = "_ orderid", dbtype = "int not null identity", isprimarykey = true, isdbgenerated = true)] public int orderid {get {return this. _ orderid;} // no need to specify a setter because Isdbgenerated is // true .} [column (storage = "_ customerid", dbtype = "nchar (5)")] Public String customerid {get {return this. _ customerid;} set {This. _ customerid = value ;}} [Association (storage = "_ customer", thiskey = "customerid", otherkey = "customerid")] public customer {get {return this. _ customer. entity;} set {This. _ customer. entity = value ;}} class program {static void Main (string [] ARGs) {//************************************* * *********** // cross-link query. //************************************** * ********** // Use a connection string. datacontext DB = new datacontext (@ "C: \ linqtest5 \ northwind. MDF "); // get a typed table to run queries. table <customer> MERs = dB. gettable <customer> (); // attach the log to show generated SQL. // dB. log = console. out; // query for MERs mers who have placed orders. vaR customershasorders = from customerobject in cust Omers where customerobject. orders. any () Select customerobject; foreach (VAR customerobject in customershasorders) {console. writeline ("ID = {0}, qty = {1}", customerobject. customerid, customerobject. orders. count); // console. writeline (customerobject. orders [0]. customer. customerid);} // prevent Console window from closing. console. readline (); //************************************** * ********** // use a strong type Data context. //************************************** * ********** // Use a connection string. // northwind DB = new northwind (@ "C: \ linqtest5 \ northwind. MDF "); // query for MERs from Seattle. // var seattlecustomers = // from customerobject in dB. MERs // Where customerobject. city = "Seattle" // select customerobject; // foreach (VAR customerobject in seattlecustomers) // {// console. writeline ("ID = {0 }", Customerobject. customerid); // freeze the console window. // console. readline () ;}} using system; using system. collections. generic; using system. LINQ; using system. text; using system. data. LINQ; using system. data. LINQ. mapping; namespace demo02 {[Table (name = "MERs")] public class customer {private string _ customerid; [column (isprimarykey = true, storage = "_ customerid")] public String cust Omerid {get {return this. _ customerid;} set {This. _ customerid = value ;}} private string _ city; [column (storage = "_ City")] Public String city {get {return this. _ city;} set {This. _ city = value ;}} private entityset <order> _ orders; public customer () {This. _ orders = new entityset <order> ();} [Association (storage = "_ orders", thiskey = "mermerid", otherkey = "customerid")] public entityse T <order> orders {get {return this. _ orders;} set {This. _ orders. assign (value) ;}} [Table (name = "orders")] public class order {private int _ orderid = 0; private string _ customerid; private entityref <customer> _ customer; public order () {This. _ customer = new entityref <customer> ();} [column (storage = "_ orderid", dbtype = "int not null identity", isprimarykey = true, isdbgenerated = true)] pub LIC int orderid {get {return this. _ orderid;} // no need to specify a setter because isdbgenerated is // true .} [column (storage = "_ customerid", dbtype = "nchar (5)")] Public String customerid {get {return this. _ customerid;} set {This. _ customerid = value ;}} [Association (storage = "_ customer", thiskey = "customerid", otherkey = "customerid")] public customer {get {return this. _ CU Stomer. entity;} set {This. _ customer. entity = value ;}} class program {static void main (string [] ARGs) {//************************************* * *********** // cross-link query. //************************************** * ********** // Use a connection string. datacontext DB = new datacontext (@ "C: \ linqtest5 \ northwind. MDF "); // get a typed table to run queries. table <customer> MERs = dB. gettable <customer> (); // attach the log to show generated SQL. // dB. log = console. out; // query for MERs mers who have placed orders. vaR customershasorders = from customerobject in cust Omers where customerobject. orders. any () Select customerobject; foreach (VAR customerobject in customershasorders) {console. writeline ("ID = {0}, qty = {1}", customerobject. customerid, customerobject. orders. count); // console. writeline (customerobject. orders [0]. customer. customerid);} // prevent Console window from closing. console. readline (); //************************************** * ********** // use a strong type Data context. //************************************** * ********** // Use a connection string. // northwind DB = new northwind (@ "C: \ linqtest5 \ northwind. MDF "); // query for MERs from Seattle. // var seattlecustomers = // from customerobject in dB. MERs // Where customerobject. city = "Seattle" // select customerobject; // foreach (VAR customerobject in seattlecustomers) // {// console. writeline ("ID = {0}", customerobject. customerid); // freeze the console window. // console. readline ();}}}

 

Northwind. CS

using System;   using System.Collections.Generic;   using System.Linq;   using System.Text;   using System.Data.Linq;     namespace Demo02   {       public class Northwind : DataContext       {           // Table<T> abstracts database details per table/data type.           public Table<Customer> Customers;           public Table<Order> Orders;             public Northwind(string connection) : base(connection) { }       }   }