Entity Framework Core include related data from table by string

asp.net-core c# entity-framework entity-framework-core sql-server

Question

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!");
}
1
0
12/6/2017 8:27:11 AM

Accepted Answer

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.

0
12/6/2017 9:34:29 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow