EF8 – Row SQL returning Non-entities

Mark Pelf

5.00/5 (2 votes)

1 Apr 2024CPOL3 min read

5.4K

Overview of new changes to EF8 – support for Row SQL returning Non-entities.

In EF8 now it is possible to include any row SQL query that is returning any C#/.NET type, without the need to include that type in the EF model. Executing arbitrary SQL statements or Stored Procedures is much easier than before.

Download source

1 New in EF8

In EF8 now it is possible to include any row SQL query that is returning any mappable C#/.NET type, without the need to include that type in the EF model. In the past, that type needed to be included in the EF model. The practical result is that executing arbitrary SQL statements or Stored Procedures is much easier than before.

Such queries can be executed using SqlQuery [3] or SqlQueryRow [4]. The result is of type System.Linq.IQueryable<TResult> and in most cases can be subject to further LINQ manipulation. Here are the signatures of these two extension methods:

//SqlQuery method
//please note it returns IQueryable...
public static System.Linq.IQueryable<TResult> SqlQuery<TResult> (
this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade, 
FormattableString sql);

//SqlQueryRaw method
//please note it returns IQueryable...
public static System.Linq.IQueryable<TResult> SqlQueryRaw<TResult> (
this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade, 
string sql, params object[] parameters);

Let us demo new features via a few examples.

2 Tutorial examples - Example1

2.1 Using Northwind database

For our examples, we will use the famous Northwind database for SqlServer. Just notice, that we will not create any EF model, it will be completely empty. That is to showcase the new approach, that even without EF model, we can still execute SQL queries and Stored Procedures and do some LINQ.

Here is what our EF model context looks like:

//NorthwindContext.cs==================================================
public partial class NorthwindContext : DbContext
{
    public NorthwindContext(DbContextOptions<NorthwindContext> options)
        : base(options)
    {
        //yes, this is compleatly empty 
    }
}

//NorthwindContextFactory.cs=========================================================
namespace Example1
{
    //we are using this factory pattern to read our configuration 
    //and setup our context with the connection string
    internal class NorthwindContextFactory : IDesignTimeDbContextFactory<NorthwindContext>
    {
        static NorthwindContextFactory()
        {
            IConfiguration config = new ConfigurationBuilder()
               .SetBasePath(Directory.GetCurrentDirectory())
               .AddJsonFile("appsettings.json", true, true)
               .Build();

            connectionString = config["ConnectionStrings:NorthwindConnection"];
            Console.WriteLine("ConnectionString:" + connectionString);
        }

        static string? connectionString = null;
        public NorthwindContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<NorthwindContext>();

            optionsBuilder.UseSqlServer(connectionString);

            return new NorthwindContext(optionsBuilder.Options);
        }
    }
}

2.2 Row SQL query returning non-entity scalar

Since EF7 it is possible to write row SQL queries that are returning non-entity scalar (see[1]). Here are some examples.

//Example 1.1
//Example of "Row SQL query returning non-entity scalar"
List<string> top3Territories = ctx.Database.SqlQuery<string>($"SELECT TOP (3) [TerritoryID] FROM [Territories]").ToList();
//result:
//top3Territories: 01581, 01730, 01833,

//Example 1.2
//Example of "Row SQL query returning non-entity scalar + LINQ usage"
//if you are using LINQ after, usage of "AS [Value]" is mandatory
int numberOfTeritories = ctx.Database.SqlQuery<int>($"SELECT COUNT(*) AS [Value] FROM [Territories]").SingleOrDefault();
//result:
//numberOfTeritories: 53

//Example 1.3
//Example of "Row SQL query returning non-entity scalar + LINQ usage"
//if you are using LINQ after, usage of "AS [Value]" is mandatory
List<string> top5TerritoriesOrdered = ctx.Database.SqlQuery<string>($"SELECT TOP (5) [TerritoryID] AS [Value] FROM [Territories]")
    .OrderByDescending(p => p).ToList();
//result:
//top5TerritoriesOrdered: 98104, 98052, 98004, 95060, 95054,

Please note that in examples 1.2 and 1.3 in order to apply LINQ to results, we needed to name the output column “Value”.

2.3 Row SQL query returning non-entity type

