目录
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数据库
对于我们的示例，我们将使用著名的SqlServer的Northwind数据库。请注意，我们不会创建任何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开始，可以编写返回非实体标量的行SQL查询（参见[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();
//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的事实，该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] What's New in EF Core 8 | Microsoft Learn
EF Core 8中的新增功能：未映射类型的原始SQL查询
[3] RelationalDatabaseFacadeExtensions.SqlQuery
RelationalDatabaseFacadeExtensions.SqlQuery<TResult>方法
[4] RelationalDatabaseFacadeExtensions.SqlQueryRaw
RelationalDatabaseFacadeExtensions.SqlQueryRaw<TResult>方法
https://www.codeproject.com/Articles/5379996/EF8-Row-SQL-returning-Non-entities
扫一扫
1634
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
