XdCxRhDW/XdCxRhDW.Repostory/EFContext/SqliteInterceptor.cs

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace XdCxRhDW.Repostory
{
    /// <summary>
    /// Sqlite拦截器.
    /// contains或indexOf成的CHARINDEX函数在sqlite里面并不支持，需要拦截转换成LIKE语句
    /// </summary>
    public class SqliteInterceptor : DbCommandInterceptor
    {
        private static Regex replaceRegex = new Regex(@"\(CHARINDEX\((.*?),\s?(.*?)\)\)\s*?>\s*?0");

        public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            ReplaceCharIndexFunc(command);
        }

        public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            ReplaceCharIndexFunc(command);
        }

        private void ReplaceCharIndexFunc(DbCommand command)
        {
            bool isMatch = false;
            var text = replaceRegex.Replace(command.CommandText, (match) =>
            {
                if (match.Success)
                {
                    string paramsKey = match.Groups[1].Value;
                    string paramsColumnName = match.Groups[2].Value;
                    //replaceParams
                    foreach (DbParameter param in command.Parameters)
                    {
                        if (param.ParameterName == paramsKey.Substring(1))
                        {
                            param.Value = string.Format("%{0}%", param.Value);
                            break;
                        }
                    }
                    isMatch = true;
                    return string.Format("{0} LIKE {1}", paramsColumnName, paramsKey);
                }
                else
                    return match.Value;
            });
            if (isMatch)
                command.CommandText = text;
        }
    }
}