EF 调用存储过程

1.执行返回表类型的存储过程

 主表从表数据一起关联查询

ALTER proc [dbo].[GetCategory]

    @cidint
as

begin

    select*
       fromCategories
    where@cid = cid

end

 

执行此存储过程的代码如下：


public IEnumerable<Category> GetCategoryWithProductsWithID(intid)

{

    varparameter = newSqlParameter

    {

        DbType = DbType.Int32,

        ParameterName ="cid",

        Value = id

    };

    //联表并延迟加载

    varresult = (fromp in this.Categories.SqlQuery("EXECUTE GetCategory @cid", parameter)select p).ToList();

    returnresult;

}

其中，Category所对应Products是延迟加载进来的，如果我们只使用Category，数据引擎就不会查询Products表的数据，但是，只要我们一到Category中查看Products，就会获取如上图这样的Products数据。请根据源码中的两个数据实体理解。

仅获取主表数据

public IEnumerable<Category> GetCategoryWithID(intid)

{

    varparameter = newSqlParameter

    {

        DbType = DbType.Int32,

        ParameterName ="cid",

        Value = id

    };

    //非联表

    varresult = (fromp in this.Categories.SqlQuery("EXECUTE GetCategory @cid", parameter)

                  selectnew

                  {

                      cid = p.cid,

                      name = p.name

                  }).ToList()

                 .Select(r =>new Category()

                 {

                     cid = r.cid,

                     name = r.name

                 });

    returnresult;

}

由从表关联主表数据

ALTER proc [dbo].[GetProductAndCategory]

    @pidint

as

begin

    selectp.pid, p.[name] , p.discontinued_date, c.cid, c.[name]

    fromProducts as p join Categories as c onp.cid = c.cid

    wherep.pid = @pid    

end

DbContext默认支持实体类型的字段和数据库视图是一个字段名，所以我们去掉了重命名部分。

public IEnumerable<Product> GetProductAndCategoryWithID(intid)

{

    varparameter = newSqlParameter

    {

        DbType = DbType.Int32,

        ParameterName ="pid",

        Value = id

    };

    //延迟加载

    varresult = (fromp in this.Products.SqlQuery("EXECUTE dbo.GetProductAndCategory @pid", parameter)select p).ToList();

    returnresult;

}

要注意，主表的数据也是延迟加载的，只有使用到的时候才会被查询。

2.执行返回值的存储过程

 

CREATE PROCEDURE [dbo].[ProSelectCount]
    @StuId int
AS
BEGIN
    select COUNT(*) from Enrollment where StudentID=@StuId
END

 

代码如下：

 Type t = typeof(int);

 sqlParameter[] params = new SqlParameter[1];

 params[0]= new sqlParameter("@stuid",1);

 var results = db.Database.SqlQuery(t,"exec ProSelectCount @stuId",params).Cast<int>().First();

 

3.执行增删改

  CREATE PROCEDURE [dbo].[ProDel]
    @stuId int,
    @courseId int
AS
BEGIN
    DELETE FROM [WLFSchool].[dbo].[Enrollment] 
    where StudentID=@stuId andCourseID=@courseId
END

 

这个用的是操作数据库 返回受影响行数

  sqlParameter[] params = new SqlParameter[2];

   params[0]= new sqlParameter("@stuid",1);

   params[1]= new sqlParameter("@coursId",2);

  int i = db.Database.ExecuteSqlCommand("exec ProDel @stuId,@courseId" ,params);

 

4.其他方法

   

 var query = db.DataBase.SqlQueryForDaynamic("select * from view_student ");

    foreach(dynamic item in d){

      var s = item.LastName;

    }