How to get a Master List from a TPH Table

Question

I am using a Table-Per-Hierarchy (TPH) Structure for my Code-First Models. I have a base model Customer:

public abstract class Customer : IEntity
{
    [Key]
    public int Id { get; set; }

    protected BusinessType CustomerType { get; set; }
}

I have several Models inherit Customer:

public class CommercialCustomer : Customer, IAggregateRoot
{
    public CommercialCustomer()
    {
        CustomerType = BusinessType.Commercial;
    }

    [DisplayName("Customer Name")]
    public string BusinessName { get; set; }        
}


public class ResidentialCustomer : Customer, IAggregateRoot
{
    public ResidentialCustomer()
    {
        CustomerType = BusinessType.Residential;
    }

    [DisplayName("Customer Name")]
    public string CustomerName { get; set; } 
}


public class EducationalCustomer : Customer, IAggregateRoot
{
    public EducationalCustomer()
    {
        CustomerType = BusinessType.Educational;            
    }

    public string SchoolName { get; set; }

    public string Department { get; set; } 
}

Etc.. To get a list of Residential Customers I use:

   IList<ResidentialCustomer> customers = _context.ResidentialCustomers.ToList();

What I need help with is how do I get a Master Customer List as a superset? Calling the .ToList() extension method on Customer just returns the Id property, as expected. Using a SqlDataAdapter I can return a table…

const string cmdString =
            "SELECT Id, CONCAT(SchoolName + ', ' + Department, CustomerName, BusinessName) AS Name FROM Customers ORDER BY Name";

string connectionString = ConfigurationManager.ConnectionStrings["TestContext"].ConnectionString;

SqlConnection connection = new SqlConnection(connectionString);

SqlCommand command = new SqlCommand(cmdString, connection);

SqlDataAdapter adapter = new SqlDataAdapter(command);

DataTable table = new DataTable();

adapter.Fill(table);

All I really need is just the raw content of the Customers Table from the database to fill a ComboBox. Is there no cleaner way by using only EntityFramework?

Answer 1

If you're using TPH in code first, the by-the-book way is to only have one DbSet for the whole hierarchy, where the type of the elements is your base type. Then use .Map to set up conditions for the derived types on the base entity. When querying for derived types, use the .OfType<T>() method on the one entity set to filter by your derived types. The following blurb here shows an example:

https://msdn.microsoft.com/en-us/data/jj591617#2.4