Linq存储过程返回XML [英] Linq stored procedure to return XML

问题描述

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

存储过程（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实体框架：

  using（DBContext db = new DBContext （））
 {
 var ProductList = await db.Database.ExecuteSqlCommandAsync（exec xml_test）; 
} 
  

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

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

注意：我也尝试过这样的方法： ExecuteSqlCommand，SqlQuery 没有帮助。

解决方案

在 MSDN ：

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

所以 ExecuteSqlCommand 为插入，更新，删除，它意味着单个非查询的行受影响的数量。所以 ExecuteSqlCommand 不适合查询。

这也是一个常见的问题，因为实体框架无法支持存储过程返回值开箱即用，因为EF是一个ORM，而不是SQL替换。在 Model First 中检查以下链接以获取类似的问题：

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

这与 ExecuteNonQuery ：

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

解决方案：
，您需要使用 Database.SqlQuery< TElement> 方法：

  var ProductList = db .Database.SqlQuery<〜应变g（exec xml_test）。ToList（）; 
  

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

Stored procedure (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:

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

Here the ProductList list is returning -1.

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

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

解决方案

Based on 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.

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.

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:

Getting data from stored procedure with Entity Framework

And this with ExecuteNonQuery:

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

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

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

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