Asp.net searches the database based on the first letter of Chinese Characters in pinyin (with the method of calling the LINQ function)

Method 1: first query all records and then query the records after the logical layer is converted to the first letter of the pinyin alphabet. It is obvious that dummies will do this.

Method 2: Add a field to the table to be searched to store the pinyin alphabet corresponding to the content of the field to be searched, and query the two fields at the same time during the search. This method is feasible, but it will increase the size of the database.

Method 3: Create a function in the database. Use this function to convert the query statement to search. The function is as follows:

Copy codeThe Code is as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================== ======
-- Author: xoyozo
-- Create date: 2010-4-17
-- Description: The first letter of a Chinese character.
-- ===================================================== ======
Create function fun_getPY
(
@ Str NVARCHAR (4000)
)
Returns nvarchar (4000)
AS
BEGIN
DECLARE @ word NCHAR (1), @ py nvarchar (4000)
SET @ PY =''
WHILE len (@ str)> 0
BEGIN
SET @ word = left (@ str, 1)
SET @ PY = @ PY + (case when unicode (@ word) BETWEEN 19968 AND 19968 + 20901
THEN (select top 1 py from (
SELECT 'A' as py, n' comment 'AS word
Union all select 'B', n''
Union all select 'C', n' then'
Union all select 'D', N 'region'
Union all select 'E', n' then'
Union all select 'F', n' then'
Union all select 'G', n' then'
Union all select 'h', N 'hour'
Union all select 'J', n' else'
Union all select 'k', n' then'
Union all select 'l', n' then'
Union all select 'M', n' then'
Union all select 'n', n' then'
Union all select 'O', n' then'
Union all select 'P', n' exposure'
Union all select 'Q', n' then'
Union all select 'R', N 'region'
Union all select's ', N 'region'
Union all select 'T', n' then'
Union all select 'w', N 'hangzhou'
Union all select 'x', n' then'
Union all select 'y', n' then'
Union all select 'Z', n' else'
) T
WHERE word >=@ word COLLATE Chinese_PRC_CS_AS_KS_WS
Order by py asc) ELSE @ word END)
SET @ str = right (@ str, len (@ str)-1)
END
RETURN @ PY
END



Usage:
Copy codeThe Code is as follows:
Select * from table where fun_getPY (field) like n' % zgr %'



This method is the quickest and most convenient. By testing to search the first letter of the title in the document table, the time consumed is about three times that of searching without this function, I believe that the cost of deployment in projects with small data volumes is very low.

Operations in the LINQ Mode
Users who are new to LINQ may be right. dbml files are not very familiar, and tables in the database can be dragged directly. on the left side of dbml, while stored procedures and functions can be dragged directly to the right side of dbml, and then can be directly used in logic. The following is an example:
Copy codeThe Code is as follows:
DataClasses1DataContext db = new DataClasses1DataContext ();
Var q = from d in db. Documents
Where d. Title. Contains ("zgr") | db. fun_getPY (d. Title). Contains ("zgr ")
Select d;