Since EF8 it is possible to write row SQL queries that are returning any mappable non-entity type. Here are some examples.

//please note that this class in not an entity
//it has no commection to NorthwindContext of any kind
public class Territory
{
    public string? TerritoryID { get; set; }
    public string? TerritoryDescription { get; set; }
    public int RegionID { get; set; }
};

//Example 1.4
//Example of "Row SQL query returning non-entity type"
List<Territory> top3TerritoriesType = ctx.Database.SqlQuery<Territory>($"SELECT TOP (3) * FROM [Territories]").ToList();
/*result:
top3Territories:
(01581, Westboro, 1)
(01730, Bedford, 1)
(01833, Georgetow, 1)
*/

//Example 1.5
//Example of "Row SQL query returning non-entity type+ LINQ usage"
List<Territory> top3TerritoriesFiltered = ctx.Database.SqlQuery<Territory>($"SELECT  * FROM [Territories]")
    .Where(p=>p.RegionID==4).ToList();  
/* result:
top3TerritoriesFiltered:
(29202, Columbia, 4)
(30346, Atlanta, 4)
(31406, Savannah, 4)
(32859, Orlando, 4)
(33607, Tampa, 4)
(72716, Bentonville, 4)
(75234, Dallas, 4)
(78759, Austin, 4)
*/

Please note that in the above examples properties of the mapped type need to correspond to names of values in the result set, which in our case above are names of columns in the table. The type used does not need to match any table or view in the database in general.

Please note that in example 1.5 we are relying on the fact that SqlQuery method returns IQueryable, which can be subject to further LINQ expressions.

2.4 Stored Procedure returning non-entity type

Executing stored procedures is just a special case of row SQL execution. Here are some examples.

//please note that this class in not an entity
//it has no commection to NorthwindContext of any kind
public class SalesItem
{
    public DateTime? ShippedDate { get; set; }
    public int OrderID { get; set; }
    public Decimal? Subtotal { get; set; }
    public string? Year { get; set; }
};

//Example 1.6
//Example of "Stored Procedure returning non-entity type"
List<SalesItem> salesByYear = ctx.Database.SqlQuery<SalesItem>(
    $"exec [Sales by Year]  @Beginning_Date = '1996-01-01' ,@Ending_Date ='1996-07-15'").ToList();
/*result:
salesByYear:
(1996-07-10 12:00:00 AM,10249,1863.4000,1996)
(1996-07-11 12:00:00 AM,10252,3597.9000,1996)
(1996-07-12 12:00:00 AM,10250,1552.6000,1996)
(1996-07-15 12:00:00 AM,10251,654.0600,1996)
(1996-07-15 12:00:00 AM,10255,2490.5000,1996)
*/

Documentation [2] suggests there might be issues trying to use LINQ on results returned from expressions like the above.

2.5 Interpolated Row SQL query returning non-entity type

The SqlQuery method can use string interpolation to parametrize the query. At the first moment, it looks like it is not safe regarding the “SQL injection” problem, but in reality, it is safe since in the background real parametrization is done properly.

//Example 1.7
//Example of "Interpolated Row SQL query returning non-entity type"
int RegionID = 4;
List<Territory> top3TerritoriesFiltered2 = ctx.Database.SqlQuery<Territory>(
    $"SELECT  * FROM [Territories] WHERE RegionID={RegionID}")
    .ToList();
/* result:
top3TerritoriesFiltered2:
(29202,Columbia,4)
(30346,Atlanta,4)
(31406,Savannah,4)
(32859,Orlando,4)
(33607,Tampa,4)
(72716,Bentonville,4)
(75234,Dallas,4)
(78759,Austin,4)
*/

//Example 1.8
//Example of "Interpolated Stored Procedure returning non-entity type"
var start = new DateOnly(1996, 1, 1);
var end = new DateOnly(1996, 7, 15);
List<SalesItem> salesByYear2 = ctx.Database.SqlQuery<SalesItem>(
    $"exec [Sales by Year]  @Beginning_Date = {start} ,@Ending_Date ={end}").ToList();
