Arif YILMAZ Arif YILMAZ - 4 months ago 30
C# Question

How to use UNION(concat/union) in Entity Framework with one call?

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

return all.ToList();

how can I do a UNION with one call?


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).