EF与Stored Proc 连用 Collection_ef initializestoredprocs-优快云博客

本文链接：https://blog.youkuaiyun.com/lglgsy456/article/details/41748723

本文介绍如何使用SQL存储过程在InstagramData数据库中创建一个带有标识字段的临时表PostIdWithIdentity，并提供了两种不同类型的存储过程示例：一种返回标识值，另一种不返回值。此外，还展示了一个获取特定范围内的数据的存储过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

=======Example 1.

Stored Proc

USE [InstagramData]

GO
/****** Object: StoredProcedure [dbo].[CreateTempTableForPostIdWithIdentity] Script Date: 2014/12/5 15:09:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CreateTempTableForPostIdWithIdentity]
-- Add the parameters for the stored procedure here
AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @return int

IF OBJECT_ID('PostIdWithIdentity') IS NOT NULL
DROP TABLE PostIdWithIdentity

CREATE TABLE PostIdWithIdentity
(
Id int IDENTITY (1,1) not null,
PostId nvarchar(255),
Tags nvarchar(MAX),
primary key (Id)
)
truncate table PostIdWithIdentity
insert into PostIdWithIdentity(PostId,Tags) select Id,Tags from [InstagramData].[dbo].[Post]
set @return = @@identity
select @return

END

EF Code:

int? newIdentityValue = db.Database.SqlQuery<Int32>("exec CreateTempTableForPostIdWithIdentity").FirstOrDefault();

=====EXAMPLE 2，no return value

STORE PROC

USE [InstagramData]
GO
/****** Object: StoredProcedure [dbo].[CreateTempTableForPostIdWithIdentity] Script Date: 2014/12/5 15:09:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CreateTempTableForPostIdWithIdentity]
-- Add the parameters for the stored procedure here
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @return int

IF OBJECT_ID('PostIdWithIdentity') IS NOT NULL
DROP TABLE PostIdWithIdentity

CREATE TABLE PostIdWithIdentity
(
Id int IDENTITY (1,1) not null,
PostId nvarchar(255),
Tags nvarchar(MAX),
primary key (Id)
)
truncate table PostIdWithIdentity
insert into PostIdWithIdentity(PostId,Tags) select Id,Tags from [InstagramData].[dbo].[Post]
END

EF CODE:

db.Database.ExecuteSqlCommand("exec CreateTempTableForPostIdWithIdentity");

EXAMPLE 3 ，带参数的

Store Proc

USE [InstagramData]
GO
/****** Object: StoredProcedure [dbo].[GetDataFromPostIdWithIdentity] Script Date: 2014/12/5 15:09:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetDataFromPostIdWithIdentity]
-- Add the parameters for the stored procedure here
@StartWith int,
@Count int

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Select Id,PostId,Tags from PostIdWithIdentity order by ID OffSET @StartWith ROW FETCH NEXT @Count ROWS only
--Select * from PostIdWithIdentity order by ID OffSET 10 ROW FETCH NEXT 10 ROWS only
-- Insert statements for procedure here
--SELECT row_number() over (order by Id asc) as id,Id as PostId,Tags From [InstagramData].[dbo].[Post]
END

EF Code

var data = db.Database.SqlQuery <DataFromPostIdWithIdentity>("exec GetDataFromPostIdWithIdentity @StartWith,@Count", new SqlParameter("StartWith", startWith), new SqlParameter("Count", _getRowCount)).ToList();

public class DataFromPostIdWithIdentity
{
public int Id { set; get; }

public string PostId { get; set; }

public string Tags { get; set; }
}