The nine forms of Select queries in the LINQ to SQL statements, linqsql

The nine forms of Select queries in the LINQ to SQL statements, linqsql
Directory

• Description
• Simple Form
• Anonymous type
• Condition form
• Type format
• Filtering form
• Shaped format
• Nested form
• Local call method form
• Distinct form

Description

Similar to the select statement in SQL commands, the query expression select is placed at the end of the expression. Return the variables in the statement, that is, the query result.

Simple Form

var q = from c in db.Products select c.ProductName;

Description of the preceding statement: the sequence of product names returned by the instance. It is just a declaration, but it does not query data, it will generate an SQL statement, only when we need this data, it will execute this statement, this is called delayed loading. If the returned result is the result set of an object, you can use ToList () and ToArray () to save the data.

Anonymous type

Note: The anonymous type is a new feature in C #3.0. In essence, the editor automatically generates an anonymous class to store our temporary variables. For example: var person = new {Name = "Jack", Age = 15}, the editor automatically creates an anonymous class, which has two attributes: Name and Age, then, initialize the object based on the data.

  var t = from c in db.Products                        select new                        {                            c.ProductName,                            c.UnitPrice                        };

Description of the preceding statement: the product name and unit price sequence of the product are returned using select and anonymous types.

 var a = from c in db.Employees                        select new                            {                                Name = c.FirstName + c.LastName,                                Phone = c.HomePhone                            };

The preceding statement Description: return the sequence of employee names and mobile phone numbers using the select and anonymous types, combine FirstName and LastName into a field Name, and rename HomePhone to Phoe.

Condition form

Note: The SQL statement is case when condition then else.

var b = from c in db.Products                        select new                        {                            c.ProductName,                            State = c.UnitsInStock - c.UnitsOnOrder > 0 ? "Out Of Stock" : "In Stock"                        };

Description of the preceding statement: Use the select statement and Condition Statement to return the sequence of Product Name and product supply status.

Type format

Description: This form returns your custom object set.

 var a = from c in db.Employees                        select new Person                            {                                Name = c.FirstName,                                Phone = c.HomePhone                            };

Description of the preceding statement: Use select and known type Person to return the sequence of names and mobile phone numbers.

Filtering form

Note: Use the Where clause to filter data.

   var d = from c in db.Products                        where c.ProductName == "Aniseed Syrup" select c.UnitPrice;

Description of the preceding statement: Use select and Where to return the unit price sequence of the product that contains only the product name Aniseed Syrup.

Shaped format

Note: An anonymous object is used in the select operation, and the attribute of this anonymous object also contains an anonymous object.

 var e = from c in db.Orders                        select new                            {                                c.OrderID,                                c.OrderDate,                                Customer = new { c.Customers.City, c.Customers.Address }                            };

The preceding Code Description: Use select to return the order ID, order time, city and Address Sequence of the Order customer.

Nested form

Description: returns the DiscountedProducts attribute of each object set. This attribute is also a set, that is, the combination of attributes of each object.

  var u = from o in db.Orders                        select new                        {                            o.OrderID,                            DiscountedProducts =                                from od in o.Order_Details                                where od.Discount > 0.0                                select od,                            FreeShippingDiscount = o.Freight                        };

The above Code Description: Use nested query to return the OrderID sequence of all orders, the subsequence of the item in the discount order, and the amount saved for free delivery.

Local call method form

Note: In this case, call the local method to determine whether the unit price is greater than 20 and return information.

var m = from g in db.Products.ToList()                        select new                            {                                g.ProductID,                                g.ProductName,                                g.UnitPrice,                                message = GetMessage(g.UnitPrice)                            };

The GetMessage method is as follows:

Public string GetMessage (Nullable <decimal> price) {if (price> 20) {return "unit price greater than 20 RMB" ;}else {return "unit price less than 20 RMB ";}}

Distinct form

Filters distinct values for non-duplicated data in the query result set.

var k = (from j in db.Orders select j.ShipCity).Distinct();

Description of the code above: query the cities covered by the order.