How to use Entity Framework with Union and Top N Percent?

Question

I am using code below to get data. Code works fine but it is making too many calls to db. Is there a better and faster way to get data? I have to use TOP N PERCENT and UNION the results. Thanks.

[WebMethod]
    public static IEnumerable<Product> GetData()
    {
        using (var db = new ApplicationDbContext())
        {
             var query1 = "SELECT TOP 10 PERCENT * FROM Products where CategoryID=1";
             var query2 = "SELECT TOP 10 PERCENT * FROM Products where CategoryID=2";
             var query3 = "SELECT TOP 10 PERCENT * FROM Products where CategoryID=3";
             var query4 = "SELECT TOP 10 PERCENT * FROM Products where CategoryID=4";
             var query5 = "SELECT TOP 10 PERCENT * FROM Products where CategoryID=5";

             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<Product> p4 = db.Database.SqlQuery<Product>(query4);
             IEnumerable<Product> p5 = db.Database.SqlQuery<Product>(query5);

             IEnumerable<Feed> all = p1.Union(p2).Union(p3).Union(p4).Union(p5);

            return all.ToList();
        }
    }

Answer 1

You might want to use parameters instead of concatenation, but something like this should work:

var ids = new[] { 1, 2, 3, 4, 5 };
var percentages = new[] { 10, 9, 8, 10, 9 };
var query = string.Join(" UNION ",
   ids.Zip(percentages, (id, percentage) => new { id, percentage })
    .Select(x => "SELECT TOP " + x.percentage +
                " PERCENT * FROM Products where CategoryID=" + x.id));
var results = db.Database.SqlQuery<Product>(query);
return results.ToList();

(when you're just concatenating numbers, there's not much risk, but if you try to concatenate almost any sort of string input, you should use parameters to prevent SQL injection)