［LINQ2SQL］DataContext.ExecuteQuery 出現 NotSupportedException
如果你想看英文版的Q&A: http://forums.asp.net/t/1368922.aspx/1
今天很開心地使用了 DataContext.ExecuteQuery<T>(query, parameters), 結果遇到了傳入的 parameter 有 null 時, 收到了
System.NotSupportedException: 查詢參數不能屬於型別 'System.Object'。
string sqlscript = @"select * from JOB
where JOB_ID={0}
and ({1} IS NULL or STATUS={1})";
int job_id = 0;
string st = null;
context.ExecuteQuery<JOB>(sqlscript, job_id, st);
因為我傳 st = null 進去, 它無法辦識 null 是什麼型態故視為System.Object,
咦怎麼跟MSDN說的 If a parameter is null, it is converted to DBNull.Value. 不一樣 !!
好吧, 那我直接傳DBNull.Value 會送你 InvalidOperationException ...
這不是平常會遇到, 而是我這種懶人, 不喜歡去組動態SQL (If parameter value is null, don't add condition script),
所以花一點SQL效能及語法去處理PARAMETER VALUE 是 NULL時,
({1} IS NULL or STATUS={1})
我的可用步數
1. 回到ADO.NET.
2. 乖乖組動態SQL Script 如果遇到 NULL 就不組SQL, 但每一支要用到ExecuteQuery者都要這麼做.
3. 想想其它辦法囉 ...
答案3: 用一個Utitlity動態去改Sql Script.
另外因為我看{n} 很討厭順便轉一轉可以直接用@param, 讓sql 可以不用改直接貼進來就好,
不過因為時間有限, 暫時先不處理同名故用同參數的情形, 有幾個@param 就要傳幾個object進來.
底層方法一：先轉SQL Script
用我十分不拿手的regular expression來拆@xxx , 另外先把組的字串交給另一個Func 來做, 讓它可以不只處理 null (當然現在只會處理null)
/// <summary>
///
/// </summary>
/// <param name="input">SQL Script</param>
/// <param name="appendFunc">
/// <para type="int" name="i">第i個參數</para>
/// <para type="string" name="value">第i個參數名稱</para>
/// <para type="string" name="returns">實作者回傳的字串,將取代參數名稱</para>
/// </param>
/// <param name="paramValues"></param>
/// <returns></returns>
private string TransferSqlScript(
string input, Func<int, string, string> appendStr, params object[] paramValues)
{
StringBuilder buf = new StringBuilder();
string pattern = @"(@[\s\S]+?)([,\s\r)])"; // find '@xxx,' , '@xxx ' , '@xxx\r'
Regex regex = new Regex(pattern, RegexOptions.IgnoreCase | RegexOptions.Multiline);
MatchCollection collection = regex.Matches(input);
int iCount = collection.Count;
int pCount = paramValues.Length;
if (iCount != pCount) // the @xxx count must match paramValues count.
{
throw new InvalidOperationException("參數量不符合 指令:" + iCount + ", 參數:" + pCount);
}
int endIndex = 0;
for (int i = 0; i < iCount; i++)
{
Match match = collection[i];
GroupCollection groups = match.Groups; // [0]FULL MATCH [1]@... [2]分隔
Group group = groups[1];
int startIndex = group.Index; // begin of @
int length = group.Length; // length of @xxx
string prefix = input.Substring(endIndex, startIndex - endIndex); // before @xxx
buf.Append(prefix);
string paramName = group.Value;
buf.Append(appendStr(i, paramName));
endIndex = startIndex + length; // move index
}
int lessLength = input.Length - endIndex; // after end of @xxx
if(lessLength>0){
buf.Append(input.Substring(endIndex, lessLength));
}
return buf.ToString();
}
共用方法二: 實際上的共用方法, 用法跟原先一模一樣, 只差不要傳{n}進來, 請用@XXX
public IEnumerable<T> ExecuteQuery<T>(string scriptText, params object[] paramValues)
{
int nullStackCount = 0;
string script = TransferSqlScript(scriptText, delegate(int i, string value)
{
if (paramValues[i] == null)
{
nullStackCount++;
return "NULL";
}
return "{" + (i - nullStackCount) + "}";
} , paramValues);
object[] trimNullParamValues = (from _v in paramValues
where _v != null
select _v).ToArray<object>();
return context.ExecuteQuery<T>(script, trimNullParamValues);
}
其中委派的部份, 可以自行擴充, 如當你要用 in {, , } , 或是要加 Trim() 或TO_CAHR()之類!!
:D 太棒了, 現在可以這樣呼了,
string sqlscript = @"select * from JOB
where JOB_ID=@JOB_ID
and (@STATUS IS NULL or STATUS=@STATUS);
int job_id = 0;
string st = null;
ExecuteQuery<JOB>(sqlscript, job_id, st, st); // 多一個參數, 因為還沒實作處理同名參數
最後一動同名參數, 有空再實做 (其實是太差, 還想不到怎麼做 ...)