LINQ experience (11) -- Null semantics and String/DateTime method of the linq to SQL statement

In this series, we mainly introduce the basics of LINQ to SQL. Because of the powerful nature of LINQ, it has different contents for different data sources, this includes the SQL Server database's LINQ to SQL, the XML document's LINQ to XML, and the ADO. NET DataSet;.. NET collections, files, strings, and so on. In addition, some open-source projects that support LINQ, such as LINQ to JSON and LINQ for nhib.pdf, are also available. In this series, there are so many things about the basics of LINQ to SQL. This article uses some examples to illustrate the Null semantics and the String/DateTime method.

Null Semantics

Note: The first example below describes the query of employees whose ReportsToEmployee is null. The second example uses Nullable <T>. HasValue to query an employee. The result is the same as that in the first example. In the third example, Nullable <T>. Value is used to return the ReportsTo Value of an employee whose ReportsToEmployee is not null.

1. Null

Find all employees not affiliated with another employee:

var q =    from e in db.Employees    where e.ReportsToEmployee == null    select e;

2. Nullable <T>. HasValue

Find all employees not affiliated with another employee:

var q =    from e in db.Employees    where !e.ReportsTo.HasValue    select e;

3. Nullable <T>. Value

Returns the employee ID of the former. Note that. Value is optional:

var q =    from e in db.Employees    where e.ReportsTo.HasValue    select new    {        e.FirstName,        e.LastName,        ReportsTo = e.ReportsTo.Value    };

String/Date Functions (String/Date Functions)

The following String method is supported in the LINQ to SQL statement. However, the difference is that the System. String method is case sensitive by default. SQL is case insensitive.

1. String Concatenation)

var q =    from c in db.Customers    select new    {        c.CustomerID,        Location = c.City + ", " + c.Country    };

Statement Description: This example uses the + operator to concatenate string fields and strings in the form of the calculated customer Location Value.

2. String. Length

var q =    from p in db.Products    where p.ProductName.Length < 10    select p;

Statement Description: In this example, the Length attribute is used to search for all products whose names are less than 10 characters.

3. String. Contains (substring)

var q =    from c in db.Customers    where c.ContactName.Contains("Anders")    select c;

Statement Description: In this example, the Contains method is used to find customers whose contact names contain "Anders.

4. String. IndexOf (substring)

var q =    from c in db.Customers    select new    {        c.ContactName,        SpacePos = c.ContactName.IndexOf(" ")    };

Statement Description: In this example, the IndexOf method is used to find the location of the first space in each customer contact's name.

5. String. StartsWith (prefix)

var q =    from c in db.Customers    where c.ContactName.StartsWith("Maria")    select c;

Statement Description: In this example, the StartsWith method is used to find the customer whose contact name starts with "Maria.

6. String. EndsWith (suffix)

var q =    from c in db.Customers    where c.ContactName.EndsWith("Anders")    select c;

Statement Description: In this example, the EndsWith method is used to find the customer whose contact name ends with "Anders.

7. String. Substring (start)

var q =    from p in db.Products    select p.ProductName.Substring(3);

Statement Description: In this example, the Substring method is used to return the part of the product name starting with the fourth letter.

8. String. Substring (start, length)

var q =    from e in db.Employees    where e.HomePhone.Substring(6, 3) == "555"    select e;

Statement Description: In this example, the Substring method is used to find an employee whose home phone number is "555" from the seventh to ninth.

9. String. ToUpper ()

var q =    from e in db.Employees    select new    {        LastName = e.LastName.ToUpper(),        e.FirstName    };

Statement Description: In this example, the ToUpper method is used to return the employee name whose last name has been converted to uppercase.

10. String. ToLower ()

var q =    from c in db.Categories    select c.CategoryName.ToLower();

Statement Description: In this example, the ToLower method is used to return the category name that has been converted to lowercase.

11. String. Trim ()

var q =    from e in db.Employees    select e.HomePhone.Substring(0, 5).Trim();

Statement Description: In this example, the Trim method is used to return the first five digits of an employee's home phone number and remove leading and trailing spaces.

12. String. Insert (pos, str)

var q =    from e in db.Employees    where e.HomePhone.Substring(4, 1) == ")"    select e.HomePhone.Insert(5, ":");

Statement Description: In this example, the "Insert" method is used to return the sequence of employee phone numbers whose fifth digit is), and Insert one following :.

13. String. Remove (start)

var q =    from e in db.Employees    where e.HomePhone.Substring(4, 1) == ")"    select e.HomePhone.Remove(9);

Statement Description: In this example, the Remove method is used to return the sequence of employee phone numbers whose fifth digit is) and Remove all characters starting from the tenth character.

14. String. Remove (start, length)

var q =    from e in db.Employees    where e.HomePhone.Substring(4, 1) == ")"    select e.HomePhone.Remove(0, 6);

Statement Description: This example uses the Remove Method to return the sequence of employee phone numbers whose fifth digit is) and Remove the first six characters.

15. String. Replace (find, replace)

var q =    from s in db.Suppliers    select new    {        s.CompanyName,        Country = s.Country        .Replace("UK", "United Kingdom")        .Replace("USA", "United States of America")    };

Statement Description: In this example, the Replace method is used to return the supplier information in the Country field where UK is replaced with United Kingdom and USA is replaced with United States of America.

Date Functions

The following DateTime methods are supported in the LINQ to SQL statement. However, the DateTime types of SQL Server and CLR differ in the range and time cycle precision, as shown in the following table.

Type	Minimum value	Maximum Value	Timing cycle
System. DateTime	January 1, 0001	December 31, 9999	100 milliseconds (0.0000001 seconds)
T-SQL DateTime	January 1, 1753	December 31, 9999	3.33... Millisecond (0.0033333 seconds)
SmallDateTime T-SQL	January 1, 1900	June 6, 2079	1 minute (60 seconds)

The CLR DateTime type has a larger range and higher accuracy than the SQL Server type. Therefore, when data from SQL Server is represented by the CLR type, the value or precision will never be lost. However, in turn, the range may be reduced, and the precision may be reduced. SQL Server date does not have the TimeZone concept, and this function is supported in CLR.
You must perform the conversion at the local time, UTC, or fixed time for the query in the LINQ to SQL statement.

Three instances are described below.

16. DateTime. Year

var q =    from o in db.Orders    where o.OrderDate.Value.Year == 1997    select o;

Statement Description: In this example, the Year attribute of DateTime is used to search for orders placed on January 1, 1997.

17. DateTime. Month

var q =    from o in db.Orders    where o.OrderDate.Value.Month == 12    select o;

Statement Description: In this example, the Month attribute of DateTime is used to search for orders placed on January 1, December.

18. DateTime. Day

var q =    from o in db.Orders    where o.OrderDate.Value.Day == 31    select o;

Statement Description: This example uses the 'day' attribute of 'datetime' to search for orders placed on the 31st Day of a month.

This series of links: navigation to the LINQ Series

Recommended resources of LINQ

Special topics: http://kb.cnblogs.com/zt/linq/ on all aspects of the entry, advanced, in-depth articles on LINQ.
LINQ group: a good place to learn questions or questions about http://space.cnblogs.com/group/linq.

This article is based on the signature 2.5 mainland China license agreement. You are welcome to reprint, interpret, or use it for commercial purposes. However, you must keep this article's signature Li yongjing (including the link). For more information, see here. If you have any questions or authorization negotiation, please leave a message for me.