I'm learning ASP.NET Core and I'm building a Web API with SQL Server.
But with it hard to use LINQ because I just know a little about it, and can't use a complex query.
That's why I want to use SQL query for it like
SELECT *
FROM A
WHERE A.ID = 1
How can I use it in ASP.NET Core?
Thank for your help!
You can use ADO with a function like this in asp.net core
public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
SqlCommand cmd = conn.CreateCommand();
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
the full example can found here
You could use the FromSql extension method to begin a LINQ query based on a raw SQL query.
var blogs = context.Blogs
.FromSql("SELECT * FROM Blogs")
.Where(b => b.Id ==1)
.FirstOrDefault();
Or you could also parameterize user input to prevent the possibility of a SQL injection attack being successful.
var blogs= context.blogs.FromSql("SELECT * From Blogs Where Id = {0}", id).FirstOrDefault();
The SQL command can be any valid SQL statement that returns all the required fields of data. It is possible to call stored procedures via the FromSql method.The DbContext exposes a Database property which includes a method called ExecuteSqlCommand. This method returns an integer specifying the number of rows affected by the SQL statement passed to it. Valid operations are INSERT, UPDATE and DELETE. The method is not used for returning entities.
For more details , you could refer to the following links:
