I've built a small application that uses data from a SQL Server database of a third party. They have a structure like this to link their objects together:
A table called 'Objecten' that keeps the most common data, like the ObjectId, Unid, Name, Description, ... All these objects have an 'ObjectType' column, which is a VARCHAR/string like: for example 'VRIJ1OBJECT
', 'VRIJ2OBJECT
', 'HARDWARE
'
The related data is stored in a table with these names: 'VRIJ1OBJECTEN
', 'VRIJ2OBJECTEN
', 'HARDWARE
'.
In my (ASP.NET Core) code, I'm using EF Core 2.0. I try to return a (paged) list of objects with some of their related data, but I have no idea how to join on a table using a string name.
So currently, when I'm returning 10 objects for a page, I'm doing 11 queries. 1 to get the 10 objects and 1 for each object to get the related data from the table according to their ObjectType. This takes about 3 seconds, while 1 query only takes about 200ms. The API should be alot faster.
Some code:
private async Task<(List<ObjectViewModel> objecten, int totaalAantalObjecten)> _getTopDeskObjectenAsync(string q = null, string sort = "Naam-", int take = 0, int skip = 0, string e = null, bool qall = false, string categorie = "", RolstoelZoekenVM rolstoelZoekenVm = null, bool aotCategorie = false, AotZoekenVM aotZoekenVm = null)
{
var objecten = await _db.Object.Where(o => o.Status != -1).ToListAsync();
... (paging, filtering, ...) ...
var returnObjecten = new List<ObjectViewModel>();
foreach (var o in objecten){
returnObjecten.Add(await _dbObjectToViewModelAsync(o));
}
return (returnObjecten, totalCount);
}
private async Task<ObjectViewModel> _dbObjectToViewModelAsync(TopDeskDatabase.Object o)
{
var vrijObject = await GetDbVrijObjectAsync(o);
return new ObjectViewModel
{
... filling up the VM properties ...
}
}
// THIS should be done by the SQL Server in the query
public async Task<IVrijobject> GetDbVrijObjectAsync(TopDeskDatabase.Object o)
{
switch(o.Type.ToLower())
{
case "vrij1object":
return await _db.Vrij1object.FirstOrDefaultAsync(d => d.Objectid == o.Unid);
case "vrij2object":
return await _db.Vrij2object.FirstOrDefaultAsync(d => d.Objectid == o.Unid);
case "vrij3object":
return await _db.Vrij3object.FirstOrDefaultAsync(d => d.Objectid == o.Unid);
case "vrij4object":
return await _db.Vrij4object.FirstOrDefaultAsync(d => d.Objectid == o.Unid);
case "vrij5object":
return await _db.Vrij5object.FirstOrDefaultAsync(d => d.Objectid == o.Unid);
case "hardware":
return await _db.Hardware.FirstOrDefaultAsync(d => d.Objectid == o.Unid);
case "inventaris":
return await _db.Inventaris.FirstOrDefaultAsync(d => d.Objectid == o.Unid);
case "telefonie":
return await _db.Telefonie.FirstOrDefaultAsync(d => d.Objectid == o.Unid);
}
throw new InvalidDataException($"Object '{o.RefNaam}' is van type '{o.Type}', welke niet VRIJxOBJECT, INVENTARIS, TELEFONIE of HARDWARE is!");
}
You could create a query with LEFT OUTER JOIN
for each possible return type, store the resulting objects for each type and then compress the data client side by casting to the IVrijobject
.
See the following example with 2 types (Note I created it with EF6, so there might be some changes required for EF Core)
public class ObjectModel
{
public int ID { get; set; }
public int ObjectTypeID { get; set; }
[StringLength(10)]
public string ObjectTypeDiscriminator { get; set; }
}
public interface IObjectTypeModel
{
int ID { get; set; }
string Data { get; set; }
}
// ObjectTypeDiscriminator = "Type1"
public class ObjectType1Model : IObjectTypeModel
{
public int ID { get; set; }
[StringLength(100)]
public string Data { get; set; }
}
// ObjectTypeDiscriminator = "Type2"
public class ObjectType2Model : IObjectTypeModel
{
public int ID { get; set; }
[StringLength(100)]
public string Data { get; set; }
}
class DbC : DbContext
{
public DbC()
{
}
public DbSet<ObjectModel> Objects { get; set; }
public DbSet<ObjectType1Model> Type1Objects { get; set; }
public DbSet<ObjectType2Model> Type2Objects { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
}
}
Test program
class Program
{
static void Main(string[] args)
{
Database.SetInitializer(new DropCreateDatabaseAlways<DbC>());
// initialize some test data
using (var db = new DbC())
{
var t1_1 = db.Type1Objects.Add(new ObjectType1Model { Data = "Object T1 1" });
var t1_2 = db.Type1Objects.Add(new ObjectType1Model { Data = "Object T1 2" });
var t2_1 = db.Type2Objects.Add(new ObjectType2Model { Data = "Object T2 1" });
var t2_2 = db.Type2Objects.Add(new ObjectType2Model { Data = "Object T2 2" });
db.SaveChanges();
db.Objects.Add(new ObjectModel { ObjectTypeID = t1_1.ID, ObjectTypeDiscriminator = "Type1" });
db.Objects.Add(new ObjectModel { ObjectTypeID = t1_2.ID, ObjectTypeDiscriminator = "Type1" });
db.Objects.Add(new ObjectModel { ObjectTypeID = t2_1.ID, ObjectTypeDiscriminator = "Type2" });
db.Objects.Add(new ObjectModel { ObjectTypeID = t2_2.ID, ObjectTypeDiscriminator = "Type2" });
db.SaveChanges();
}
// fresh context for query demonstration
using (var db = new DbC())
{
db.Database.Log = x => Console.WriteLine(x);
var result =
from o in db.Objects
join t1 in db.Type1Objects on new { Discriminator = o.ObjectTypeDiscriminator, ObjectTypeID = o.ObjectTypeID } equals new { Discriminator = "Type1", ObjectTypeID = t1.ID } into types1
join t2 in db.Type2Objects on new { Discriminator = o.ObjectTypeDiscriminator, ObjectTypeID = o.ObjectTypeID } equals new { Discriminator = "Type2", ObjectTypeID = t2.ID } into types2
from t1 in types1.DefaultIfEmpty()
from t2 in types2.DefaultIfEmpty()
select new
{
Obj = o,
T1 = t1,
T2 = t2,
};
foreach (var item in result)
{
// only one concrete type will have a non-null value
var T = (IObjectTypeModel)item.T1 ?? item.T2;
Console.WriteLine("{0,20}{1,20}", item.Obj.ObjectTypeDiscriminator, T.Data);
}
}
Console.ReadKey();
}
}
Output:
Opened connection at 06.12.2017 10:25:34 +01:00
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[ObjectTypeID] AS [ObjectTypeID],
[Extent1].[ObjectTypeDiscriminator] AS [ObjectTypeDiscriminator],
[Extent2].[ID] AS [ID1],
[Extent2].[Data] AS [Data],
[Extent3].[ID] AS [ID2],
[Extent3].[Data] AS [Data1]
FROM [dbo].[ObjectModels] AS [Extent1]
LEFT OUTER JOIN [dbo].[ObjectType1Model] AS [Extent2]
ON ([Extent1].[ObjectTypeDiscriminator] = N'Type1') AND ([Extent1].[ObjectTypeID] = [Extent2].[ID])
LEFT OUTER JOIN [dbo].[ObjectType2Model] AS [Extent3]
ON ([Extent1].[ObjectTypeDiscriminator] = N'Type2') AND ([Extent1].[ObjectTypeID] = [Extent3].[ID])
-- Executing at 06.12.2017 10:25:34 +01:00
-- Completed in 6 ms with result: SqlDataReader
Type1 Object T1 1
Type1 Object T1 2
Type2 Object T2 1
Type2 Object T2 2
Closed connection at 06.12.2017 10:25:34 +01:00
As you can see, the generated query will use separate result columns for each object, which might or might not be less efficient than merging the result based on a discriminator (I didn't investigate this aspect). If your Unid
is unique enough, then you don't need the Discriminator
part of the join condition.