LINQ to SQL 呼叫預存程序完整攻略：五大回傳模式與輸出參數

預存程序映射基礎

將預存程序拖曳至 O/R 設計器，LINQ to SQL 會自動產生對應方法，回傳型別依程序特性而定：ISingleResult<T>、IMultipleResults 或標量型別。

五種常見回傳模式實戰

1. 標量回傳

程序 Customers Count By Region 回傳指定區域客戶數：

[Function(Name="dbo.[Customers Count By Region]")]
public int Customers_Count_By_Region([Parameter(DbType="NVarChar(15)")] string param1) {
    IExecuteResult result = this.ExecuteMethodCall(this, MethodInfo.GetCurrentMethod(), param1);
    return (int)result.ReturnValue;
}

呼叫：int count = db.Customers_Count_By_Region("WA");

2. 單一結果集

程序 Customers By City 回傳指定城市客戶資料：

ISingleResult<Customers_By_CityResult> result = db.Customers_By_City("London");
foreach (var cust in result) Console.WriteLine($"CustID={cust.CustomerID}; City={cust.City}");

3. 多重可能形狀的單一結果集

程序依參數回傳不同欄位組合，需手動修飾 [ResultType] 並改回傳 IMultipleResults：

[Function(Name="dbo.[Whole Or Partial Customers Set]")]
[ResultType(typeof(WholeCustomersSetResult))]
[ResultType(typeof(PartialCustomersSetResult))]
public IMultipleResults Whole_Or_Partial_Customers_Set([Parameter(DbType="Int")] int? param1) { ... }

呼叫端透過 GetResult<T>() 取得對應形狀：

IMultipleResults result = db.Whole_Or_Partial_Customers_Set(1);
var shape1 = result.GetResult<WholeCustomersSetResult>();
result = db.Whole_Or_Partial_Customers_Set(2);
var shape2 = result.GetResult<PartialCustomersSetResult>();

4. 多個依序結果集

程序 Get Customer And Orders 依序回傳客戶與訂單：

[Function(Name="dbo.[Get Customer And Orders]")]
[ResultType(typeof(CustomerResultSet))]
[ResultType(typeof(OrdersResultSet))]
public IMultipleResults Get_Customer_And_Orders(string customerID) { ... }

呼叫：

IMultipleResults result = db.Get_Customer_And_Orders("SEVES");
var customers = result.GetResult<CustomerResultSet>();
var orders = result.GetResult<OrdersResultSet>();

5. 帶輸出參數

程序 CustOrderTotal 以 OUTPUT 參數回傳總銷售額：

[Function(Name="dbo.CustOrderTotal")]
public int CustOrderTotal(
    [Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID,
    [Parameter(Name="TotalSales", DbType="Money")] ref decimal? totalSales) { ... }

呼叫需用 ref：

decimal? totalSales = 0;
db.CustOrderTotal("ALFKI", ref totalSales);
Console.WriteLine($"Total Sales = {totalSales:C}");