/*result:
salesByYear2:
(1996-07-10 12:00:00 AM,10249,1863.4000,1996)
(1996-07-11 12:00:00 AM,10252,3597.9000,1996)
(1996-07-12 12:00:00 AM,10250,1552.6000,1996)
(1996-07-15 12:00:00 AM,10251,654.0600,1996)
(1996-07-15 12:00:00 AM,10255,2490.5000,1996)
*/

2.6 Parametrized Row SQL query returning non-entity type

If you want to have complete control over the parametrization of your SQL query, you can use the method SqlQueryRow.

//Example 1.9
//Example of "Parametrized Row SQL query returning non-entity type"
var RegionIDParam = new SqlParameter("@RegionID", 4);
List<Territory> top3TerritoriesFiltered3 = ctx.Database.SqlQueryRaw<Territory>(
    $"SELECT  * FROM [Territories] WHERE RegionID=@RegionID", RegionIDParam)
    .ToList();
/* result:
top3TerritoriesFiltered3:
(29202,Columbia,4)
(30346,Atlanta,4)
(31406,Savannah,4)
(32859,Orlando,4)
(33607,Tampa,4)
(72716,Bentonville,4)
(75234,Dallas,4)
(78759,Austin,4)
*/

//Example 1.10
//Example of "Parametrized Stored Procedure returning non-entity type"
var startParam = new SqlParameter("@start", new DateOnly(1996, 1, 1));
var endParam = new SqlParameter("@end", new DateOnly(1996, 7, 15));
var params1= new SqlParameter[] { startParam, endParam };
List<SalesItem> salesByYear3 = ctx.Database.SqlQueryRaw<SalesItem>(
    $"exec [Sales by Year]  @Beginning_Date = @start ,@Ending_Date =@end", 
    params1).ToList();
/*result:
salesByYear3:
(1996-07-10 12:00:00 AM,10249,1863.4000,1996)
(1996-07-11 12:00:00 AM,10252,3597.9000,1996)
(1996-07-12 12:00:00 AM,10250,1552.6000,1996)
(1996-07-15 12:00:00 AM,10251,654.0600,1996)
(1996-07-15 12:00:00 AM,10255,2490.5000,1996)
*/

3 Some practical applications

Now, since we have the ability to run row SQL queries much easier than before, let us see some practical applications. We will show some row SQL queries to achieve the following:

finding the number of tables in the database
finding the number of views in the database
finding the number of stored procedures in the database
finding memory taken by database files
finding full SqlServer version info
finding short SqlServer version info
finding SqlServer edition info
finding current database name

Interestingly, the below code will work for any EF model/Database Context. Here are our examples:

//Example 2.1
//finding number of tables in the database
FormattableString sql21 = $"SELECT COUNT(*) AS [Value] FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
int numberOfTablesInDatabase = ctx.Database.SqlQuery<int>(sql21).SingleOrDefault();
//result
//numberOfTablesInDatabase: 13

//Example 2.2
//finding number of views in the database
FormattableString sql22 = $"SELECT COUNT(*) AS [Value] FROM INFORMATION_SCHEMA.VIEWS ";
int numberOfViewsInDatabase = ctx.Database.SqlQuery<int>(sql22).SingleOrDefault();
//result
//numberOfViewsInDatabase: 16

//Example 2.3
//finding number of stored procedures in the database
FormattableString sql23 = $"SELECT COUNT(*) AS [Value] FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'";
int numberOfStorProcsInDatabase = ctx.Database.SqlQuery<int>(sql23).SingleOrDefault();
//result
//numberOfStorProcsInDatabase: 7

//please note that this class in not an entity
//it has no connection to AnyDBContext of any kind
public class DbFileInfo
{
    public string? DbName { get; set; }
    public string? FileName { get; set; }
    public string? FileType { get; set; }
    public Decimal CurrentSizeMB { get; set; }
    public Decimal FreeSpaceMB { get; set; }

};

//Example 2.4
//finding memory taken by database files
FormattableString sql24 =
    @$"SELECT DB_NAME() AS DbName, name AS FileName, type_desc AS FileType, 
        size/128.0 AS CurrentSizeMB, 
        size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
        FROM sys.database_files WHERE type IN (0,1)";
