(EFCore Best Practices) Raw SQL with FromSqlRaw for Efficiency

Raw Query Example

Let us suppose that we have to execute a query that selects all columns from a table and gives an alias of, say, Fees to one column called Price - SELECT ID, Name, Price AS Fees FROM Items.

// - - SQL query to be executed direct 

SELECT ID, Name, Price AS Fees FROM Items

The first requirement of C# side is that (1) we must have a model class that contains the exact same compatible properties as the columns sent by the SQL query. (2) the names of the properties must also match the names of the incoming columns. (3) the SQL query cannot bring related data to populate the foreign key properties that we usually have in our code-first model classes.

Video Explanation (see it happen!)

Please watch the following youtube video:

Add a matching model class

We must have a model class that contains all the same exact properties.

Open the Item.cs file and add this model class. We could have added it in a separate file. But since this is a tutorial I have added it in the same file. The model class has three properties - ID, Name and Fees - that match the columns returned by the SQL query.

// -- source code is in the downloads 
// -- attached to this chapter 

// -- add this matching model 

// see the video for [Keyless] attribute 

[Keyless]
public class MyRecord
{
  public int Id { get; set; }

  public string Name { get; set; } = "";

  public int Fees { get; set; }
}

Notice that this model class has been marked with the Keyless attribute. It indicates that this is class is just for holding data - it doesn't have a primary key. It indicates that EF Core should not track its DBSet for updates and deletes.

Source code is in the attached downloads.

Prevent table creation of this entity

The DbSet for this model has to be added to the DbContext class.

Locate the entries for db-set of Items and Invoices. I have provided the source code in the attached downloads. Add an entry for the dbset of this model class.

EF Core will attempt to create a table in the database because the default behavior is to create tables for all dbset. We do not want this to happen.

Override the OnModelCreating method and use the ToView method of the fluent API to map the query to a view. This prevents the table from being created.

// source code is in the downloads 

// prevent creation of table for 
// dbset of raw sql query 

public class MyProjectContext : DbContext
{
   
  // . . . code not shown for brevity 

  public DbSet<MyRecord> MyRecords { get; set; } = default!;

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<MyRecord> (b => b.ToView("sql_v1"));
  }

}

Executing the Query

Now we are ready to execute the query. Open the program.cs file and scroll towards the end. Add a MapGet api for testing purposes. This API will be called from an endpoint, say, "mydata".

  
  // program.cs file 

  // prior code not shown for brevity 

  // source code is in the downloads 

app.MapRazorPages();

app.MapGet("/mydata", (MyProjectContext _ctx) =>
{
  String query = "SELECT ID, Name, Price AS Fees  FROM Items";

  List<MyRecord> alldata = _ctx.MyRecords
                               .FromSqlRaw(query)
                               .ToList();

  return Results.Ok(alldata);
  
});


app.Run();

  

The query in the SQL syntax has been provided by a database expert. The FromSqlRaw method is used to execute the query. The result is obtained in a list. The list is returned as a json by this API.

Run the project and open the "mydata" endpoint. We verify that the json is displayed as expected. Thanks!