Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the difference between ExecuteSqlCommand vs SqlQuery ? when doing a db access?

Tags:

I have had a couple of suggestions on how to access data from my database:

var allMyIds     = context.Database.ExecuteSqlCommand("select id from AspNetUserLogins");  var allMyIds     = context.Database.SqlQuery<string>("select id from AspNetUserLogins"); 

Can someone explain the difference between these if there is any?

like image 944
Samantha J T Star Avatar asked Jun 16 '14 16:06

Samantha J T Star


People also ask

What is the difference between SQL command and query?

A statement is any text that the database engine recognizes as a valid command. As of SQL-92 : An SQL-statement is a string of characters that conforms to the format and syntax rules specified in this international standard. A query is a statement that returns a recordset (possibly empty).

How can I directly execute SQL queries in LINQ?

Add a LINQ to SQL class file. Drag and drop the respective table. Now, copy this code in the main method. We are creating an instance of sample datacontext class and then we are using this ExecuteQuery method to execute the SQL query.

Which of the following method is used to execute the raw SQL query to the database in EF core?

Entity Framework Core provides the DbSet. FromSql() method to execute raw SQL queries for the underlying database and get the results as entity objects.


1 Answers

The SqlQuery method allows you to return entities from the database. Where as ExecuteSqlCommand just runs the command and returns the status code from the DB.

More here

SqlQuery (Emphasis Mine)

Creates a raw SQL query that will return elements of the given type. The type can be any type that has properties that match the names of the columns returned from the query, or can be a simple primitive type. The type does not have to be an entity type. The results of this query are never tracked by the context even if the type of object returned is an entity type. Use the SqlQuery method to return entities that are tracked by the context. As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter. context.Database.SqlQuery(typeof(Post), "SELECT * FROM dbo.Posts WHERE Author = @p0", userSuppliedAuthor); Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string. context.Database.SqlQuery(typeof(Post), "SELECT * FROM dbo.Posts WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));

ExecuteSqlCommand Return Type: int

Executes the given DDL/DML command against the database. As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter. context.Database.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @p0", userSuppliedAuthor); Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string. context.Database.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));

like image 116
TheNorthWes Avatar answered Oct 12 '22 00:10

TheNorthWes