Common query for multiple similar entity types in Entity Framework

Question

I have a scenario where I have 3 types of products in a database and all products have their own separate tables (e.g. Product1, Product2 and Product3). Almost all of the product tables have the same schema. I have the requirement to get separate types of products in different tables.

I currently have 3 methods to get the products, one for each product type:

public List<Product1> GetProduct1Data() {
    //....
    context.Product1.Where(..).Tolist();
}

public List<Product2> GetProduct2Data() {
    //....
    context.Product2.Where(..).Tolist();
}

public List<Product3> GetProduct3Data() {
    //....
    context.Product3.Where(..).Tolist();
}

While calling products I have a WebApi method which accepts product type and calls the respective methods:

public IHttpActionResult GetProducts(ProductType product)
{ 
    ///....
    // Ii have to call repositories according to product parameter
}

Does Entity Framework have any way that I can select a table with only one method?

Answer 1

You can use a generic method with an interface constraint.

When you have these auto-generated POCO classes:

public partial class Product1 {
    public string Column1 {
        get;
        set;
    }

    public string Column2 {
        get;
        set;
    }
}


public partial class Product2 {
    public string Column1 {
        get;
        set;
    }

    public string Column2 {
        get;
        set;
    }
}


public partial class Product3 {
    public string Column1 {
        get;
        set;
    }

    public string Column2 {
        get;
        set;
    }
}

You create an interface for the properties the entity classes have in common...

interface IProduct {
    string Column1 {
        get;
        set;
    }

    string Column2 {
        get;
        set;
    }
}

...which you apply to your generated classes (in new code files - the classes are partial to allow you to do that):

partial class Product1 : IProduct {};
partial class Product2 : IProduct {};
partial class Product3 : IProduct {};

Now you can create a generic method for your query. You can make it an extension method to apply it to your DbSets:

static class ProductExtensions {
    public static List<T> GetProducts<T>(this DbSet<T> products)
        where T : IProduct {

        var productQry =
            from product in products
            where product.Column1 == "Example"
            select product;
        return productQry.ToList();
    }
}

You can use this extension method on your DbSets:

List<Product1> product1List = context.Product1s.GetProducts();
List<Product2> product2List = context.Product2s.GetProducts();
List<Product3> product3List = context.Product3s.GetProducts();

The only restriction that you have is that the columns in your tables really need to have the same name and type. EF does not recognize explicit interface implementations. On the other hand, the tables don't have to be completely identical. You can define an interface for a part of the columns (which have to match) and the rest can be different.

Answer 2

Provided answer is the most elegant solution, but I am thinking an alternative: mapping to a view.

If the various products tables are mainly used for reading and in an aggregated manner (read from many types at once), it may be convenient to create a view over all of them and map to it:

CREATE VIEW ProductAggregated
AS
SELECT 1 AS ProdTypeId, ProdId AS ProductId, Name AS ProductName          -- other product columns may come here
UNION ALL
SELECT 2 AS ProdTypeId, Id AS ProductId, ProdName AS ProductName          -- different columns names may be harmonized with aliasing
-- other product types table may be entered here

Map a POCO to the view:

public class ProductAggregated
{
    public int ProdTypeId { get; set; }
    public string ProductName { get; set; }
    // other properties come here
}

Relevant product types may be defined into an enum:

public enum ProdType
{
    None = 0,                   -- as default value, if needed
    ProdType1 = 1,
    ProdType2 = 2
    ProdType3 = 3
}

You may easily select products based on natural conditions:

// all products
var allProducts = context.ProductAggregateds.ToList();

// all products of certain types
var types = new List<int> { (int)ProdType.ProdType1, (int)ProdType.ProdType3 };
var only13Products = context.ProductAggregateds
    .Where(p => types.Contains(p.ProductTypeId)
    .ToList();

If a new product type is created and the application does not deal with its specific columns, just add an UNION ALL to the view and everything should work fine.