The Like Statement of EF SQLite generates a CHARINDEX solution, sqlitecharindex

The Like Statement of EF SQLite generates a CHARINDEX solution, sqlitecharindex

When EF SQLite is used, it is found that the Like statement cannot be completely queried. The generated SQL statement is similar to this

(CHARINDEX(@p__linq__2, [Extent1].[LeagueName])) > 0)

After checking the information, SQLite does not support the CHARINDEX function. In fact, the solution is very simple. We only need to implement an Interceptor and replace the SQL statement, then you can add it to EF. The following is the implementation of Interceptor:

Private static Regex replaceRegex = new Regex (@ "\ (CHARINDEX \((.*?), (.*?) \)> 0 \) "); private void ReplaceCharIndexFunc (DbCommand command) {var flag = false; var text = replaceRegex. replace (command. commandText, m => {if (! M. success) return m. value; flag = true; var key = m. groups [1]. value; var name = m. groups [2]. value; // Replace the parameter foreach (DbParameter commandParameter in command. parameters) {if (commandParameter. parameterName = key. substring (1) {commandParameter. value = $ "% {commandParameter. value }%"; break ;}}return $ "{name} LIKE {key}" ;}); if (flag) command. commandText = text ;}

First, replace all charindexes with regular expressions, and then modify the query value. Then we add Interceptor in EF:

public QiuTanDb() : base("name=defaultConn")        {            DbInterception.Add(new SqliteInterceptor());        }