使用EF來調用存儲過程

1：首先先貼上一個簡單的存儲過程

USE [testActice]
GO
/****** Object:  StoredProcedure [dbo].[selectTable]    Script Date: 06/12/2017 15:38:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[selectTable]
@id int,
@name varchar()

AS
BEGIN
SET NOCOUNT ON;
declare @sql nvarchar(1000)
set @sql='select * from myTable '
if @id >
begin
  set @sql[email protected]sql+' where id='+cast(@id as varchar())
end
if @name <>''
begin
  set @sql[email protected]sql+' and name='[email protected]
end

exec sp_executesql @sql
set nocount off;
END

           

2：使用EF調用存儲過程，并傳回結果集

List<myTable> s = new List<myTable>();
            SqlParameter[] sqlparms=new SqlParameter[];
            sqlparms[] = new SqlParameter("@ID", id);
            sqlparms[] = new SqlParameter("@name", name);
            var result = this.db.Database.SqlQuery<myTable>("exec selectTable @ID,@name", sqlparms);
            foreach (var c in result)
            {
                s.Add(new myTable { detail = c.detail, ID = c.ID, mes = c.mes, myTableID = c.myTableID, name = c.name });
            }
            return s;