List<DbFileInfo> dbMemoryInfo = ctx.Database.SqlQuery<DbFileInfo>(sql24).ToList();
/*result
dbMemoryInfo:
(DbName:Northwind,FileName:Northwind,FileType:ROWS,CurrentSizeMB:8.000000,FreeSpaceMB:1.187500)
(DbName:Northwind,FileName:Northwind_log,FileType:LOG,CurrentSizeMB:72.000000,FreeSpaceMB:63.093750)
*/

//Example 2.5
//finding full SqlServer version info
FormattableString sql25 = $"Select @@version AS [Value]";
string? sqlServerVersionFull = ctx.Database.SqlQuery<string>(sql25).SingleOrDefault();
/*result
sqlServerVersionFull: 
Microsoft SQL Server 2019 (RTM-CU22-GDR) (KB5029378) - 15.0.4326.1 (X64)
Aug 18 2023 14:05:15
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22621: ) (Hypervisor)
*/

//Example 2.6
//finding short SqlServer version info
FormattableString sql26 = $"Select SERVERPROPERTY('productversion') AS [Value]";
string? sqlServerVersionShort = ctx.Database.SqlQuery<string>(sql26).SingleOrDefault();
/*result
sqlServerVersionShort: 15.0.4326.1
*/

//Example 2.7
//finding  SqlServer edition info
FormattableString sql27 = $"Select SERVERPROPERTY('edition') AS [Value]";
string? sqlServerEdition = ctx.Database.SqlQuery<string>(sql27).SingleOrDefault();
/*result
sqlServerEdition: Developer Edition (64-bit)
*/

//Example 2.8
//finding  current database name
FormattableString sql28 = $"Select DB_NAME() AS [Value]";
string? currentDatabaseName = ctx.Database.SqlQuery<string>(sql28).SingleOrDefault();
/*result
currentDatabaseName: Northwind
*/

4 Full Code

Since most people like code they can copy-paste, here is the full code of the examples.

4.1 Example1

/* appsettings.json============================================*/
{
  "ConnectionStrings": {
    "NorthwindConnection": "Data Source=.;User Id=sa;Password=dbadmin1!;Initial Catalog=Northwind;Encrypt=False"
  }
}

//NorthwindContext.cs==================================================
public partial class NorthwindContext : DbContext
{
    public NorthwindContext(DbContextOptions<NorthwindContext> options)
        : base(options)
    {
        //yes, this is compleatly empty 
    }
}

//NorthwindContextFactory.cs=========================================================
namespace Example1
{
    //we are using this factory pattern to read our configuration 
    //and setup our context with the connection string
    internal class NorthwindContextFactory : IDesignTimeDbContextFactory<NorthwindContext>
    {
        static NorthwindContextFactory()
        {
            IConfiguration config = new ConfigurationBuilder()
               .SetBasePath(Directory.GetCurrentDirectory())
               .AddJsonFile("appsettings.json", true, true)
               .Build();

            connectionString = config["ConnectionStrings:NorthwindConnection"];
            Console.WriteLine("ConnectionString:" + connectionString);
        }

        static string? connectionString = null;
        public NorthwindContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<NorthwindContext>();

            optionsBuilder.UseSqlServer(connectionString);

            return new NorthwindContext(optionsBuilder.Options);
        }
    }
}

//Program.cs=================================================
namespace Example1
{
    internal class Program
    {
        //please note that this class in not an entity
        //it has no commection to NorthwindContext of any kind
        public class Territory
        {
            public string? TerritoryID { get; set; }
            public string? TerritoryDescription { get; set; }
            public int RegionID { get; set; }
        };

        //please note that this class in not an entity
        //it has no commection to NorthwindContext of any kind
        public class SalesItem
        {
            public DateTime? ShippedDate { get; set; }
            public int OrderID { get; set; }
            public Decimal? Subtotal { get; set; }
            public string? Year { get; set; }
        };

