FromSqlRaw in Entity Framework calling stored procedure, returns no data

Question

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>();  

Answer 1

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
   
2. my sample code
   
3. result
   

---

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