ASP.NET MVC & EF multiple joins scaling-CodePudding

I am using ASP.NET MVC and Entity Framework to connect to several databases from a single API. I'm currently refactoring some of the legacy code to ease server load (we are expecting a large uptick in the close future).

There are several controllers with a similar problem that is consuming a large chunk of CPU power each time generating specific objects to return. The method currently used is to create an object of each table and then construct an object out of the retrieved tables.

Like this:

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;
}

A recent optimization I did was to remove extra data from the query and set defaults for when there's missing data and just select what was needed, in this example Table1 has 200 fields and I only need a few, so this cuts down time from around 180ms to 10ms:

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.

CodePudding user response：

Credit to "Kirk Wolf" for the answer. By adding associations in my data model, I was able to create a very similar result to the 2nd snippet of code in the question. After doing this on a (smaller) query and running a few benchmarks it seems the performance went from 20ms average to 5ms average. A much appreciated boost that should translate into much higher time savings for larger controllers. Steps:

Add associations to data model
Recompile

Access the objects as follows

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);
}

As a final note here are some details of my tests:

The controller joined data from 6 tables and did a minimal amount of processing on it (3 fields only).
The tables have a total of 487 fields.
The returned fields are a total of 22, with 2 being generated in the controller and 1 being modified in the controller.
The total time to prepare the object to return decreased from 20ms average to 5ms average.