LINQtoSQL: the method of processing the char (1) field will cause the full table scan problem.

1. Related content: In SQLServer2000, if the database sorting rule is Chinese_PRC_CI_AS, the query results are case-insensitive. For example, the following two SQL statements have the same query results.

1. related content: in SQL Server 2000, if the database sorting rule is Chinese_PRC_CI_AS, the query results are case-insensitive. For example, the following two SQL statements have the same query results.

If the field type in the table is char (1), Linq to SQL generates the char (System. Char) attribute, as shown in

2.

To query records with LineCode = 'A', you can define A Linq query statement.

The generated SQL statement is as follows:

Have you noticed the Where statement? It is where unicode ([t0]. [LineCode]) = 65. Here, we take the unicode of the LineCode column and compare it with the UNICODE of 'A. We know that the UNICODE values of 'A' and 'A' are different. UNICODE ('A') = 65, UNICODE ('A') = 97. That is to say, the results of these two queries are different in Linq to SQL.

Generate SQL statements

Obviously,The query of char (1) fields in Linq to SQL is case-sensitive..

This also causes a serious problem. We know that in SQL Server,Any operation on the left side of the operator enables SQL to use full table scan.. That is to say, the query of the Linq,This will cause a full table scan.Even if the [LineCode] column defines an aggregate index. If it is where [linecode] = 'A', you can use an index. Let's take a look at the comparison of query execution plans in these two cases.

As shown in the figure, the SQL statements generated by Linq to SQL are table scans, while the latter is index searches.

3.

Countermeasure

In the DBML designer, change LineCode to the string type.

Check the modified query.

LinqSQL

After changing to string, the generated SQL will no longer use UNICODE functions,This solves the case-sensitive and full table scan problems.. However, a new problem occurs because the length of data stored in the database is 1. During Insert and Update operations, note that LineCode should not input too long. Otherwise, an error may occur.