c# - LINQ to SQL，如何计算在一个或多个字段中找到的搜索词？

有没有一种方法可以在LINQ中编写查询以返回在字段中找到的搜索词的计数

基本上，我想这样工作：

var matches = from t in _db.Books
                          let score = GetScore(t, searchterms)
                          where score >= 1
                          orderby score descending
                          select t;

public static int GetScore(Book b, params string[] searchterms)
    {
        int count = 0;
        foreach (string term in searchterms)
        {
            if (b.Title.Contains(term))
                count++;
        }
        return count;
    }

但是，那当然是行不通的。
我的小GetScore函数可以转换为LINQ吗？

谢谢。

编辑：我也希望获得分数。理想情况下，我会将结果选择到SearchResults类（用于View）中，该类将包含一些Book信息和该查询的Book得分。要更新我的查询，它是这样的：

var matches = from t in _db.Books
                          let score = GetScore(t, searchterms)
                          where score >= 1
                          orderby score descending
                          select new SearchResult
                                                {
                                                    Title = t.Title,
                                                    Type = "Book",
                                                    Link = "Books/Details/" + t.BookID,
                                                    Score = score
                                                };

对不起，我本来不清楚。

最佳答案

如果不对数据库发出多个查询，就无法做您想做的事情-本质上每个搜索项一个。如果您愿意这样做，那么这里是一种简单的方法：

var terms = new [] { "s", "t", "r", "e", "b", "c", };

var ids =
    from term in terms
    from id in _db.Books
        .Where(book => book.Title.Contains(term))
        .Select(book => book.Id)
    group term by id into gts
    orderby gts.Count() descending
    select gts.Key;

var selectedIds = ids.Take(50).ToArray();

var query =
    from book in _db.Books
    where selectedIds.Contains(book.Id)
    select book;

我写了ids来返回一个ID列表，该ID按最匹配术语最多的ID排序。这是为了最接近您想要在问题中得到的相同结果。然后，我决定使用Take(50)来获得前50个结果。您显然可以更改此策略以适合您的需求，但最终必须使用一组ID才能在最终查询中使用。

我希望这有帮助。



编辑：基于OP的编辑。

查询包含分数的方法如下：

var terms = new [] { "s", "t", "r", "e", "b", "c", "l", "i", };

var idScores =
    from term in terms
    from id in _db.Books
        .Where(book => book.Title.Contains(term))
        .Select(book => book.BookID)
    group term by id into gts
    select new
    {
        Id = gts.Key,
        Score = gts.Count(),
    };

var selectedIds = idScores.Select(x => x.Id).Take(50).ToArray();

var selectedBooks =
    from book in _db.Books
    where selectedIds.Contains(book.BookID)
    select book;

var query =
    from b in selectedBooks.ToArray()
    join x in idScores on b.BookID equals x.Id
    orderby x.Score descending
    select new
    {
        Title = b.Title,
        Type = "Book",
        Link = "Books/Details/" + b.BookID,
        Score = x.Score,
    };