4. query syntax

Summary:

The query syntax of LINQ. I reiterate that this series is my study notes for personal review only. There is a source link in the first article.

Content:

Select:

1. query the customer's company name and address

VaR TB = from C inctx... MERs

Select New

{

Company Name = C. companyName,

Address = C. Address

};

 

Select [t0]. [companyName], [t0]. [address]

From [DBO]. [MERs] as [t0]

 

2. query the employee's name and age

VaR TB = from C inctx... emplpyees

Select New

{

Name = C. lastname + C. firstname,

Employment age = C. hiredate. value. Year

};

 

Select [t0]. [lastname] + [t0]. [firstname] as [value], datepart (year,

[T0]. [hiredate]) as [value2] from [DBO]. [employees] as [t0]

3. query the customer ID and contact information (positions and contacts)

VaR TB = from C inctx... MERs

Select New

{

Id = C. customerid,

Contact info = new

{

Position = C. contacttitle,

Contact = C. contactname

}

};

Select [t0]. [customerid], [t0]. [contacttitle], [t0]. [contactname]

From [DBO]. [MERs] as [t0]

4. Check whether the order number and order are overweight.

Vartb = from C in CTX. Orders

Selectnew

{

Order No. = C. orderid,

Overweight = C. Freight> 100 ?" Yes ":" no"

};

Where:

1. query the country, city, and number of orders of a customer. The country must be France. The number of orders must be greater than 5.

VaR TB = from C inctx. MERs

Wherec. Country =" France & Amp; C. Orders. Count & gt; 5

Select New

{

Country = C. Country,

City = C. City,

Number of orders = C. Orders. Count

};

 

2. query the employment year and name of all employees without subordinates in descending order of employment year.

VaR TB = from X inctx. Employees

Wherec. Employees. Count = 0

Orderbyc. hiredate. value. Year descending, C. firstname ascending

Select New

{

Employment year = C. hiredate. value. Year,

Name = C. firstname

};

Paging:

1. query customers on the second page based on 10 records on each page

VaR TB = (From cin ctx. MERs select c). Skip (10). Take (10 );

GROUP:

1. query the country names and customers with more than 5 customers based on the customer's country grouping.

VaR TB = from C inctx. MERs

Group C byC. Country into G

Whereg. Count ()> 5

Orderbyg. Count () descending

Select New

{

Country = G. Key,

Customer COUNT = G. Count ()

};

2. query the order quantity separately based on whether the condition is overweight.

VaR TB = from C in CTX. Orders

Group C by new {condition = C. Freight> 100} into G

Select New

{

Quantity = G. Count (),

Overweight = G. Key. condition ?" Yes ":" no"

};

Distinct:

1. query countries covered by customers

VaR TB = (from C inctx. Customers orderby C. Country select C. Country). Distinct ();

 

Elect distinct [t0]. [Country]

From [DBO]. [MERs] as [t0]

 

Union :

1. query and sort City A by customer name.

VaR connects and filters out the same item = (fromC in CTX. MERs where c. City. Contains ("")

Select c). Union (from C in CTX. Customers wherec. contactname. startwith ("")

Select c). orderby (C => C. contactname );

 

Concat:

1,Query the customers whose city is a and whose city contains a, and sort by customer name,

The same customer information is not filtered.

VaR connection and the same item is not filtered = (fromC in CTX. MERs wherec. City. Contains ("")

Select c). Concat (from C inctx. MERs where c. contactname. startswith ("A") Select

C). orderby (C => C. contactname );

 

Subquery:

1. query customer information with more than 5 orders

VaR TB = from C inctx. MERs

Where (from o in CTX. ordersgroup o by O. customerid O where O. Count ()> 5

Selecto. Key). Contains (C. customerid)

Select C;

In operation:

1. query customers in a specified City

VaR in operation = from C inctx. MERs

Where new string [] {" Brandenburg "," Cowes "," Stavern "}. Contains (C. City)

Select C;

Join join:

1. inner connection. products without category cannot be found

VaR innerjoin = from P in CTX. Products

Join C in CTX. Categories

On P. categoryid equals C. categoryid

Select P. productname;

 

Select count (*) as [value]

From [DBO]. [products] as [t0]

Inner join [DBO]. [categories] as [T1] on [t0]. [categoryid] = ([T1]. [categoryid])

 

2. external connections can be found for products without category

VaR leftjoin = from P in CTX. Products

Join C inctx. Categories

On P. categoryid limit SC. categoryid

Into pro

From x inpro. defaultifempty ()

Selectp. productname;

 

Select count (*) as [value]

From [DBO]. [products] as [t0]

Left Outer Join [DBO]. [categories] as [T1] on [t0]. [categoryid] = ([T1]. [categoryid])