It has been some time since using Entity Framework (Core or Framework), but I always found the SqlQuery() method in EF objects useful. Either I'm missing a reference/NuGet Package that I'm forgetting, the method was removed, or it was just replaced in EFCore 6.
I have my reasons for having the option to execute raw SQL statements. Using LINQ statements from entity properties is handy, but sometimes much too rigid. I like to have the option to use either when necessary.
Am I missing something, the SqlQuery() method just gone, or has it been replaced? If it was removed, I expect there is a very good reason for it. Otherwise, it'd be rather frustrating after finding it so useful.
Once I found the details provided by StackOverflow on similar topics, it only took a few minutes to see how it works.
Using the DbSet<{type}>().FromSqlRaw({string}) method, it is possible to define complex object types in .NET that represent the records returned by a raw SQL query.
- No need for class or property annotations. These can be optional. Just be sure the column names are unique in the query statement and match the property names in the model type.
- The model type still needs to be either declared as a DbSet<> property or configured in the OnModelCreating() method of the DbContext -- a step backwards in my opinion.
- Use the FromSqlRaw() method from either the declared DbSet<> property of that model type, or through the Set<>() method.
- This works with SQL JOIN statements too.
This approach allows for a more dynamic communication between the database and applications. It can be a great way to focus on ironing out the details of the primary processes of the data-access layer for new projects before development even begins. If the columns or property names are unknown, then a classic ADO.NET approach through a custom extension method may be required.
