复杂的 MVC LINQ 生成的 SQL 脚本

Question

我对MVC世界很陌生，但多年来我一直在做 asp.net。我用 mvc 开始了这个项目，所以我可以习惯它。我一直在苦苦挣扎 linq 生成的一些过于复杂的 sql 语句，我认为我做错了什么。也许它做对了，如果不从头开始编写 sql 语句，我就无法分辨。

好的，目标是我得到了这个身份数据库，我添加了一些字段，并在地址和电话号码等其他一些表中链接。

我正在供应商搜索屏幕上工作，并希望使用 linq 获取数据。一些信息在下面的示例中 table1 的下拉列表中，并且有一个文本搜索选项，允许对主 aspnet 用户和其他一些表上的多个字段进行"OR"搜索。

我希望有人帮助我在 linq 上获得简化版本。或者我应该开始编写我的 sql 并以某种方式在这里使用它（不知道如何在 mvc 中做到这一点，但应该非常相似

 var VendorRole = await db.Roles.FirstAsync(r => r.Name == "vendor");
        var results = db.Users.Include(u => u.table1).
            Include(u => u.table2).Include(u => u.table3).
            Include(u => u.table4).Include(u => u.table5).
            Where(u => u.Roles.Any(s => s.RoleId == VendorRole.Id));
        if (id1.HasValue)
        {
            results.Where(m => m.table1.First(a => a.id1 == id1) != null);
        }
        if (id2.HasValue)
        {
            results.Where(m => m.table1.First(a => a.id2 == id2) != null);
        }
        if (id3.HasValue)
        {
            results.Where(m => m.table1.First(a => a.id3 == id3) != null);
        }
        IEnumerable<string> searchTerms = SearchString.Split(' ')
                                              .Select(x => x.Trim());
        foreach (string term in searchTerms)
        {
            if (!string.IsNullOrWhiteSpace(term))
            {
                string captured = term.ToUpper();
                results = results.Where(t => (t.field1.ToUpper().Contains(captured) ||
                                            t.field2.ToUpper().Contains(captured) ||
                                            t.field3.ToUpper().Contains(captured) ||
                                            (t.field2 + " " + t.field3).ToUpper().Contains(captured) ||
                                            t.field4.ToUpper().Contains(captured) ||
                                            t.field5.ToUpper().Contains(captured) ||
                                            t.table2.Any(m => (m.field6 + " " + m.field7).ToUpper().Contains(captured)) != null));
            }
        }

        ViewBag.count = results.Count();
        if ((page -1) * 10 > ViewBag.count)
        {
            page = (int)Math.Floor((double)ViewBag.count / 10);
        }
        ViewBag.page = page;
        return PartialView(await results.OrderBy(m => m.field1 + " " + m.field2 + " " + m.field3).Skip((page - 1) * 10).Take(10).ToListAsync());

感谢您花时间提供帮助。

Answer 1

查询

现在的外观是，每个Where子句都使用集合上的AND进行匹配，而不是OR。

如果您确实想在所有情况下使用 OR，以下内容应该很有用。

正如我在另一个答案中提到的，使用 Linq 构建复杂查询谓词的正确方法是使用 PredicateBuilder 。完成此操作后，您可以将代码重构为两部分：

1. 构建Where子句的谓词和
2. 查询数据

代码如下所示：

private Expression<Func<User, bool>> BuildIdQuery(
    Expression<Func<User, bool>> predicate,
    int? id,
    Func<User, int> propertySelector)
{
    if(id.HasValue)
    {
        // First(...) with throw an exception if there are no items
        // matching the predicate; Any() is the proper way to do it.
        return predicate.Or(m => m.Any(a => propertySelector(a) == id);
    }
    return predicate;
}
Expression<Func<User, bool>> BuildSearchTermQuery(
    Expression<Func<User, bool>> predicate, string searchTerm)
{
    if(String.IsNullOrWhiteSpace(searchTerm)
        return predicate;
    // You don't need to use ToUpper() unless you 
    // know that your database performs case-sensitive comparison
    return predicate.Or(m => m.field1.Contains(searchTerm))
        .Or(m => m.field2.Contains(searchTerm)); // etc.
}

拥有所有这些查询将如下所示：

var predicate = PredicateBuilder.True<User>()
predicate = BuildIdQuery(predicate, id1, a => a.id1);
predicate = BuildIdQuery(predicate, id2, a => a.id2);
foreach(var term in searchTerms)
{
    predicate = BuildSearchTermQuery(predicate, term);
}
var results = db.Users
    .Where(predicate)
    .Skip(...)
    .Take(...);    

Answer 2

使用 Linq 或存储过程由你决定。对于数据库中的文本搜索，您可以查看Lucene端口

我建议您使用 N 层将复杂的业务逻辑与表示分开。您以后可以轻松更改/优化它。

/// <summary>
/// Assume you have special class to store users
/// </summary>
public class UserBdo
{
    public int id1 { get; set; }
    public int id2 { get; set; }
    ......
}
public class PagingParams
{
    public int CurrentPage { get; set; }
    public int PerPage { get; set; } = 10;
    /// <summary>
    /// How many without paging
    /// </summary>
    public int TotalResults { get; set; }
}
public class UsersBll
{
    public IEnumerable<UserBdo> Search(int? id1, int? id2, int? id3, IEnumerable<string> searchTerms, PagingParams pp)
    {
        var results = db.Users.Include(u => u.table1).
            Include(u => u.table2).Include(u => u.table3).
            Include(u => u.table4).Include(u => u.table5).
            Where(u => u.Roles.Any(s => s.RoleId == VendorRole.Id));
        if (id1.HasValue)
        {
            results.Where(m => m.table1.First(a => a.id1 == id1) != null);
        }
        if (id2.HasValue)
        {
            results.Where(m => m.table1.First(a => a.id2 == id2) != null);
        }
        //results = ...
        pp.TotalResults = 100;
        return results.Skip((pp.CurrentPage  - 1) * pp.PerPage).Take(pp.PerPage).ToListAsync();
    }
}