假如遇到必要拼接SQL字串需求的情況下,舉例 : 有時候值使用字串拼接會比不使用參數化效率好,特別是該欄位值只會有幾種固定值。

這時候Dapper可以使用Literal Replacements功能,使用方式 : 將要拼接的值字串以{=屬性名稱}取代,並將值保存在Parameter參數內,舉例 :

void Main()
{
	using (var cn = Connection)
	{
		var result = cn.Query("select N'暐翰' Name,26 Age,{=VipLevel} VipLevel", new User{ VipLevel = 1}).First();
	}
}

為什麼Literal Replacement可以避免緩存問題

首先追蹤源碼GetCacheInfo下GetLiteralTokens方法,可以發現Dapper在建立緩存之前會抓取SQL字串內符合{=變數名稱}規格的資料。

private static readonly Regex literalTokens = new Regex(@"(?<![\p{L}\p{N}_])\{=([\p{L}\p{N}_]+)\}", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.CultureInvariant | RegexOptions.Compiled);
internal static IList<LiteralToken> GetLiteralTokens(string sql)
{
	if (string.IsNullOrEmpty(sql)) return LiteralToken.None;
	if (!literalTokens.IsMatch(sql)) return LiteralToken.None;

	var matches = literalTokens.Matches(sql);
	var found = new HashSet<string>(StringComparer.Ordinal);
	List<LiteralToken> list = new List<LiteralToken>(matches.Count);
	foreach (Match match in matches)
	{
		string token = match.Value;
		if (found.Add(match.Value))
		{
			list.Add(new LiteralToken(token, match.Groups[1].Value));
		}
	}
	return list.Count == 0 ? LiteralToken.None : list;
}

接著在CreateParamInfoGenerator方法生成Parameter參數化動態方法,此段方法IL如下 :

IL_0000: ldarg.1    
IL_0001: castclass  <>f__AnonymousType1`1[System.Int32]
IL_0006: stloc.0    
IL_0007: ldarg.0    
IL_0008: callvirt   System.Data.IDataParameterCollection get_Parameters()/System.Data.IDbCommand
IL_000d: pop        
IL_000e: ldarg.0    
IL_000f: ldarg.0    
IL_0010: callvirt   System.String get_CommandText()/System.Data.IDbCommand
IL_0015: ldstr      "{=VipLevel}"
IL_001a: ldloc.0    
IL_001b: callvirt   Int32 get_VipLevel()/<>f__AnonymousType1`1[System.Int32]
IL_0020: stloc.1    
IL_0021: ldloca.s   V_1

IL_0023: call       System.Globalization.CultureInfo get_InvariantCulture()/System.Globalization.CultureInfo
IL_0028: call       System.String ToString(System.IFormatProvider)/System.Int32
IL_002d: callvirt   System.String Replace(System.String, System.String)/System.String
IL_0032: callvirt   Void set_CommandText(System.String)/System.Data.IDbCommand
IL_0037: ret        

接著再生成Mapping動態方法,要了解此段邏輯我這邊做一個模擬例子方便讀者理解 :

public static class DbExtension
{
	public static IEnumerable<User> Query(this DbConnection cnn, string sql, User parameter)
	{
		using (var command = cnn.CreateCommand())
		{
			command.CommandText = sql;
			CommandLiteralReplace(command, parameter);
			using (var reader = command.ExecuteReader())
				while (reader.Read())
					yield return Mapping(reader);
		}
	}

	private static void CommandLiteralReplace(IDbCommand cmd, User parameter)
	{
		cmd.CommandText = cmd.CommandText.Replace("{=VipLevel}", parameter.VipLevel.ToString(System.Globalization.CultureInfo.InvariantCulture));
	}

	private static User Mapping(IDataReader reader)
	{
		var user = new User();
		var value = default(object);
		value = reader[0];
		if(!(value is System.DBNull))
			user.Name = (string)value;
		value = reader[1];
		if (!(value is System.DBNull))
			user.Age = (int)value;
		value = reader[2];
		if (!(value is System.DBNull))
			user.VipLevel = (int)value;
		return user;
	}
}

看完以上例子,可以發現Dapper Literal Replacements底層原理就是字串取代,同樣屬於字串拼接方式,為何可以避免緩存問題?

這是因為取代的時機點在SetParameter動態方法內,所以Cache的SQL Key是沒有變動過的,可以重複利用同樣的SQL字串、緩存。

也因為是字串取代方式,所以只支持基本Value類別,假如使用String類別系統會告知The type String is not supported for SQL literals.,避免SQL Injection問題。