        static void Main(string[] args)
        {
            try
            {
                Console.WriteLine("Hello, from Example1");

                using NorthwindContext ctx = new NorthwindContextFactory().CreateDbContext(new string[0]);

                Console.WriteLine("Example 1.1 ==================================");
                //Example 1.1
                //Example of "Row SQL query returning non-entity scalar"
                List<string> top3Territories = ctx.Database.SqlQuery<string>($"SELECT TOP (3) [TerritoryID] FROM [Territories]").ToList();
                string text11 = "top3Territories: ";
                foreach (string id in top3Territories)
                {
                    text11 += id + ", ";
                }
                Console.WriteLine(text11);
                //result:
                //top3Territories: 01581, 01730, 01833,

                Console.WriteLine("Example 1.2 ==================================");
                //Example 1.2
                //Example of "Row SQL query returning non-entity scalar + LINQ usage"
                //if you are using LINQ after, usage of "AS [Value]" is mandatory
                int numberOfTeritories = ctx.Database.SqlQuery<int>($"SELECT COUNT(*) AS [Value] FROM [Territories]").SingleOrDefault();
                Console.WriteLine("numberOfTeritories: " + numberOfTeritories.ToString());
                //result:
                //numberOfTeritories: 53

                Console.WriteLine("Example 1.3 ==================================");
                //Example 1.3
                //Example of "Row SQL query returning non-entity scalar + LINQ usage"
                //if you are using LINQ after, usage of "AS [Value]" is mandatory
                List<string> top5TerritoriesOrdered = ctx.Database.SqlQuery<string>($"SELECT TOP (5) [TerritoryID] AS [Value] FROM [Territories]")
                    .OrderByDescending(p => p).ToList();
                string text13 = "top5TerritoriesOrdered: ";
                foreach (string id in top5TerritoriesOrdered)
                {
                    text13 += id + ", ";
                }
                Console.WriteLine(text13);
                //result:
                //top5TerritoriesOrdered: 98104, 98052, 98004, 95060, 95054,

                Console.WriteLine("Example 1.4 ==================================");
                //Example 1.4
                //Example of "Row SQL query returning non-entity type"
                List<Territory> top3TerritoriesType = ctx.Database.SqlQuery<Territory>($"SELECT TOP (3) * FROM [Territories]").ToList();
                string text14 = "top3Territories: ";
                foreach (Territory ter in top3TerritoriesType)
                {
                    text14 += $"\n ({ter.TerritoryID?.Trim()},{ter.TerritoryDescription?.Trim()},{ter.RegionID.ToString()})";
                }
                Console.WriteLine(text14);
                /*result:
                top3Territories:
                (01581, Westboro, 1)
                (01730, Bedford, 1)
                (01833, Georgetow, 1)
                */

                Console.WriteLine("Example 1.5 ==================================");
                //Example 1.5
                //Example of "Row SQL query returning non-entity type+ LINQ usage"
                List<Territory> top3TerritoriesFiltered = ctx.Database.SqlQuery<Territory>($"SELECT  * FROM [Territories]")
                    .Where(p=>p.RegionID==4).ToList();  
                string text15 = "top3TerritoriesFiltered: ";
                foreach (Territory ter in top3TerritoriesFiltered)
                {
                    text15 += $"\n ({ter.TerritoryID?.Trim()},{ter.TerritoryDescription?.Trim()},{ter.RegionID.ToString()})";
                }
                Console.WriteLine(text15);
                /* result:
                top3TerritoriesFiltered:
                (29202, Columbia, 4)
                (30346, Atlanta, 4)
                (31406, Savannah, 4)
                (32859, Orlando, 4)
                (33607, Tampa, 4)
                (72716, Bentonville, 4)
                (75234, Dallas, 4)
                (78759, Austin, 4)
                */

                Console.WriteLine("Example 1.6 ==================================");
                //Example 1.6
                //Example of "Stored Procedure returning non-entity type"
                List<SalesItem> salesByYear = ctx.Database.SqlQuery<SalesItem>(
                    $"exec [Sales by Year]  @Beginning_Date = '1996-01-01' ,@Ending_Date ='1996-07-15'").ToList();
                string text16 = "salesByYear: ";
                foreach (SalesItem item in salesByYear)
                {
                    text16 += $"\n ({item.ShippedDate?.ToString()},{item.OrderID.ToString()}," +
                        $"{item.Subtotal?.ToString()},{item.Year?.Trim()})";
                }
                Console.WriteLine(text16);
                /*result:
                salesByYear:
                (1996-07-10 12:00:00 AM,10249,1863.4000,1996)
                (1996-07-11 12:00:00 AM,10252,3597.9000,1996)
                (1996-07-12 12:00:00 AM,10250,1552.6000,1996)
                (1996-07-15 12:00:00 AM,10251,654.0600,1996)
                (1996-07-15 12:00:00 AM,10255,2490.5000,1996)
                */

                Console.WriteLine("Example 1.7 ==================================");
                //Example 1.7
                //Example of "Interpolated Row SQL query returning non-entity type"
                int RegionID = 4;
                List<Territory> top3TerritoriesFiltered2 = ctx.Database.SqlQuery<Territory>(
                    $"SELECT  * FROM [Territories] WHERE RegionID={RegionID}")
                    .ToList();
                string text17 = "top3TerritoriesFiltered2: ";
                foreach (Territory ter in top3TerritoriesFiltered2)
                {
                    text17 += $"\n ({ter.TerritoryID?.Trim()},{ter.TerritoryDescription?.Trim()},{ter.RegionID.ToString()})";
                }
                Console.WriteLine(text17);
                /* result:
                top3TerritoriesFiltered2:
                (29202,Columbia,4)
                (30346,Atlanta,4)
                (31406,Savannah,4)
                (32859,Orlando,4)
                (33607,Tampa,4)
                (72716,Bentonville,4)
                (75234,Dallas,4)
                (78759,Austin,4)
                */

                Console.WriteLine("Example 1.8 ==================================");
                //Example 1.8
                //Example of "Interpolated Stored Procedure returning non-entity type"
                var start = new DateOnly(1996, 1, 1);
                var end = new DateOnly(1996, 7, 15);
                List<SalesItem> salesByYear2 = ctx.Database.SqlQuery<SalesItem>(
                    $"exec [Sales by Year]  @Beginning_Date = {start} ,@Ending_Date ={end}").ToList();
                string text18 = "salesByYear2: ";
                foreach (SalesItem item in salesByYear2)
                {
                    text18 += $"\n ({item.ShippedDate?.ToString()},{item.OrderID.ToString()}," +
                        $"{item.Subtotal?.ToString()},{item.Year?.Trim()})";
                }
                Console.WriteLine(text18);
                /*result:
                salesByYear2:
                (1996-07-10 12:00:00 AM,10249,1863.4000,1996)
                (1996-07-11 12:00:00 AM,10252,3597.9000,1996)
                (1996-07-12 12:00:00 AM,10250,1552.6000,1996)
                (1996-07-15 12:00:00 AM,10251,654.0600,1996)
                (1996-07-15 12:00:00 AM,10255,2490.5000,1996)
                */

                Console.WriteLine("Example 1.9 ==================================");
                //Example 1.9
                //Example of "Parametrized Row SQL query returning non-entity type"
                var RegionIDParam = new SqlParameter("@RegionID", 4);
                List<Territory> top3TerritoriesFiltered3 = ctx.Database.SqlQueryRaw<Territory>(
                    $"SELECT  * FROM [Territories] WHERE RegionID=@RegionID", RegionIDParam)
                    .ToList();
                string text19 = "top3TerritoriesFiltered3: ";
                foreach (Territory ter in top3TerritoriesFiltered3)
                {
                    text19 += $"\n ({ter.TerritoryID?.Trim()},{ter.TerritoryDescription?.Trim()},{ter.RegionID.ToString()})";
                }
                Console.WriteLine(text19);
                /* result:
                top3TerritoriesFiltered3:
                (29202,Columbia,4)
                (30346,Atlanta,4)
                (31406,Savannah,4)
                (32859,Orlando,4)
                (33607,Tampa,4)
                (72716,Bentonville,4)
                (75234,Dallas,4)
                (78759,Austin,4)
                */

                Console.WriteLine("Example 1.10 ==================================");
                //Example 1.10
                //Example of "Parametrized Stored Procedure returning non-entity type"
                var startParam = new SqlParameter("@start", new DateOnly(1996, 1, 1));
                var endParam = new SqlParameter("@end", new DateOnly(1996, 7, 15));
                var params1= new SqlParameter[] { startParam, endParam };
                List<SalesItem> salesByYear3 = ctx.Database.SqlQueryRaw<SalesItem>(
                    $"exec [Sales by Year]  @Beginning_Date = @start ,@Ending_Date =@end", 
                    params1).ToList();
                string text110 = "salesByYear3: ";
                foreach (SalesItem item in salesByYear3)
                {
                    text110 += $"\n ({item.ShippedDate?.ToString()},{item.OrderID.ToString()}," +
                        $"{item.Subtotal?.ToString()},{item.Year?.Trim()})";
                }
                Console.WriteLine(text110);
                /*result:
                salesByYear3:
                (1996-07-10 12:00:00 AM,10249,1863.4000,1996)
                (1996-07-11 12:00:00 AM,10252,3597.9000,1996)
                (1996-07-12 12:00:00 AM,10250,1552.6000,1996)
                (1996-07-15 12:00:00 AM,10251,654.0600,1996)
                (1996-07-15 12:00:00 AM,10255,2490.5000,1996)
                */
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: "+ex.ToString());
            }
        }
    }
}

