ASP.NETMVC&EF多連接縮放

我正在使用 ASP.NET MVC 和 Entity Framework 從單個 API 連接到多個資料庫。我目前正在重構一些遺留代碼以減輕服務器負載（我們預計在不久的將來會有很大的提升）。

有幾個控制器存在類似問題，每次生成要回傳的特定物件時都會消耗大量 CPU 功率。目前使用的方法是為每個表創建一個物件，然后從檢索到的表中構造一個物件。

像這樣：

public IHttpActionResult Get(string code) 
{
    using (DataEntities entities = new DataEntities()) 
    {
        var table1 = entities.Table1.FirstOrDefault(t1 => t1.ItemCode == code);

        if (table1 == null) 
        {
            return NotFound();
        }

        var table2 = entities.Table2.FirstOrDefault(t2 => t2.ItemId == table1.ItemId);
        var table3 = entities.Table3.FirstOrDefault(t3 => t3.ItemId == table1.ItemId);
        return Ok(new TableValues() 
                      {
                          ItemCode = code,
                          Table1Value = table1.Value;
                          Table2Value = table2.Value;
                          Table3Value = table3.Value;
                      });
    }
}

class TableValues 
{
    public string ItemCode;
    public int Table1Value;
    public int Table2Value;
    public int Table3Value;
}

我最近做的一個優化是從查詢中洗掉額外的資料，并在缺少資料時設定默認值，然后選擇需要的內容，在這個例子中，Table1有 200 多個欄位，我只需要幾個，所以這可以減少大約 180 毫秒的時間到 10 毫秒：

public IHTTPActionResult Get(string code) 
{
    using (DataEntities entities = new DataEntities()) 
    {
        var table1 = entities.Table1 
                             .FirstOrDefault(t1 => t1.ItemCode == code)
                             .Select(t1 => new Table1Short()  
                                               {
                                                   ItemCode = t1.ItemCode,
                                                   Value1 = t1.Value1,
                                                   Value2 = t1.Value2,
                                                   Value3 = t1.Value3
                                               });
        if (table1 == null) 
        {
            return NotFound("Not found in database");
        }

        return Ok(table1);
    }
}

class Table1Short 
{
    public string ItemCode = string.Empty;
    public int Value1 = 0;
    public int Value2 = 0;
    public int Value3 = 0;
}

Is there any way to create an IQueryable with multiple tables and then apply a .Select that constructs the objects using the various joined tables?

The final result would look something like this:

public IHttpActionResult Get(string code) 
{
    using (DataEntities entities = new DataEntities())  
    {
        // THIS LINE WONT WORK AS IS
        var tableValuesQuery = entities.Table1
                                       .Join(entities.Table2 as t2)
                                       .Join(entities.Table3 as t3);

        table1 = tableValuesQuery.FirstOrDefault((t1, t2, t3) => t1.ItemCode == code)
                                 .Select((t1, t2, t3) => new TableValues() 
                                                         {
                                                             ItemCode = t1.ItemCode
                                                             Value1 = t1.Value,
                                                             Value2 = t2.Value,
                                                             Value3 = t3.Value
                                                         });
        // ADDITIONAL CODE TO JOIN TABLES HERE AS "t2" and "t3"
        if (table1 == null) 
        {
            return NotFound("Not found in database");
        }

        return Ok(table1);
    }
}

class TableValues 
{
    public int ItemId = string.Empty;
    public int Table1Value = 0;
    public int Table2Value = 0;
    public int Table3Value = 0;
}

Doing this would increase speed a bit as well as prevent errors on the API user's end from missing data (nulls in non-nullable datatypes). I checked the documentation and found only examples of 2 tables being joined, which I cannot seem to get working properly with 3.

On a final note: I would just create a SQL view and import that into the EF, but that is sadly not an option as modification of some databases is exclusive to the programs that use them.

uj5u.com熱心網友回復：

歸功于“Kirk Wolf”的答案。通過在我的資料模型中添加關聯，我能夠創建與問題中的第二段代碼非常相似的結果。在（較小的）查詢上執行此操作并運行一些基準測驗后，性能似乎從平均 20 毫秒變為平均 5 毫秒。一個非常受歡迎的提升應該轉化為更大的控制器節省更多的時間。腳步：

1. 將關聯添加到資料模型

2. 重新編譯

3. 訪問物件如下

   public IHttpResult Get(string code) {
     var result = entities.Table1.Where(t1 => t1.ItemCode == code)
       .Select(t1 => new TableValues() {
         ItemCode = t1.ItemCode,
         Value1 = t1.Value,
         Value2 = t1.Table2.Value
         Value3 = t1.Table3.Value
       })
       .FirstOrDefault();
   
     if (result == null) {
       return NotFound();
     }
     return Ok(result);
   }
   

作為最后一點，這里是我的測驗的一些細節：

• 控制器連接了 6 個表中的資料并對其進行了最少的處理（僅 3 個欄位）。

• 這些表共有 487 個欄位。

• 回傳的欄位一共22個，其中2個在控制器中生成，1個在控制器中修改。

• 準備物件回傳的總時間從平均 20 毫秒減少到平均 5 毫秒。

標籤：c# asp.net entity-framework join select

上一篇：Mysql將兩個查詢合并為一個

下一篇：從文本中的計數器獲取變數$i