Linq存储过程返回XML。

I am using Entity Framework code-first approach. I want to call a stored procedure from the DbContext class and to get XML output.

我正在使用实体框架代码优先方法。我想从DbContext类调用一个存储过程，并获取XML输出。

Stored procedure (SQL Server):

存储过程(SQL Server):

CREATE PROCEDURE xml_test    
AS
BEGIN
    DECLARE @xml1 xml

    SET @xml1 = (SELECT * from Product FOR XML RAW) 

    SELECT @xml1 AS my_xml
END

LINQ Entity Framework:

LINQ实体框架:

using (DBContext db = new DBContext())
{
    var ProductList = await db.Database.ExecuteSqlCommandAsync("exec xml_test");
}

Here the ProductList list is returning -1.

在这里，ProductList列表返回-1。

I want to get the xml output which is returned by the stored procedure.

我想获取存储过程返回的xml输出。

Note: I have also tried methods like: ExecuteSqlCommand, SqlQuery with no help.

注意:我也尝试过类似的方法:ExecuteSqlCommand, SqlQuery没有帮助。

4 个解决方案

#1

Based on MSDN:

基于MSDN:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

对于UPDATE、INSERT和DELETE语句，返回值是受命令影响的行数。当触发器存在于要插入或更新的表上时，返回值包括受插入或更新操作影响的行数以及受触发器或触发器影响的行数。对于所有其他类型的语句，返回值为-1。如果发生回滚，返回值也是-1。

So ExecuteSqlCommand returns an int for queries like Insert,Update,Delete and it means the number of row affected by a single non-query. So ExecuteSqlCommand is not suitable for querying.

所以ExecuteSqlCommand返回一个int类型的查询，如Insert、Update、Delete，它表示受单个非查询影响的行数。所以ExecuteSqlCommand不适合查询。

Also this is a common problem because Entity Framework cannot support Stored Procedure Return values out of the box and it is because EF is an ORM, not a SQL replacement. Check the following link for similar problem in Model First:

这也是一个常见的问题，因为实体框架不能支持存储过程直接返回值，这是因为EF是ORM，而不是SQL替换。首先检查模型中类似问题的以下链接:

Getting data from stored procedure with Entity Framework

使用实体框架从存储过程获取数据

And this with ExecuteNonQuery:

这与ExecuteNonQuery:

ExecuteNonQuery returning -1 when using sql COUNT despite the query string

尽管使用查询字符串，但使用sql COUNT时，ExecuteNonQuery返回-1

The solution: for queries you need to use Database.SqlQuery<TElement> method:

解决方案:对于查询，需要使用数据库。SqlQuery < TElement >方法:

var ProductList = db.Database.SqlQuery<string>("exec xml_test").ToList();

#2

I think you can use SQLQuery like this:

我认为您可以这样使用SQLQuery:

using (var dbcontext = new DBContext())
{
    //Reading stored procedure results as List<string>
    var r = dbcontext.Database.SqlQuery<string>("EXEC xml_test").ToList(); //Note: EXEC is optional

    //Joining strings to one string that causes in resulting long strings
    var xmlString = string.Join("", r);

    //Now you can load your string to a XmlDocument
    var xml = new XmlDocument();

    //Note: You need to add a root element to your result
    xml.LoadXml($"<root>{xmlString}</root>");
}

Note: To get records from your stored procedure you need to add SET NOCOUNT ON; after BEGIN ;).

注意:要从存储过程中获取记录，需要添加SET NOCOUNT;开始后,)。

CREATE PROCEDURE [dbo].[xml_test] 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * from dbo.AspNetUsers FOR XML RAW;
END

#3

Database.ExecuteSqlCommand executes commands used for CRUD operation, not querying.

数据库。ExecuteSqlCommand执行用于CRUD操作的命令，而不是查询。

Using Database.SqlQuery is for queries. It will return elements of a given type but xml isn't a primitive type and probably it's a reason why LINQ not working. Try cast xml in stored procedure to nvarchar(max) this will be string type.

使用数据库。SqlQuery查询。它将返回给定类型的元素，但xml不是原始类型，这可能是LINQ不能工作的原因之一。在存储过程中尝试将xml转换为nvarchar(max)，这将是字符串类型。

. So your stored procedure should look like:

。因此存储过程应该如下所示:

    CREATE PROCEDURE xml_test    
AS
BEGIN
    DECLARE @xml1 xml

    SET @xml1 = (SELECT * from Product FOR XML RAW) 

    SELECT CAST(@xml1 as nvarchar(max))
END

#4

As mentioned by shA.t "FOR XML" can be used. However one thing to take care of while using is the truncation of string/XML (returned after function call via EF) at around 2k characters, to handle this scenario you can have a look at this. Also if the code design allows, you can even use Ouptput parameters with Enitity Framework.

如前所述,沙。可以使用t“FOR XML”。然而，在使用时需要注意的一件事是字符串/XML(通过EF函数调用后返回)在大约2k个字符处的截断，要处理这个场景，您可以看一下这个。如果代码设计允许，您甚至可以使用Enitity参数。

#1