我的表NewsArticles的表包含以下列:ID和Text(其中包含新闻文章的内容)
var keywords = new List<string>{
"Lorem ipsum dolor",
"elementum lacinia",
"cursus nulla molestie",
}
这是我的查询,但确实可以正常工作
dbContext.NewsArticles.Where(article=>keywords.Contains(article.Text)).ToArray()
结果应该是包含任何一个关键字的所有单词的任何文章,无论单词顺序如何
具有以下内容的文章将是匹配项“ ipsum示例Lorem文本dolor”
单个查询可能吗?
如果没有,那有什么替代方案?存储过程?
我不确定LINQ to EF将如何转换它,或者它是否效率很高,但我认为这是您正在寻找的正确LINQ(您的逻辑已关闭):
// First, put your keywords in something more LINQ-friendly
var keywordGroups = keywords.Select(k => k.Split(' ')).ToArray();
Where(article => keywordGroups.
Any(keywordGroup => keywordGroup.
All(keyword => article.Text.Contains(keyword))))
话虽这么说,如果关键字组是固定的,我可能会将它们硬编码到查询中,如果EF妨碍了它,则可能会加入SQL。
如果关键字组不是固定的,那么如果EF妨碍了我,我仍然会使用SQL。
下面的代码显示了如何动态构造一个LINQ表达式树,该树有望完成您想要的操作:
using NUnit.Framework;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
namespace Sandbox
{
public class NewsArticle
{
public string Text { get; set; }
}
public class NewsArticleRepository
{
/// <summary>
/// This demonstartes how to perform the logic with a simple LINQ query.
/// Untetested with Linq to EF.
/// </summary>
public IEnumerable<NewsArticle> GetArticlesWithKeywordsUsingLinq(IQueryable<NewsArticle> articles, IEnumerable<string> keywords)
{
var keywordGroups = keywords.Select(k => k.Split(' ')).ToArray();
var filteredArticles = articles.Where(article => keywordGroups.
Any(keywordGroup => keywordGroup.
All(keyword => article.Text.Contains(keyword))));
var result = filteredArticles.AsEnumerable();
return result;
}
/// <summary>
/// This demonstartes how to perform the logic with an expression tree;
/// This is probably more efficient when converted to SQL.
/// Untetested with Linq to EF.
/// </summary>
public IEnumerable<NewsArticle> GetArticlesWithKeywordsUsingExpressionTree(IQueryable<NewsArticle> articles, IEnumerable<string> keywords)
{
var keywordGroups = keywords.Select(k => k.Split(' ')).ToArray();
var filteredArticles = articles.Where(GetWhereClauseForKeywordGroups(keywordGroups));
var result = filteredArticles.AsEnumerable();
return result;
}
/// <summary>
/// This demonstartes how to perform the logic with an expression tree;
/// This is probably even more efficient when converted to SQL because it uses a UNION instead of OR.
/// Untetested with Linq to EF.
/// </summary>
public IEnumerable<NewsArticle> GetArticlesWithKeywordsUsingExpressionTreeWithUnion(IQueryable<NewsArticle> articles, IEnumerable<string> keywords)
{
var keywordGroups = keywords.Select(k => k.Split(' ')).ToArray();
var filteredArticles = articles.Where(a => false);
foreach (var keywordGroup in keywordGroups)
{
var articlesWithAllKeywordsInGroup = articles.Where(GetWhereClauseForKeywordGroup(keywordGroup));
filteredArticles = filteredArticles.Union(articlesWithAllKeywordsInGroup);
}
var result = filteredArticles.AsEnumerable();
return result;
}
private Expression<Func<NewsArticle, bool>> GetWhereClauseForKeywordGroup(string[] keywordGroup)
{
var containsMethod = GetContainsMethod();
var article = Expression.Parameter(typeof(NewsArticle), "article");
Expression containsAllKeywords = Expression.Constant(true);
foreach (var keyword in keywordGroup)
{
var containsKeyword = Expression.Call(
Expression.Property(article, "Text"),
containsMethod,
Expression.Constant(keyword));
containsAllKeywords = Expression.And(containsAllKeywords, containsKeyword);
}
var whereClause = Expression.Lambda<Func<NewsArticle, bool>>(containsAllKeywords, article);
return whereClause;
}
private Expression<Func<NewsArticle, bool>> GetWhereClauseForKeywordGroups(string[][] keywordGroups)
{
var containsMethod = GetContainsMethod();
var article = Expression.Parameter(typeof(NewsArticle), "article");
Expression containsSomeKeywordGroup = Expression.Constant(false);
foreach (var keywordGroup in keywordGroups)
{
Expression containsAllKeywords = Expression.Constant(true);
foreach (var keyword in keywordGroup)
{
var containsKeyword = Expression.Call(
Expression.Property(article, "Text"),
containsMethod,
Expression.Constant(keyword));
containsAllKeywords = Expression.And(containsAllKeywords, containsKeyword);
}
containsSomeKeywordGroup = Expression.Or(containsSomeKeywordGroup, containsAllKeywords);
}
var whereClause = Expression.Lambda<Func<NewsArticle, bool>>(containsSomeKeywordGroup, article);
return whereClause;
}
private static MethodInfo GetContainsMethod()
{
var stringMethods = typeof(string).
GetMethods(BindingFlags.Instance | BindingFlags.Public).ToArray();
var containsMethods = stringMethods.Where(m => m.Name == "Contains");
var containsMethod = containsMethods.Single();
return containsMethod;
}
}
public class Tests
{
private NewsArticle _requestedExample;
private NewsArticle _missingWord;
private NewsArticle _inOrder;
private NewsArticle _outOfOrder;
private IQueryable<NewsArticle> _articles;
private List<string> _keywords;
[SetUp]
public void SetUp()
{
this._keywords = new List<string>
{
"Lorem ipsum dolor",
"elementum lacinia",
"cursus nulla molestie"
};
this._requestedExample = new NewsArticle
{
Text = "Requested Example: ipsum example Lorem text dolor"
};
this._missingWord = new NewsArticle
{
Text = "Missing word: cursus nulla"
};
this._inOrder = new NewsArticle
{
Text = "In Order: Lorem ipsum dolor"
};
this._outOfOrder = new NewsArticle
{
Text = "Out of Order: Lorem dolor ipsum"
};
this._articles = new[]
{
this._requestedExample,
this._missingWord,
this._inOrder,
this._outOfOrder,
}.AsQueryable();
}
[Test]
public void GetArticlesWithKeywordsUsingLinqShouldWork()
{
var result = new NewsArticleRepository().GetArticlesWithKeywordsUsingLinq(this._articles, this._keywords).ToArray();
AssertResult(result);
}
[Test]
public void GetArticlesWithKeywordsUsingExpressionTreeShouldWork()
{
var result = new NewsArticleRepository().GetArticlesWithKeywordsUsingExpressionTree(this._articles, this._keywords).ToArray();
AssertResult(result);
}
[Test]
public void GetArticlesWithKeywordsUsingExpressionTreeWithUnionShouldWork()
{
var result = new NewsArticleRepository().GetArticlesWithKeywordsUsingExpressionTreeWithUnion(this._articles, this._keywords).ToArray();
AssertResult(result);
}
private void AssertResult(NewsArticle[] result)
{
Assert.That(result.Contains(this._requestedExample), Is.True);
Assert.That(result.Contains(this._missingWord), Is.False);
Assert.That(result.Contains(this._inOrder), Is.True);
Assert.That(result.Contains(this._outOfOrder), Is.True);
}
}
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句