4.2 Example2

/* appsettings.json============================================*/
{
  "ConnectionStrings": {
    "NorthwindConnection": "Data Source=.;User Id=sa;Password=dbadmin1!;Initial Catalog=Northwind;Encrypt=False"
  }
}

//AnyDBContext.cs==================================================
public partial class AnyDBContext : DbContext
{
    public AnyDBContext(DbContextOptions<AnyDBContext> options)
        : base(options)
    {
        //yes, this is compleatly empty 
    }
}

//AnyDBContextFactory.cs=========================================================
namespace Example2
{
    //we are using this factory pattern to read our configuration 
    //and setup our context with the connection string
    internal class AnyDBContextFactory : IDesignTimeDbContextFactory<AnyDBContext>
    {
        static AnyDBContextFactory()
        {
            IConfiguration config = new ConfigurationBuilder()
               .SetBasePath(Directory.GetCurrentDirectory())
               .AddJsonFile("appsettings.json", true, true)
               .Build();

            connectionString = config["ConnectionStrings:NorthwindConnection"];
            Console.WriteLine("ConnectionString:" + connectionString);
        }

        static string? connectionString = null;
        public AnyDBContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<AnyDBContext>();

            optionsBuilder.UseSqlServer(connectionString);

            return new AnyDBContext(optionsBuilder.Options);
        }
    }
}

