linq2sqlC#:如何从更改模式名称的表中进行查询

Question

我有一个Web服务，它试图连接到桌面会计应用程序的数据库。

它有相同名称但具有不同模式名称的表，例如：

[DatabaseName].[202001].[CustomerCredit]
[DatabaseName].[202002].[CustomerCredit]
.
.
.
[DatabaseName].[202014].[CustomerCredit]
[DatabaseName].[202015].[CustomerCredit]
[DatabaseName].[202016].[CustomerCredit]
...
..
[DatabaseName].[2020xx].[CustomerCredit]

模式名称的格式为[Year+IncrementalNumber]，如[202014]、[202015]、[202016]等。

每当我想在数据库中查询客户信用信息时，如果202016是我数据库中的最新模式，我应该从编号最大的模式(如[DatabaseName].[202016].[CustomerCredit])中获取信息。

注意：在会计应用程序数据库中创建新架构没有规则，完全由会计应用程序的用户决定，安装在不同位置的每个应用程序实例可能具有不同数量的架构。

因此，当我开发Web服务时，我不知道在开发之前连接到哪个模式。在运行时，我可以从它的表中找到正确的模式进行查询，但我不知道如何在查询中获取具有正确模式名称的表信息。我通常会创建一个linq-to-sql-dbml类，并使用它的定义从数据库中读取信息，但我不知道如何用这种方式管理模式更改？

DBML设计器管理的Scehma名称如下：

[global::System.Data.Linq.Mapping.TableAttribute(Name="[202001].CustomerCredit")]

然而，由于我的应用程序可以在运行时检索模式名称，我不知道如何在特殊情况下修复表声明。它在ADO.NET中很容易处理，但我不知道它在Linq2SQL:中的等价物

select count(*)  from [" + Variables.FinancialYearSchemaName + "].CustomerCredit  where SFC_Status = 100;

Answer 1

最终，否：大多数ORM不希望在运行时更改模式，因此包括EF和LINQ到SQL在内的大多数ORM都不支持这种情况。一个可能的选项是有不同的连接字符串，每个连接字符串都有不同的用户帐户，每个都在数据库中配置了不同的默认架构，并用与所需帐户匹配的连接字符串或连接初始化DB上下文。然后，如果EF向RDBMS请求[CustomerCredit]，它将首先查找该帐户的模式([202014].[CustomerCredit])。在这种情况下，您可能应该避免有[202014].[CustomerCredit]，以防止混淆。然而，这是一个相当棘手和丑陋的解决方案。但是它应该起作用。

或者，您将不得不对数据访问进行更多的控制，本质上是编写自己的SQL(可能是用令牌替换模式，这本身就有问题)。

Answer 2

该模式本质上是CustomerCredit表的手动分区。最好的解决方案是让分区对所有用户透明。代码不应该知道数据是如何分区的。

数据库解决方案

数据库解决方案的好处是，它们对用户透明或几乎透明，并且需要最少的维护

表分区

干净的解决方案是使用表分区，使不同的分区对所有用户透明。表分区曾经只是企业版的一项功能，但自SQL Server 2016 SP1(甚至Express)以来，它在所有版本中都可用。这意味着它在所有仍处于主流支持的版本中都是免费的。

该表根据一个函数(如基于日期的函数)进行分区，并存储在不同的文件中。只要可能，查询优化器就可以检查分区边界和查询条件，并且只使用包含相关数据的文件。例如，在日期分区表中，包含日期过滤器的查询只能搜索相关的分区。

分区视图

另一个选项(至少从2000年开始可用)是使用partitionend视图，本质上是一个UNION ALL视图，它组合了所有表分区，例如：

SELECT <select_list1>  
FROM [202001].[CustomerCredit]
UNION ALL  
SELECT <select_list2>  
FROM [202002].[CustomerCredit]
UNION ALL  
...  
SELECT <select_listn>  
FROM Tn;

EF可以将实体映射到视图而不是表。如果满足可更新视图的标准，则分区视图本身将是可更新的，并且将对正确的表进行任何修改。

查询优化器可以利用表上的CHECK约束，一次只搜索一个表，类似于分区表的工作方式。

代码解决方案

这需要原始SQL查询，以及每次进行更改时识别正确表/模式的方法。每当表分区发生更改时，无论是代码修改还是配置文件中的更改，都需要对应用程序进行修改。

在所有情况下，一个查询一次只能从一个表读取

保留ADO。NET

一种可能性是继续使用ADO。NET，替换查询模板中的表/架构名称。如果需要，代码必须映射到对象，就像它已经做过的那样。

EF原始SQL

另一种是使用EF的原始SQL功能，例如EF Core的FromSqlRaw，以ADO的方式从特定的表中进行查询。NET会。好处是EF将把查询结果映射到对象。在EF Core中，原始查询可以与LINQ运算符组合：

var query=$"select * from [DatabaseName].[{schemaName}].[CustomerCredit]"
var credits = context.CustomerCredits
.FromSqlRaw(query)
.Where(...)
.ToList();

Dapper

另一种选择是使用Dapper或另一个带有特殊查询的微ORM，类似于ADO。NET，并将结果映射到对象：

var query=$"select * from [DatabaseName].[{schemaName}].[CustomerCredit] where customerID=@ID";
var credits=connection.Query<CustomerCredit>(query,new {ID=someID});