I need to do a UNION in Entity Framework but I need to do only one call to the DB. I know that I can use CONCAT/UNION but it calls the DB for each CONCAT/UNION, and thats not what I want. And I need to use TOP 4. It is too slow.
here is my example...
using (var db = new ApplicationDbContext())
var query1 = "SELECT TOP 4 * FROM Products where CategoryID=1";
var query2 = "SELECT TOP 4 * FROM Products where CategoryID=2";
var query3 = "SELECT TOP 4 * FROM Products where CategoryID=3";
IEnumerable<Product> p1 = db.Database.SqlQuery<Product>(query1);
IEnumerable<Product> p2 = db.Database.SqlQuery<Product>(query2);
IEnumerable<Product> p3 = db.Database.SqlQuery<Product>(query3);
IEnumerable<Feed> all = p1.Union(p2).Union(p3);
If you are bent in doing this using pure LINQ you could do a group by and then top 4 on the values within the group.
var result = db.Products.GroupBy(p => p.CategoryID).SelectMany(g => g.Take(4));
if you are ok with two lines you could also use a compiled query:
var query = CompiledQuery.Compile<ApplicationDbContext, int, int, IQueryable<Product>>(( ctx, filter, top) => ctx.Products.Where(p => p.CategoryID == filter).Take(top)); var result = query(db, 1, 4).ToList().Union(query(db, 2, 4).ToList()).Union(query(db, 3, 4).ToList()).ToList();
This one will generate 3 roundtrips to the database but still return only max 12 records (not as good as the groupby option in my opinion).