Null semantics and DateTime, linqdatetime of the linq to SQL statement (14)

Null semantics and DateTime, linqdatetime of the linq to SQL statement (14)
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    };

 

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.

1. 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.

2. 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.

3. 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.