Select/distinct of LINQ to SQL statements (2)

SELECT/DISTINCT operations include 9 forms, namely simple usage, anonymous type form, conditional form, specified type form, filter form, shaping type form, nested type form, local method invocation form, distinct form.

1. Simple usage:

This example returns a sequence that contains only the name of the customer contact person.

var q =

From C in DB. Customers

Select C.contactname;

Note: This statement is just a declaration or a description, and does not really take the data out, only when you need the data, it will execute the statement, which is deferred loading (deferred loading). If, at the time of declaration, the result set returned is the collection of objects. You can use the ToList () or ToArray () method to save the results of the query before querying the collection. Of course, lazy loading (deferred loading) can stitch the query syntax like a stitched SQL statement, and then execute it.

2. Anonymous type form:

Description: The anonymous type is a new feature in c#3.0. The essence of this is that the compiler automatically generates an anonymous class based on our customizations to help us implement the storage of temporary variables. Anonymous types also depend on another feature: support for creating objects based on property. For example, var d = new {name = "s"}; The compiler automatically generates an anonymous class with property called Name, then allocates memory by this type and initializes the object. but var d = new {"S"}; it is not compiled. Because the compiler does not know the name of the property in the anonymous class. For example, string c = "D"; var d = new {C}; It is possible to compile the. The compiler creates a property called the anonymous class with the name C.

For example, the following example: new {c,contactname,c.phone}; Both ContactName and phone define the property that corresponds to the field in the table in the mapping file. When the compiler reads the data and creates the object, an anonymous class is created that has two properties, ContactName and phone, and then initializes the object based on the data. In addition, the compiler can rename the property's name.

var q =

From C in DB. Customers

Select New {c.contactname, c.phone};

Above statement description: Returns a sequence with only the customer contact name and phone number using SELECT and anonymous types

var q =

From E in DB. Employees

Select New

{

Name = E.firstname + "" + E.lastname,

Phone = E.homephone

};

The above statement describes: Use Select and anonymous types to return a sequence that contains only employee names and phone numbers, and merge the FirstName and LastName fields into a field "Name", and also rename the HomePhone field to phone in the resulting sequence.

var q =

from P in Db. Products

Select New

{

P.productid,

Halfprice = P.UNITPRICE/2

};

The above statement describes: Use Select and anonymous types to return the IDs of all products and the sequence of Halfprice (set to the value of the product unit price divided by 2).

3. Condition form:

Description: The generated SQL statement is: case when condition then else.

var q =

from P in Db. Products

Select New

{

P.productname,

Availability =

P.unitsinstock-p.unitsonorder < 0?

"Out of the stock": "In Stock"

};

The above statement describes: use SELECT and conditional statements to return a sequence of product names and product availability status.

4. Specify the type form:

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

var q =

From E in DB. Employees

Select New Name

{

FirstName = E.firstname,

LastName = E.lastname

};

The above statement describes: Returns a sequence of employee names using select and known types.

5. Filter form:

Description: Combine where to use, play the role of filtering.

var q =

From C in DB. Customers

where c.city = = "London"

Select C.contactname;

The above statement describes: use Select and where to return a sequence with only the London customer contact name.

6.shaped form (shaping type):

Description: Its select operation uses an anonymous object, and in this anonymous object, its properties are an anonymous object.

var q =

From C in DB. Customers

Select New {

C.customerid,

CompanyInfo = new {c.companyname, c.city, c.country},

ContactInfo = new {c.contactname, c.contacttitle}

};

Statement Description: Uses SELECT and anonymous types to return an shaping subset of the data about the customer. Check the customer ID and company information (company name, city, country) and contact information (contacts and positions).

7. Nested type form:

Description: Returns each object in the set of objects in the Discountedproducts property, and also contains a collection. That is, each object is also a collection class.

var q =

From O in Db. Orders

Select New {

O.orderid,

Discountedproducts =

From OD in o.orderdetails

Where OD. Discount > 0.0

Select OD,

Freeshippingdiscount = O.freight

};

Statement Description: Use a nested query to return the sequence of all orders and their OrderID, the sub-sequences of items in a discounted order, and the amount saved by free shipping.

8. Local method invocation form (Localmethodcall):

This example calls the local method Phonenumberconverter in the query to convert the phone number to an international format.

var q = from C in db. Customers

where c.country = = "UK" | | C.country = = "USA"

Select New

{

C.customerid,

C.companyname,

Phone = C.phone,

Internationalphone =

Phonenumberconverter (C.country, C.phone)

};

The Phonenumberconverter method is as follows:

public string Phonenumberconverter (String country, String Phone)

{

Phone = Phone.replace ("", ""). Replace (")", ")-");

Switch (country)

{

Case "USA":

Return "1-" + Phone;

Case "UK":

Return "44-" + Phone;

Default

return Phone;

}

}

The following also uses this method to convert phone numbers to international format and create XDocument

XDocument doc = new XDocument (

New XElement ("Customers", from C in DB. Customers

where c.country = = "UK" | | C.country = = "USA"

Select (New XElement ("Customer",

New XAttribute ("CustomerID", C.customerid),

New XAttribute ("CompanyName", C.companyname),

New XAttribute ("Interationalphone",

Phonenumberconverter (C.country, C.phone))

))));

9.DISTINCT form:

Description: Filters for values that are not the same in the field. Used to query for a result set that is not duplicated. The generated SQL statement is: SELECT DISTINCT [City] from [Customers]

var q = (

From C in DB. Customers

Select c.city)

. Distinct ();

Statement Description: Query the country where the customer is covered.