C# How to use DbContext.Database.SqlQuery with abstract/derived classes

Question

So, my situation is that I'm using EF6 and a Component table using the table per hierarchy pattern. I have an abstract Component class and a few classes that inherit it, such as FirstNameComponent.

The call looks something like this:

var sql = "Select * from Components";
var components = DbContext.Database.SqlQuery<Component>(sql).ToList();

In this case, I must use SqlQuery off either DbContext or DbSet.

As you'd expect, this queries out all the components in the table and tries to map them to the Component class, but since it's abstract, an exception is thrown.

Is there a way to use a custom model binder or something with the discriminator column on the table to tell the SqlQuery call which classes to use for each Component? Or maybe a different way to serialize/deserialize the rows into the Component classes?

Answer 1

You can define a discriminator property inside your abstract class (basically the name of the column you want as the discriminator, as a string)

Say, you have the Component class as an abstract class and the FirstNameComponent is inheriting that.

So, You would like your Component class to look something like -

public abstract class Component
{
    public const string Discriminator = "ComponentType";

    // Other properties/methods
}

Now, your FirstNameComponent class would look like -

public class FirstNameComponent : Component
{
    public const string TypeOfComponent = nameof(FirstNameComponent);

    public override string ComponentType // your discriminator column
    {
        get
        {
            return TypeOfComponent;
        }
    }
    // Other properties/methods
}

and use this property when building your model using fluent API inside your context..

public class ComponentContext: DbContext
{
    public DbSet<FirstNameComponent> FirstNameComponents { get; set; }

    // Other mappings 

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Component>()
            .Map<FirstNameComponent>(
                configuration =>
                configuration.Requires(Component.Discriminator).HasValue(FirstNameComponent.TypeOfComponent))
    }
}

Accordingly, your query would be something like -

var components = ComponentContext.Components.OfType<FirstNameComponent>();

or even -

var components = ComponentContext.Components.Where(c => c is FirstNameComponent).ToList();

UPDATE

Looking at your updated question, here is how you can restructure your query to fetch a particular type of Component -

string sqlQuery = @"Select * from Components Where ComponentType = @componentType";
string componentType = nameof(FirstNameComponent); //Can be made more dynamic

// Pass the type of component you need to cast to as a parameter
SqlParameter parameter = new SqlParameter("@componentType", componentType);

var components = DbContext.Database.SqlQuery<FirstNameComponent>(sqlQuery, parameter);

This should give you the desired result.

UPDATE 2

So, after some more research on this, I found this blog which talks about Polymorphic queries using both Linq to Entities and EntitySQL.

Based on the reference, here is another way you could try out to get all the Components -

string sqlQuery = @"SELECT VALUE c FROM Components AS c";
System.Data.Entity.Core.Objects.ObjectContext objectContext = ((IObjectContextAdapter)ComponentContext).ObjectContext;
System.Data.Entity.Core.Objects.ObjectQuery<Component> objectQuery = objectContext.CreateQuery<Component>(sqlQuery);
List<Component> components = objectQuery.ToList<Component>();

And once you have all the Components, you could just filter out the desired types using a simple Where clause on the ComponentType field -

var firstNameComponents = components.Where(c => c.ComponentType == nameof(FirstNameComponent)).ToList();