c# - 如何将 Entity Framework 与 Union 和 Top N Percent 一起使用？

Question

我正在使用下面的代码来获取数据。代码工作正常，但它对 db 的调用太多了。有没有更好更快的方式获取数据？我必须使用 TOP N PERCENT 和 UNION 结果。谢谢。

[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

你可能想使用参数而不是连接，但像这样的东西应该可以工作:

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

(当你只是连接数字时，风险不大，但如果你尝试连接几乎任何类型的 string 输入，你应该使用参数来防止 SQL 注入(inject))