FromSqlRaw in Entity Framework calling stored procedure, returns no data

Anna Tamaoka 136 Reputation points
2021-11-10T21:52:39.383+00:00

I have a method that creates a CSV of data from SQL. When I run the SQL string in SQL I receive rows, but when I try to run FromSqlRaw in my application, it returns no data. I was going off of the Raw SQL Queries documentation but I can't find what I'm doing wrong.

public RequestAttachment GetExportCsv(string pPms, string pType)  
{  
 //I think I need to use List<object> instead of List<CsvExport> to get the final result into a list of strings to be added to the CSV?  
 List<object> lstExports = _context.CsvExports  
 .FromSqlRaw("EXEC dbo.EEPayableExtract @p_PMS = {0}, @p_Type = {1}", pPms, pType)  
 .ToList<object>();  
  
 //generate csv  
 StringBuilder sb = new();  
 foreach (var item in lstExports)  
 {  
 string[] arrExports = (string[])item;  
 foreach (var data in arrExports)  
 {  
 //Append data with comma(,) separator.  
 sb.Append(data + ',');  
 }  
 //Append new line character.  
 sb.Append("\r\n");  
 }  
 //RequestAttachment is my file model  
 return new RequestAttachment  
 {  
 FileName = "UploadLineItems",  
 Extension = ".csv",  
 DataFiles = Encoding.ASCII.GetBytes(sb.ToString())  
 };  
}  

My DbContext does also declare that my model is keyless, as I saw in some other similar posts.

modelBuilder.Entity<CsvExport>(entity =>  
{  
    entity.HasNoKey();  
});  

I tried just assigning it to a list, but it still does not return data.

var list = _context.CsvExports  
 .FromSqlRaw("EXEC dbo.EEPayableExtract @p_PMS = {0}, @p_Type = {1}", pPms, pType)  
 .ToList();  

I also tried adding .AsNoTracking() as suggested in this post (EF Core Stored Procedure FromSqlRaw Does Not Give Updated Values) and still no data.

List<object> lstExports = _context.CsvExports  
	.FromSqlRaw("EXEC dbo.EEPayableExtract @p_PMS = {0}, @p_Type = {1}", pPms, pType)  
	.AsNoTracking()  
	.ToList<object>();  
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
2,581 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,561 questions
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
424 questions
No comments
{count} votes

Accepted answer
  1. JasonPan - MSFT 1,146 Reputation points Microsoft Employee
    2021-11-11T04:08:37.357+00:00

    Hi @Anna Tamaoka

    I have create a sample project to test, and I can get value by FromSqlRaw.

    I think your code is right, you can follow below steps to troubleshoot.

    1. run the command in your SSMS, check the result.
    2. check the connectionstring in your project.

    My test steps

    1. exec procedure in ssms
      148432-image.png
    2. my sample code
      148433-image.png
    3. result
      148444-image.png

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best Regards,
    Jason


0 additional answers

Sort by: Most helpful