Null semantics and 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 long.

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.