Generic query for several similar object types in Entity Framework

Question

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

Currently, I have 3 methods for getting products, one for each type of product:

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

When invoking products, I have a WebApi method that takes a product type and calls the appropriate 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 the general method with interface restriction.

When you have these automatically 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 are creating an interface for properties that have entity classes ...

 interface IProduct { string Column1 { get; set; } string Column2 { get; set; } } 

... which you apply to your generated classes (in new code files - classes are partial so you can do this):

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

Now you can create a generic method for your request. You can do this with an extension method to apply it to your DbSet s:

 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(); } } 

This extension method can be used on DbSet s:

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

The only limitation 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, tables do not have to be completely identical. You can define an interface for part of the columns (which must match), and the rest can be different.

Answer 2

The answer provided is the most elegant solution, but I am thinking of an alternative: matching with the view.

If tables of various products are mainly used for reading and in aggregated form (read from several types at the same time), it may be convenient to create an idea of ​​all of them and compare them with 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 

Match POCO with a view:

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

Appropriate product types can be defined in the listing:

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

You can easily choose 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 type of product is created and the application does not process its specific columns, just add UNION ALL to the view, and everything should work fine.