c# - 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.