1

I created ASP.NET 5.0 Core-Web-API project and use package Microsoft.EntityFrameworkCore version 5.0.13. Additionally I created a stored procedure with 2 parameters of type DATE. The procedure runs in sql management studio without errors.

var p1 = new SqlParameter("@From", System.Data.SqlDbType.DateTime);
p1.Value = new DateTime(2021, 11, 07);
var p2 = new SqlParameter("@To", System.Data.SqlDbType.DateTime);
p1.Value = new DateTime(2021, 12, 07);
List<MapTableDimvw> myList = myDbContext.MyTable.FromSqlRaw("EXEC myProc @From, @To", p1, p2).ToList();
Hölderlin
  • 424
  • 1
  • 3
  • 16
  • 1
    Don't use `FromSqlInterpolated` in this case. EF tries to make a parameter of everything enclosed by '{}'. – Gert Arnold Jan 03 '22 at 10:58
  • Does this answer your question? [How to execute an .SQL script file using c#](https://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp) – Scircia Jan 03 '22 at 15:24
  • @Scircia don't think so. I can run procedures in EF as well. But I don't know how to execute `new SqlCommand()` in EF via `FromRawSql` or `FromSqlInterpolated`. – Hölderlin Jan 03 '22 at 16:58
  • just making sure whether you didn't copy \ paste your code properly here, or simply missed the error : you call .Value on p1 twice (instead of calling .Value on p2 after creating the SqlParameter) – Ran Mitelman Jan 05 '22 at 19:50

1 Answers1

1

I think you can do with the following :

var blogs = context.myTable
.FromSqlRaw($"Select Id, colA, colB From myTable")
.ToList();
// or AsEnumerable();
var blogs = context.myTable
.FromSqlRaw($"Select Id, colA, colB From myTable")
.AsEnumerable();

Source: Raw SQL Queries

Otherwise, you can try to add something like this :

var simple = _context.myTable.FromSqlInterpolated($"Select Id, colA, colB From myTable").ToList();

Regards.

Flood
  • 78
  • 6
  • The problem is not that he cannot run the query, but that the query is in a file and cannot run it when he reads the statement out of the file. – Scircia Jan 03 '22 at 15:22
  • Ok, but if you do that : String sql = "your sql query"; var simple = myDbContext.myTable.FromSqlInterpolated("sql"); Is that working? It's just for understand if the problem is in the reading of the text file, or in the interpretation of the query. – Flood Jan 03 '22 at 15:37
  • Thx for help. As Gert Arnold mentioned and also explained [here](https://www.pmichaels.net/tag/fromsqlinterpolated/) I can't use pre-build string for `FromSqlInterpolated`. Inspired of your help I tried `stored procedures` instead. But then I run into errors by passing parameters. Ples c my edits. – Hölderlin Jan 04 '22 at 04:17
  • Why you don't do simple that : `string myQueryString = string.Format("EXEC myProc {0}, {1}", p1, p2);` for prepare the SQL query, and after that `List myList = myDbContext.MyTable.FromSqlRaw(myQueryString).ToList();` – Flood Jan 04 '22 at 08:51
  • I edit my question. And resolved the problem as u and Gert Arnold mentioned. – Hölderlin Jan 06 '22 at 07:55