EF中的动态SQL存储过程 [英] Dynamic SQL stored proc in EF

问题描述

 

 

我是EF新手。我只是想尝试使用它。我有一个像这样的存储过程

I am new to EF. I am just trying to work with it. I have a stored proc like this

ALTER PROCEDURE [dbo].[SearchCustomer]
 @LastName VARCHAR(50) = NULL,
 @FirstName VARCHAR(50) = NULL,
 @PhoneNumber VARCHAR(50) = NULL,
 @City VARCHAR(50) = NULL
 AS
BEGIN
 SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(max)
DECLARE @WhereCondition nvarchar(1000)

SET @WhereCondition = ' WHERE 1=1 '

IF @LastName IS NOT NULL
BEGIN
 SET @WhereCondition = @WhereCondition + ' AND (CUST.LastName LIKE ''' + replace(@LastName, '''', '''''') + '%'')'
END
IF @FirstName IS NOT NULL
BEGIN
 SET @WhereCondition = @WhereCondition + ' AND (CUST.FirstName LIKE ''' + replace(@FirstName, '''', '''''')+ '%'')'
END
IF @PhoneNumber IS NOT NULL
BEGIN
 SET @WhereCondition = @WhereCondition + ' AND PHN.Phone LIKE ''' + @PhoneNumber + '%'''
END
IF @City IS NOT NULL
BEGIN
 SET @WhereCondition = @WhereCondition + ' AND CUST.CustomerID IN (SELECT CustomerID FROM tblAddress WHERE City LIKE ''' + @City + '%'')'
END
SET @SQL = '
SELECT TOP 50(CUST.LastName + '', '' +CUST.FirstName) AS CustName, PHN.Phone, CUST.CustomerID, Address1, State, City
 from tblCustomer CUST 
LEFT OUTER JOIN tblAddress ADR on CUST.CustomerID = ADR.CustomerID
LEFT OUTER JOIN tblPhone PHN on CUST.CustomerID = PHN.CustomerID ' 
SET @SQL = @SQL + @WhereCondition + ' Order By CUST.LastName, CUST.FirstName'
EXEC sp_executesql @SQL
END

它适用于传统的东西。这是一个搜索过程。我简化了它。最初它有10个参数和更多表连接。

It works fine with traditional stuff. This is a search proc. I simplified it. Originally it has 10 parameters and more table joins.

我将此作为函数添加到EF模型。我能够执行它并看到它在SQL事件探查器中运行。但是我如何阅读数据包。它是一个数据集。 

I Added this as function to EF model. I am able to execute it and see that it ran in SQL profiler. But How do I read the databack. It is a dataset. 

我们可以通过代码中的方法将这些数据加载到对象集合中吗？我可以给字体结束数据集还是对象集合？

Can we load this data through a method in code to an object collection? Can I give the font end either data set or object collection?

 

推荐答案

EF不支持数据集。

您可以使用SP和EntityClient来读取相关数据。

以下链接将帮助您：
http://msdn.microsoft.com/en-us/library/bb896274.aspx

另外，您可以将SP作为FunctionImport映射到您的上下文。

有关详细信息，请阅读以下链接：

http://blogs.microsoft.co.il/blogs/gilf/archive/ 2009/03/06 / mapping-stored-procedures-to-objectcontext-methods.aspx

http://blogs.microsoft.co.il/blogs/gilf /archive/2009/03/13/mapping-stored-procedure-results-to-a-custom-entity-in-entity-framework.aspx

我希望它会帮助你。

这篇关于EF中的动态SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！