LINQ to SQL: Handling a char (1) field can cause a full table scan problem _mssql

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

Table Definition	Generated entities

2.

If you want to query the records of linecode== ' A ', you can define LINQ query statements like this

var test1 = from P in db. Productlines

where P.linecode = = ' A '

Select P;

The resulting SQL statement is like this

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

Did you notice the WHERE statement? is where UNICODE ([t0].[ Linecode] = 65, where you first take the Unicode comparison of the contents of the Linecode column and the ' a '. We know that the Unicode of ' a ' and ' a ' is different. UNICODE (' a ') =65,unicode (' a ') = 97, which means that the results of the two queries we have in LINQ to SQL are not the same.

LINQ statements	var test1 = from P in Db. Productlines           &nbs P; where P.linecode = = ' A '           ;    select p;	var test1 = from P in db. 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) [a]	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 char (1) Type field is case-sensitive in LINQ to SQL .

This also leads to a more serious problem, and we know that in SQL Server, any operation on the left side of the operator causes SQL to take a full table scan . That is, this query for LINQ causes a full table scan , even if the aggregate index is defined on the [Linecode] column. And if it is where [linecode]= ' A ', you can use the index. Let's look at the comparison of the query execution plans in these two scenarios.

As you can see in the diagram, the SQL statements generated by LINQ to SQL are table scans, while the latter is index lookup.

3.

Countermeasures

In the DBML designer, change the Linecode to a string type.

Look at the query after the change.

var test1 = from P in db. 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

When

is changed to string, the generated SQL no longer uses the Unicode function, resolves the problem of case sensitivity and full table scanning . But a new problem, because the data stored in the database length is 1, in insert and update should be noted that Linecode do not enter too long content, otherwise there will be an error.