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.