c# - 使用 Microsoft SQL Server 从动态表中搜索数据

Question

我有一个这样的组织表:

OrgID | OrgInviteCode | OrgName       | Status | ProjectTableName | InsertOn
-------------------------------------------------------------------------------------------
 1    | RC12T67       | Organization1 | Active |  Project1        | 2015-12-19 15:37:43.333
 2    | BC56uI7       | Organization2 | Active |  Project2        | 2015-12-19 15:37:43.333
 3    | ORG1456       | Organization3 | Active |  Project3        | 2015-12-19 15:37:43.333
 4    | ORG2856       | Organization4 | Active |  Project4        | 2015-12-19 15:37:43.333

我有一个存储过程来为项目创建一个动态表。

如果成功创建任何新组织，我们将调用存储过程为该组织创建项目表。

每个组织都有自己的项目表。因此项目表名称对于每个组织都是动态的，它的名称存储在组织表中。

组织 1 --> 项目表

ProjectID | OrgID | ProjectName | ProjectInvideCode |   Address1        | Address2 |  City      |State    |   ZIP      |  Country
-------------------------------------------------------------------------------------------------------------------------------
1         |   1   |  Org1Proj1   |  XJ34590         | 235 Harrison St. |            | Syracuse  | AK      |  23456234    |  US
2         |   1   |  Org1Proj2   |  JKI8907         | 35 Sterling  St. |            | Syracuse  | NY      |  23456456    |  US

Organization2 --> 项目表

ProjectID | OrgID | ProjectName | ProjectInvideCode |   Address1 | Address2 |  City       |State    |   ZIP    |  Country
-------------------------------------------------------------------------------------------------------------------------------
1         |   2   |  Org2Proj1   |  RUIO90          | 90 Ram St. |          | Los Angeles | CA    |  23456234    |  US
2         |   2   |  Org2Proj2   |  KLOP907         | 35 Wide St.|          | Chicago     | IL    |  23456456    |  US

我目前正在整合搜索功能。用户或匿名用户可以根据以下逻辑搜索数据:

• 使用组织名称或组织邀请代码进行搜索。
• 使用项目名称或项目邀请码进行搜索。
• 搜索项目地址、城市、州、国家/地区

我知道查找组织名称和邀请码的搜索结果非常简单，因为所有内容都位于同一个表中。

但由于表名是动态的，获取项目(名称或邀请码)的搜索结果比较复杂。我在 How to fetch data from dynamic multiple tables? 中找到了这个链接，所以我认为这不是一个更好的解决方案，因为搜索需要非常快。

我们之所以根据组织将项目表分开，是因为在我们的要求中他们明确提到“我们有 1000000 个组织，但每个组织有超过 100 万个项目”。希望您理解我们不想将 1000000(组织)* 1 百万 = XXXXXX 项目转储到单个表中的概念。

问题:

• 我们如何才能有效地搜索项目数据？
• 我们的项目分表概念最差吗？您有什么建议可以更好地处理吗？
• C#有什么有效的方法吗？

工具和技术:

• Asp.Net 4.5，C#
• MVC 5
• Entity Framework 代码优先
• SQL Server 2012

Answer 1

我认为你可以创建合并所有项目表的VIEW

SELECT
    REPLACE(
        REPLACE( 
            REPLACE(
            (
                SELECT DISTINCT 'SELECT * FROM Organization O JOIN ' 
                    + ProjectTableName  
                    + ' PT ON O.OrgId = PT.OrgId WHERE O.OrgId = ' + convert(varchar(10), OrgId) 
                    as [text()] FROM Organization
                FOR XML PATH ('DELIMITER')  
            ), '</DELIMITER><DELIMITER>', ' 
            UNION ALL 
            '), '</DELIMITER>', '')
    ,'<DELIMITER>', 'CREATE VIEW Organization_Projects 
AS
')

它在效率上必须与源表查询非常接近。