//Program.cs=================================================
namespace Example2
{
    internal class Program
    {
        //please note that this class in not an entity
        //it has no connection to AnyDBContext of any kind
        public class DbFileInfo
        {
            public string? DbName { get; set; }
            public string? FileName { get; set; }
            public string? FileType { get; set; }
            public Decimal CurrentSizeMB { get; set; }
            public Decimal FreeSpaceMB { get; set; }

        };

        static void Main(string[] args)
        {
            try
            {
                Console.WriteLine("Hello, from Example2");

                using AnyDBContext ctx = new AnyDBContextFactory().CreateDbContext(new string[0]);

                Console.WriteLine("Example 2.1 ==================================");
                //Example 2.1
                //finding number of tables in the database
                FormattableString sql21 = $"SELECT COUNT(*) AS [Value] FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
                int numberOfTablesInDatabase = ctx.Database.SqlQuery<int>(sql21).SingleOrDefault();
                Console.WriteLine("numberOfTablesInDatabase: " + numberOfTablesInDatabase.ToString());
                //result
                //numberOfTablesInDatabase: 13

                Console.WriteLine("Example 2.2 ==================================");
                //Example 2.2
                //finding number of views in the database
                FormattableString sql22 = $"SELECT COUNT(*) AS [Value] FROM INFORMATION_SCHEMA.VIEWS ";
                int numberOfViewsInDatabase = ctx.Database.SqlQuery<int>(sql22).SingleOrDefault();
                Console.WriteLine("numberOfViewsInDatabase: " + numberOfViewsInDatabase.ToString());
                //result
                //numberOfViewsInDatabase: 16

                Console.WriteLine("Example 2.3 ==================================");
                //Example 2.3
                //finding number of stored procedures in the database
                FormattableString sql23 = $"SELECT COUNT(*) AS [Value] FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'";
                int numberOfStorProcsInDatabase = ctx.Database.SqlQuery<int>(sql23).SingleOrDefault();
                Console.WriteLine("numberOfStorProcsInDatabase: " + numberOfStorProcsInDatabase.ToString());
                //result
                //numberOfStorProcsInDatabase: 7

                Console.WriteLine("Example 2.4 ==================================");
                //Example 2.4
                //finding memory taken by database files
                FormattableString sql24 =
                    @$"SELECT DB_NAME() AS DbName, name AS FileName, type_desc AS FileType, 
                        size/128.0 AS CurrentSizeMB, 
                        size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
                        FROM sys.database_files WHERE type IN (0,1)";
                List<DbFileInfo> dbMemoryInfo = ctx.Database.SqlQuery<DbFileInfo>(sql24).ToList();
                string text23 = "dbMemoryInfo: ";
                foreach (DbFileInfo item in dbMemoryInfo)
                {
                    text23 += $"\n (DbName:{item.DbName?.Trim()},FileName:{item.FileName?.Trim()},FileType:{item.FileType?.Trim()}," +
                        $"CurrentSizeMB:{item.CurrentSizeMB.ToString()},FreeSpaceMB:{item.FreeSpaceMB.ToString()})";
                }
                Console.WriteLine(text23);
                /*result
                dbMemoryInfo:
                (DbName:Northwind,FileName:Northwind,FileType:ROWS,CurrentSizeMB:8.000000,FreeSpaceMB:1.187500)
                (DbName:Northwind,FileName:Northwind_log,FileType:LOG,CurrentSizeMB:72.000000,FreeSpaceMB:63.093750)
                */

                Console.WriteLine("Example 2.5 ==================================");
                //Example 2.5
                //finding full SqlServer version info
                FormattableString sql25 = $"Select @@version AS [Value]";
                string? sqlServerVersionFull = ctx.Database.SqlQuery<string>(sql25).SingleOrDefault();
                Console.WriteLine("sqlServerVersionFull: " + sqlServerVersionFull?.ToString());
                /*result
                sqlServerVersionFull: 
                Microsoft SQL Server 2019 (RTM-CU22-GDR) (KB5029378) - 15.0.4326.1 (X64)
                Aug 18 2023 14:05:15
                Copyright (C) 2019 Microsoft Corporation
                Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22621: ) (Hypervisor)
                */

                Console.WriteLine("Example 2.6 ==================================");
                //Example 2.6
                //finding short SqlServer version info
                FormattableString sql26 = $"Select SERVERPROPERTY('productversion') AS [Value]";
                string? sqlServerVersionShort = ctx.Database.SqlQuery<string>(sql26).SingleOrDefault();
                Console.WriteLine("sqlServerVersionShort: " + sqlServerVersionShort?.ToString());
                /*result
                sqlServerVersionShort: 15.0.4326.1
                */

                Console.WriteLine("Example 2.7 ==================================");
                //Example 2.7
                //finding  SqlServer edition info
                FormattableString sql27 = $"Select SERVERPROPERTY('edition') AS [Value]";
                string? sqlServerEdition = ctx.Database.SqlQuery<string>(sql27).SingleOrDefault();
                Console.WriteLine("sqlServerEdition: " + sqlServerEdition?.ToString());
                /*result
                sqlServerEdition: Developer Edition (64-bit)
                */

                Console.WriteLine("Example 2.8 ==================================");
                //Example 2.8
                //finding  current database name
                FormattableString sql28 = $"Select DB_NAME() AS [Value]";
                string? currentDatabaseName = ctx.Database.SqlQuery<string>(sql28).SingleOrDefault();
                Console.WriteLine("currentDatabaseName: " + currentDatabaseName?.ToString());
                /*result
                currentDatabaseName: Northwind
                */



            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.ToString());
            }
        }
    }
}

5 References

[1] https://learn.microsoft.com/en-us/ef/core/querying/sql-queries#querying-scalar-(non-entity)-types
SQL Queries, Querying scalar (non-entity) types

[2] https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#raw-sql-queries-for-unmapped-types
What's New in EF Core 8, Raw SQL queries for unmapped types

[3] https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.sqlquery?view=efcore-8.0
RelationalDatabaseFacadeExtensions.SqlQuery<TResult> Method

[4] https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.sqlqueryraw?view=efcore-8.0
RelationalDatabaseFacadeExtensions.SqlQueryRaw<TResult> Method

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)