Execute sql statement or procedure in .net entity framework core

Question

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

Don't use FromSqlInterpolated in this case. EF tries to make a parameter of everything enclosed by '{}'. — Gert Arnold
– Gert Arnold, Commented Jan 3, 2022 at 10:58
Does this answer your question? How to execute an .SQL script file using c# — Scircia
– Scircia, Commented Jan 3, 2022 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. — Murmulodi
– Murmulodi, Commented Jan 3, 2022 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
– Ran Mitelman, Commented Jan 5, 2022 at 19:50

Flood · Accepted Answer · 2022-01-03 12:24:43Z

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.

edited Jan 3, 2022 at 12:24

answered Jan 3, 2022 at 10:17

Flood

786 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Scircia Over a year ago

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.

Flood Over a year ago

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.

Murmulodi Over a year ago

Thx for help. As Gert Arnold mentioned and also explained here 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.

Flood Over a year ago

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<MapTableDimvw> myList = myDbContext.MyTable.FromSqlRaw(myQueryString).ToList();

Murmulodi Over a year ago

I edit my question. And resolved the problem as u and Gert Arnold mentioned.

Collectives™ on Stack Overflow

Execute sql statement or procedure in .net entity framework core

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related