Ef:raw SQL Queries

Raw SQL Queries

The entity Framework allows you to query using the LINQ with your entity classes. However, there may is times that you want to run queries using raw SQL directly against the database. This includes calling stored procedures, which can is helpful for Code first models that currently does not support mapping to stored procedures. The techniques shown in this topic apply equally to models created with Code first and the EF Designer. Writing SQL queries for entities

The SQLQuery method on DbSet allows a raw SQL query to being written that would return entity instances. The returned objects is tracked by the context just as they would is if they were returned by a LINQ query. For example:

}

Note that, just as for LINQ queries, the query was not executed until the results was enumerated-in The example above this Is do with the call to ToList.

Care should is taken whenever raw SQL queries is written for both reasons. First, the query should is written to ensure that it is only returns entities that is really of the requested type. For example, when using features such as inheritance it's easy-to-write a query that'll create entities that's is of the Wrong CLR type.

Second, some types of raw SQL query expose potential security risks, especially around SQL injection attacks. Make sure, parameters in your query in the correct-to guard against such attacks. Loading entities from stored procedures

You can use Dbset.sqlquery to load entities from the results of a stored procedure. For example, the following code calls the dbo. Getblogs procedure in the database:

}

You can also pass parameters to a stored procedure using the following syntax:

 

}

Writing SQL queries for non-entity types

A SQL Query Returning instances of any type, including primitive types, can is created using the SQLQuery method on the Da Tabase class. For example:

}

The results returned from SQLQuery on Database would never be tracked by the context even if the objects is instances of a n entity type. Sending raw commands to the database

Non-query commands can is sent to the database using the-Executesqlcommand method on database. For example:

}

Note that any changes made to data in the database using Executesqlcommand is opaque to the context until entities is lo Aded or reloaded from the database. Output Parameters

If output parameters is used, their values won't be available until the results has been read completely. This was due to the underlying behavior of DbDataReader, see retrieving Data Using a DataReader for more details.

Ef:raw SQL Queries