Advanced

Execute Raw SQL Queries in EF Core

EF Core provides the DbSet.FromSql() method to execute raw SQL queries and get the results as entity objects.

var users = _context.Users
    .FromSql("SELECT * FROM Users")
    .ToList();

Limitations

• The SQL query must return all the columns of the table. e.g.,

context.Users.FromSql("SELECT username, email FROM Users).ToList();

will throw an exception.

• The SQL query cannot include JOIN queries to get related data. Instead Include() method can be used to load related entities after FromSql() method.

• Scalar properties are those where actual values are contained in the entities. Normally a scalar property will map to a database field.

• Navigation properties help to navigate from one entity to another entity directly in the code.

The Mapping will have the information on how the Conceptual Models are mapped to Storage Models.

Conceptual Models are the model classes which contain the relationships. These are independent of the database design.

Storage Models are our database design models, which contains database tables, views, stored procedures and keys with relationships.

Is the repository pattern useful with Entity Framework Core?The Reformed Programmer