linq2sql C#: How to query from a table with changing schema name

Question

I have a webservice which tries to connect to a database of a desktop accounting application.

It have tables with same name but with different schema names such as:

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

Schema name is in format [Year+IncrementalNumber] such as [202014], [202015],[202016] and etc.

Whenever I want to query customer credit information in database, I should fetch information from schema with biggest number such as [DatabaseName].[202016].[CustomerCredit] if 202016 is latest schema in my db.

Note: Creation of new schema in accounting application database have no rules and is completely decided by user of accounting application and every instance of application installed on different place may have different number of schemas.

So when I'm developing my webservice I have no idea to connect to which schema prior to development. In run-time I can find correct schema to query from its tables but I don't know how to manage to fetch table information with correct schema name in query. I ususally creat a linq-to-sql dbml class and use its definitions to read information from db but I don't know how to manage schema change in this way?

DBML designer manage Scehma names like this:

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

However since my app can retrieve schema name in run time, I don't know how to fix table declaration in my special case. It is so easy to handle in ADO.NET but I don't know its equivalent in Linq2SQL:

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

Answer 1

Ultimately, no: most ORMs do not expect the schema change to vary at runtime, so most - including EF and LINQ-to-SQL do not support this scenario. One possible option would be to have different connection strings, each with different user accounts, that each has a different default schema configured at the database - and intialize your DB-context with a connection-string or connection that matches the required account. Then if EF asks the RDBMS for [CustomerCredit], it will look first in that account's schema ([202014].[CustomerCredit]). You should probably avoid having a [202014].[CustomerCredit] in that scenario, to prevent confusion. This is, however, a pretty hacky and ugly solution. But... it should work.

Alternatively, you would have to take more control over the data access, essentially writing your own SQL (presumably with a token replacement for the schema, which has problems of its own).

Answer 2

That schema is essentially a manual partitioning of the CustomerCredit table. The best solution would one that makes partitioning transparent to all users. The code shouldn't know how the data is partitioned.

Database Solutions

The benefit of database solutions is that they are transparent or almost transparent to users and require minimal maintenance

Table Partitioning

The clean solution would be to use table partitioning, making the different partitions transparent to all users. Table partitioning used to be an Enterprise-only feature but it became available in all editions since SQL Server 2016 SP1, even Express. This means it's free in all versions still in mainstream support.

The table is partitioned based on a function (eg a date based function) and stored in different files. Whenever possible, the query optimizer can check the partition boundaries and the query conditions and use only the file that contains the relevant data. Eg in a date-partitioned table, queries that contain a date filter can search only the relevant partitions.

Partitioned views

Another option, available since 2000 at least, is to use partitionend views, essentially a UNION ALL view that combines all table partitions, eg :

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

EF can map entities to views instead of tables. If the criteria for updatable views are met, the partitioned view itself will be updatable and any modifications will be made to the correct table.

The query optimizer can take advantage of CHECK constraints on the tables to search only one table at a time, similar to how partitioned tables work.

Code solutions

This requires raw SQL queries, and a way to identify the correct table/schema each time a change is made. It requires modifications to the application each time the table partitioning changes, whether those are code modifications, or changes in a configuration file.

In all cases, one query can only read from one table at a time

Keep ADO.NET

One possibility is to keep using ADO.NET, replacing the table/schema name in a query template. The code will have to map to objects if needed, the same way it already did.

EF Raw SQL

Another, is to use EF's raw SQL features, eg EF Core's FromSqlRaw to query from a specific table , the same way ADO.NET would. The benefit is that EF will map the query results to objects. In EF Core, the raw query can be combined with LINQ operators :

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

Dapper

Another option is to use Dapper or another micro-ORM with an ad-hoc query, similar to ADO.NET, and map the results to objects:

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