Linq stored procedure for XML return

Question

I am using the first Entity Framework approach. I want to call a stored procedure from the DbContext class and get the 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 returns -1.

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

Note. I also tried using the following methods: ExecuteSqlCommand, SqlQuery without help.

Answer 1

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 in an inserted or updated table, the return value includes the number of rows affected by both the insert and update operations, as well as the number of rows affected by the trigger or triggers. For all other types of operators, the return value is -1. If a rollback occurs, the return value is -1.

So ExecuteSqlCommand returns int for queries like Insert , Update , Delete , which means the number of rows affected by one query. Therefore, ExecuteSqlCommand not suitable for queries.

This is also a common problem because the Entity Framework cannot support stored return values ​​from the box, and this is because EF is an ORM and not a replacement for SQL. Check the following link for a similar problem in the first model :

Retrieving data from a stored procedure using the Entity Framework

And this is with ExecuteNonQuery :

ExecuteNonQuery returns -1 when using sql COUNT, despite the query string

Solution : for queries you need to use the Database.SqlQuery<TElement> method:

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

Answer 2

I think you can use SQLQuery as follows:

 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 a stored procedure, you need to add SET NOCOUNT ON; after BEGIN ;).

 CREATE PROCEDURE [dbo].[xml_test] AS BEGIN SET NOCOUNT ON; SELECT * from dbo.AspNetUsers FOR XML RAW; END 

Answer 3

Database.ExecuteSqlCommand executes the commands used for the CRUD operation, not the queries.

Using Database.SqlQuery for queries. It will return elements of the specified type, but xml not a primitive type and is probably the reason LINQ is not working. Try cast xml in the stored procedure before nvarchar(max) , this will be the type of the string.

. Therefore, your stored procedure should look like this:

  CREATE PROCEDURE xml_test AS BEGIN DECLARE @xml1 xml SET @xml1 = (SELECT * from Product FOR XML RAW) SELECT CAST(@xml1 as nvarchar(max)) END 

Answer 4

As mentioned by shA.t " FOR XML ". However, when using one thing, you need to truncate the / XML line (returned after calling the function via EF) of about 2 thousand characters. To process this script, you can take a look at this one . Also, if the code design allows, you can even use Ouptput Options with the Enitity Framework .