【Entity Framework】Using WHERE IN Clause in Stored Procedure by Entity Framework

這題的問題在 EF ObjectContext 的 YourDbEntities 幫你匯進來的方法參數部分是根據宣告內容哈死的，比方說

Create Proc usp_SelectSomething 
@Column1 int,
@Column2 varchar(255)
As 
    Select * From Table 
    Where Column1 = @Column1 AND Column2 = @Column2
Go

這樣的話在C# EF 取就會是

public ObjectResult sp_SelectSomething(global::System.Int32 column1, global::System.String column2)
{
    ...
}
_MyDbEntitie.usp_SelectSomething(column1, column2);

但是這樣就沒辦法應對 WHERE col IN(...) 的應用，因為無法動態產生不定數量的參數。

最簡單的方法是用這篇裡面寫到的作弊方法，這樣

ALTER PROCEDURE [dbo].[usp_CheatedSelectProductByIds]
    @productIds nvarchar(max)    
AS
BEGIN
    SET NOCOUNT ON 
    DECLARE @cmd nvarchar(max) 
    SET @cmd = N'SELECT * FROM Product WHERE id IN (' + @productIds + ')'
    EXEC sp_executesql @cmd
END

string cmdStr = "usp_CheatedSelectProductByIds @productIds";
MyLabEntities entities = new MyLabEntities();
var products = entities.Database.SqlQuery<Product>(
    cmdStr, new SqlParameter("productIds", input)).ToList();

但是如果你想用

var products2 = entities.usp_CheatedSelectProductByIds(input).ToList();

這樣的方式，就必須先參考這篇，設定回傳集合為複雜型別，而且因為是用 sp_executesql 去串字串 cmd，EF 不會幫你把複雜型別建出來，你要自己先去手動建出來才有得選，參考

當然，如果你不用 sp_executesql

ALTER PROC [dbo].[usp_SelectProductByIds] 
@productIds varchar(max)
As 
    SELECT * FROM Product 
    WHERE id IN (@productIds)

EF 就會自己幫你把複雜型別建出來；但同時你也會發現不管是entities.Database.SqlQuery() 或 entities.usp_SelectProductByIds() 都會查不到東西或噴掉。 其實上面用 sp_executesql 硬串命令是有 inject 風險的，SO ref。

所以比較妥當的方法是用你傳入的 CSV 規格字串做一個 temp table，然後以那個 temp table 做子查詢給你的 WHERE IN，參考。

CREATE PROC [dbo].[usp_StableSelectProductByIds] 
@productIds nvarchar(max)
As

DECLARE @Delimeter char(1)
SET @Delimeter = ','

DECLARE @tmpTable TABLE(Id nvarchar(50))
DECLARE @Id nvarchar(50)
DECLARE @StartPos int, @Length int
WHILE LEN(@productIds) > 0
    BEGIN
        SET @StartPos = CHARINDEX(@Delimeter, @productIds)
        IF @StartPos < 0 SET @StartPos = 0
        SET @Length = LEN(@productIds) - @StartPos - 1
        IF @Length < 0 SET @Length = 0
        IF @StartPos > 0
            BEGIN
                SET @Id = SUBSTRING(@productIds, 1, @StartPos - 1)
                SET @productIds = SUBSTRING(@productIds, @StartPos + 1, LEN(@productIds) - @StartPos)
            END
        ELSE
            BEGIN
                SET @Id = @productIds
                SET @productIds = ''
            END
        INSERT @tmpTable (Id) VALUES(@Id)
END

SELECT * FROM Product 
WHERE id IN (SELECT Id FROM @tmpTable)