EF Core 查询优化：是否可以使用"包含在列表中<string>"？

Question

使用EF Core+.NET 5.0预览版+Postgres。

我试图找出所有带有标签的问题。

我有这个Question类：

public class Question : IEntity
{
/// <summary>
/// auto index
/// </summary>
public int Id { get; set; }
public List<string> tags { get; set; }
//other properties       
}

所以，我希望这个代码能做我想做的事：

var questions = _context.Questions
.Where(question => question.tags.Contains(tag) == true)
.AsQueryable();
return questions.AsAsyncEnumerable();

但什么也没发生。我记录EF Core SQL查询，它看起来像这样：

[22:15:30 INF] Entity Framework Core 3.1.9 initialized 'ApplicationContext' using provider 
'Npgsql.EntityFrameworkCore.PostgreSQL' with options: None
[22:15:30 INF] Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT q."Id", q."OwnerId", q.answer_count, q.bounty_amount, q.bounty_closes_date, q.closed_date, 
q.closed_reason, q.content_license, q.creation_date, q.is_answered, q.last_activity_date, 
q.last_edit_date, q.link, q.question_id, q.score, q.tags, q.title, q.view_count
FROM "Questions" AS q
WHERE (TRUE = FALSE) = TRUE

这个问题把我弄糊涂了。

我用一种更简单的方式重写了代码：

var qResult = new List<Question>();
var quests = _context.Questions.AsQueryable();
foreach (var q in quests)
{
if (q.tags.Contains(tag))
qResult.Add(q);
}

这段代码是有效的，但我担心它没有优化查询，当我有更多的问题时，我会失去记忆。

那么，如何创建优化的查询呢？

p.S.完整代码：github repo

这是我的appsettings.Development.json:

{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"Questions": "Host=localhost;Port=5432;Database=questionsdb;Username=postgres;Password=password"
},
"Tags": [
"ef core",
"c#",
"asp.net core",
"asp.net core webapi"
]
}

附言：另一种变体：

var questions = _context.Questions.Where(question => question.tags.Contains(tag)).AsQueryable();
return questions.ToList();

[21:44:29 INF] Executed DbCommand (1ms) [Parameters=[], CommandType='Text', 
CommandTimeout='30']
SELECT q."Id", q."OwnerId", q.answer_count, q.bounty_amount, 
q.bounty_closes_date, q.closed_date, q.closed_reason, q.content_license, 
q.creation_date, q.is_answered, q.last_activity_date, q.last_edit_date, 
q.link, q.question_id, q.score, q.tags, q.title, q.view_count
FROM "Questions" AS q
WHERE TRUE = FALSE

Answer 1

只需尝试将查询更改为

return   _context.Questions
.Where(q => q.tags.Contains(tag))
.ToArrayAsync();