EF8 – 行 SQL 返回非实体
EF8 新变化概述 – 支持行 SQL 返回非实体。
1 EF8 中的新增功能
在 EF8 中，现在可以包含任何返回任何可映射 C#/.NET 类型的行 SQL 查询，而无需在 EF 模型中包含该类型。 过去，需要在 EF 模型中包含该类型。 实际结果是，执行任意 SQL 语句或存储过程比以前容易得多。
可以使用 SqlQuery [3] 或 SqlQueryRow [4] 执行此类查询。 结果的类型为 System.Linq.IQueryable<TResult>，并且在大多数情况下可以进行进一步的 LINQ 操作。 这是这两个扩展方法的签名
//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);
让我们通过几个例子来演示新功能。
2 教程示例 - 示例 1
2.1 使用 Northwind 数据库
对于我们的示例，我们将使用著名的 Northwind 数据库 for SqlServer。 请注意，我们不会创建任何 EF 模型，它将是完全空的。 这就是为了展示新的方法，即使没有 EF 模型，我们仍然可以执行 SQL 查询和存储过程并进行一些 LINQ。
以下是我们的 EF 模型上下文的样子
//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 行 SQL 查询返回非实体标量
自 EF7 以来，就可以编写返回非实体标量（参见 [1]）的行 SQL 查询。 这里有一些例子。
//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,
请注意，在示例 1.2 和 1.3 中，为了将 LINQ 应用于结果，我们需要将输出列命名为“Value”。
2.3 行 SQL 查询返回非实体类型
自 EF8 以来，就可以编写返回任何可映射非实体类型的行 SQL 查询。 这里有一些例子。
//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)
*/
请注意，在上面的示例中，映射类型的属性需要对应于结果集中值的名称，在我们的上述情况下，即表中列的名称。 通常，使用的类型不需要与数据库中的任何表或视图匹配。
请注意，在示例 1.5 中，我们依赖于 SqlQuery 方法返回 IQueryable 这一事实，该方法可以进行进一步的 LINQ 表达式处理。
2.4 存储过程返回非实体类型
执行存储过程只是行 SQL 执行的一种特殊情况。 这里有一些例子。
//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)
*/
文档 [2] 建议尝试对类似上述表达式返回的结果使用 LINQ 可能会出现问题。
2.5 插入行 SQL 查询返回非实体类型
SqlQuery 方法可以使用字符串插值来参数化查询。 第一眼看起来，在“SQL 注入”问题方面它是不安全的，但实际上，它是安全的，因为在后台，实际上是正确地完成了参数化。
//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 参数化行 SQL 查询返回非实体类型
如果你想完全控制 SQL 查询的参数化，可以使用方法 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 一些实际应用
现在，由于我们比以前更容易运行行 SQL 查询，让我们看看一些实际应用。 我们将展示一些行 SQL 查询来实现以下目的
- 查找数据库中的表数
- 查找数据库中的视图数
- 查找数据库中的存储过程数
- 查找数据库文件占用的内存
- 查找完整的 SqlServer 版本信息
- 查找简短的 SqlServer 版本信息
- 查找 SqlServer 版本信息
- 查找当前数据库名称
有趣的是，以下代码将适用于任何 EF 模型/数据库上下文。 这是我们的例子
//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 完整代码
由于大多数人喜欢可以复制粘贴的代码，这里是示例的完整代码。
4.1 示例 1
/* 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 示例 2
/* 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 参考资料
[1] https://learn.microsoft.com/en-us/ef/core/querying/sql-queries#querying-scalar-(non-entity)-types
SQL 查询，查询标量（非实体）类型
[2] https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#raw-sql-queries-for-unmapped-types
EF Core 8 中的新增功能，未映射类型的原始 SQL 查询
[3] https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.sqlquery?view=efcore-8.0
RelationalDatabaseFacadeExtensions.SqlQuery<TResult> 方法
[4] https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.sqlqueryraw?view=efcore-8.0
RelationalDatabaseFacadeExtensions.SqlQueryRaw<TResult> 方法