Catalog

• Explain
• simple form
• Anonymous type form
• Conditional form
• Specified type form
• Screening Forms
• Shaped form
• Nested form
• Local invocation method form
• Distinct Form

Explain

The query expression select is placed at the end of the expression, which is similar to the function of select in the SQL command but different in location. And return the variable in the sentence, that is, the query result.

simple form

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

Statement description above: The instance returns a sequence of product names. It's just a statement, but without querying the data, it generates an SQL statement. Only when we need the data will it execute the statement, which is called delayed loading. If the return is the result set of the object, you can use ToList(), ToArray() to save the data.

Anonymous type form

Description: Anonymous type is a new feature in C#3.0. The essence is that 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, and initializes the object according to the data.

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

The above statement description: Use select and anonymous type to return the product name and unit price sequence of the product.

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

                            };

The statement described above: Use select and anonymous type to return the employee's name and mobile phone number sequence, and merge FirstName and LastName into a field Name, rename HomePhone Phoe.

Conditional form

Description: The generated 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"

                        };

The above statement description: Use select and conditional statements to return the product name and the product's supply status sequence.

Specified type form

Description: This form returns the object set of your custom type.

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

                            };

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

Screening Forms

Description: Where plays a screening role.

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

The above statement states: Use select and Where to return the unit price sequence of a product that contains only the product name Anised Syrup.

shaped form

Note: Anonymous objects are used in select operations, and attributes in this anonymous object also contain anonymous objects.

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

                            };

The above code description: Use select to return the ID of the order, the order time, the city and address sequence of the order customer.

Nested form

Description: Returns the Discounted Products attribute in each object set, which is also a collection, i.e. the combination of the 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 queries to return the sequence of OrderID for all orders, the subsequence of items in discounted orders, and the amount saved by free delivery.

Local invocation method form

Description: In this example query, the local method is called to determine whether the unit price is greater than 20, and the information is returned.

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 is more than 20 yuan";
            }
            else
            {
                return "Unit price less than 20 yuan";
            }
        }

  Distinct Form

Description: Screen different values for querying data that are not duplicated in the result set.

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

The above code description: query the city covered by the order.