The where clause in Linq is null, And the linqwhere clause is null.

The where clause in Linq is null, And the linqwhere clause is null.

Today, I encountered a problem: how to judge whether a field is null in the where clause, and this field is not of the string type, but of the int and GUID type. After a long time, I finally figured it out. (Because I took over the project halfway, the problem was that my colleagues had dug a hole for me. Today I am filling it out)

1. Before talking about this problem, let me first talk about the correspondence between the database table and the model between C:

Generally, we can use the code generator (Neusoft code generator) to generate tables in the database. The blank and non-empty fields are also generated for us, such as tables.

The model generated using the code generator is:

/// <Summary> /// SettingConfiguration: Object Class (attribute description automatically extracts the description of the Database field) /// </summary> [Serializable] public partial class SettingConfiguration {public SettingConfiguration () {}# region Model private int _ id; private string _ name; private string _ value; private int? _ Parentid; // <summary> //// </summary> public int Id {set {_ id = value;} get {return _ id ;}} /// <summary >///// </summary> public string Name {set {_ name = value ;}get {return _ name ;}} /// <summary >///// </summary> public string Value {set {_ value = value ;}get {return _ value ;}} /// <summary> ///// </summary> public int? ParentId {set {_ parentid = value;} get {return _ parentid ;}# endregion Model}View Code

In the database, we can see that the ParentId field can be blank, and other fields cannot be blank;

Corresponding to the code Model generated by the code generator, you can see the code:

Public int? ParentId

{
Set {_ parentid = value ;}
Get {return _ parentid ;}
}

Mainly int?

Some do not know this int? What does that mean? What is the difference with int? The difference between the two is as follows: int? It can be null, while int cannot be null. For example, the following two codes:

Int? A = null; // compiled successfully
Int a = null; // compilation error.
Int? Null values can be assigned to the numeric type, which is set to be compatible with the Null value of SQL or other databases.

Therefore, when converting a database table to a model, you must convert the fields that can be empty into the corresponding C # fields that can be empty:

Mainly include

Int --> int?

Guid --> Guid?

DateTime --> DateTime?

Short --> short?

Decimal --> decimal?

Note: Why is string not converted to string? Amount ~~~~~ You are stupid. A field of the string type can be null. Why write a string ?, One more action.

Through the above introduction, you must have a certain understanding of converting database tables into models. Let's get down to the truth and talk about the problem:

2. root cause of the problem

The root cause of the problem is that the conversion was not converted when the model was created, which led to the failure of one query and three queries.

For a field that can be null, if you want to determine whether this field is null, for example, if we want to query the data where ParentId in the SettingConfiguration table is null, we can write it like this:

Var dtvar = (from des in db. SettingConfiguration
Where (des. ParentId = null)
Select des );

In this case, the query will certainly be okay ~~~~ ~~~

If the ParentId field is

Public int ParentId

{
Set {_ parentid = value ;}
Get {return _ parentid ;}
}

If this is the case, how can you not find the data and report no error? It makes you feel depressed for the rest of your life (I am so depressed), depressed, and found many ways on the Internet, cannot query data,

After I changed the model, I solved all the problems. This is a big pitfall. Let me fill it out and remind everyone that the model must correspond to the database, otherwise, you will find yourself in the future.

 

The following provides some useful methods for querying null:

 

Linq to SQL Null Query

 

SELECT * FROM [Orders] AS [t0] WHERE ([t0]. [ShippedDate]) IS NULL

 Method 1:

From o in Orders where o. ShippedDate = null select o

The corresponding Lamda expression is:

Orders. Where (o => (o. ShippedDate = (DateTime ?) Null ))

The corresponding SQL statement is:

SELECT [t0]. [OrderID], [t0]. [CustomerID], [t0]. [EmployeeID], [t0]. [OrderDate], [t0]. [RequiredDate], [t0]. [ShippedDate], [t0]. [ShipVia], [t0]. [Freight], [t0]. [ShipName], [t0]. [ShipAddress], [t0]. [ShipCity], [t0]. [ShipRegion], [t0]. [ShipPostalCode], [t0]. [ShipCountry] FROM [Orders] AS [t0] WHERE [t0]. [ShippedDate] IS NULL

Method 2:

From o in Orders where Nullable <DateTime>. Equals (o. ShippedDate, null) select o

The corresponding Lamda expression is:

Orders. Where (o => Object. Equals (o. ShippedDate, null ))

The corresponding SQL statement is:

SELECT [t0]. [OrderID], [t0]. [CustomerID], [t0]. [EmployeeID], [t0]. [OrderDate], [t0]. [RequiredDate], [t0]. [ShippedDate], [t0]. [ShipVia], [t0]. [Freight], [t0]. [ShipName], [t0]. [ShipAddress], [t0]. [ShipCity], [t0]. [ShipRegion], [t0]. [ShipPostalCode], [t0]. [ShipCountry] FROM [Orders] AS [t0] WHERE [t0]. [ShippedDate] IS NULL

Method 3: 

From o in Orders where! O. ShippedDate. HasValue select o

The corresponding Lamda expression is:

Orders. Where (o =>! (O. ShippedDate. HasValue ))

The corresponding SQL statement is:

SELECT [t0]. [OrderID], [t0]. [CustomerID], [t0]. [EmployeeID], [t0]. [OrderDate], [t0]. [RequiredDate], [t0]. [ShippedDate], [t0]. [ShipVia], [t0]. [Freight], [t0]. [ShipName], [t0]. [ShipAddress], [t0]. [ShipCity], [t0]. [ShipRegion], [t0]. [ShipPostalCode], [t0]. [ShipCountry] FROM [Orders] AS [t0] where not ([t0]. [ShippedDate] is not null)

 Method 4:

From o in Orders where o. ShippedDate. Value = (DateTime ?) Null select o

The corresponding Lamda expression is: 

Orders. Where (o => (DateTime ?) (O. ShippedDate. Value) = (DateTime ?) Null ))

The corresponding SQL statement is:

SELECT [t0]. [OrderID], [t0]. [CustomerID], [t0]. [EmployeeID], [t0]. [OrderDate], [t0]. [RequiredDate], [t0]. [ShippedDate], [t0]. [ShipVia], [t0]. [Freight], [t0]. [ShipName], [t0]. [ShipAddress], [t0]. [ShipCity], [t0]. [ShipRegion], [t0]. [ShipPostalCode], [t0]. [ShipCountry] FROM [Orders] AS [t0] WHERE ([t0]. [ShippedDate]) IS NULL

 Method 5:

From o in Orders where System. Data. Linq. SqlClient. SqlMethods. Equals (o. ShippedDate. Value, null) select o

The corresponding Lamda expression is:

Orders. Where (o => Object. Equals (o. ShippedDate. Value, null ))

The corresponding SQL statement is:

SELECT [t0]. [OrderID], [t0]. [CustomerID], [t0]. [EmployeeID], [t0]. [OrderDate], [t0]. [RequiredDate], [t0]. [ShippedDate], [t0]. [ShipVia], [t0]. [Freight], [t0]. [ShipName], [t0]. [ShipAddress], [t0]. [ShipCity], [t0]. [ShipRegion], [t0]. [ShipPostalCode], [t0]. [ShipCountry] FROM [Orders] AS [t0] WHERE ([t0]. [ShippedDate]) IS NULL

 

These five methods can be used for reference.