LINQ to SQL: processing the char (1) field can cause full table scan problems.

 

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

Table Definition	Generated entity

2.

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

Var test1 = from pInDb. ProductLines

Where p. LineCode = 'A'

Select p;

The generated SQL statement is as follows:

SELECT [t0]. [LineCode], [t0]. [LineName], [t0]. [JPH], [t0]. [QueueCount]

FROM [dbo]. [ProductLine] AS [t0]

Where unicode ([t0]. [LineCode]) = @ p0

-- @ P0: Input Int (Size = 0; Prec = 0; Scale = 0) [65]

-- Context: SqlProvider (Sql2000) Model: AttributedMetaModel Build: 3.5.21022.8

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.

Linq statements	Var test1 = from pInDb. ProductLines Where p. LineCode = 'A' Select p;	Var test1 = from pInDb. ProductLines Where p. LineCode = 'A' Select p;
Generate SQL statements	SELECT [t0]. [LineCode], [t0]. [LineName], [t0]. [JPH], [t0]. [QueueCount] FROM [dbo]. [ProductLine] AS [t0] Where unicode ([t0]. [LineCode]) = @ p0 -- @ P0: Input Int (Size = 0; Prec = 0; Scale = 0) [97]	SELECT [t0]. [LineCode], [t0]. [LineName], [t0]. [JPH], [t0]. [QueueCount] FROM [dbo]. [ProductLine] AS [t0] Where unicode ([t0]. [LineCode]) = @ p0 -- @ P0: Input Int (Size = 0; Prec = 0; Scale = 0) [65]

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.

Var test1 = from pInDb. ProductLines Where p. LineCode ="A" Select p;	SELECT [t0]. [LineCode], [t0]. [LineName], [t0]. [JPH], [t0]. [QueueCount] FROM [dbo]. [ProductLine] AS [t0] WHERE [t0]. [LineCode] = @ p0 -- @ P0: Input VarChar (Size = 1; Prec = 0; Scale = 0) [a] -- Context: SqlProvider (Sql2000) Model: AttributedMetaModel Build: 3.5.21022.8
Linq	SQL

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.