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