Can I rewrite this query to LINQ?

Question

I have such query

 SELECT DISTINCT ON (c.""CmsId"") ""CmsId"", c.""Id"", c.""Title"" 
 FROM ""Content"" AS c
 LEFT JOIN ""TagsContents"" AS tc ON c.""Id"" = tc.""ContentId""
 LEFT JOIN ""Tags"" AS t ON tc.""TagId"" = t.""Id""
 WHERE lower(c.""LanguageCode"") = @language AND 
 (({GetTitleFilters(phraseWords)}) OR ({GetTagFilters(phraseWords)}))
 LIMIT @resultsPerPage OFFSET @offsetValue 

GetTitleFilters() and GetTagFilters() are returning where clauses as string based on words in user input

I execute query like this

  var results = await _context.Contents
     .FromSqlRaw(query, queryParams)
     .AsNoTracking()
     .ToListAsync(cancellationToken);

So my problem is: I'm trying to write tests for this logic. But i'm getting this error:

Query root of type 'FromSqlQueryRootExpression' wasn't handled by provider code. This issue happens when using a provider specific method on a different provider where it is not supported.

I'm using in-memory db to store data for this test. Not sure options I have here, but my idea is to rewrite this query to LINQ and then it should be available for tests (I want to execute query on SQL side).

Maybe you can suggest me something, maybe there's a way to execute FromSqlRaw in-memory db?

EDIT Missing filter functions

    private string GetTitleFilters(List<string> words)
    {
        var result = new StringBuilder();
        foreach (var word in words)
        {
            var index = words.IndexOf(word);
            result.Append($@"(strpos(lower(c.""Title""), @word{index}) > 0)");
            if (!words.Last().Equals(word))
            {
                result.Append(" AND ");
            }
        }

        return result.ToString();
    }

    private string GetTagFilters(List<string> words)
    {
        var result = new StringBuilder();
        foreach (var word in words)
        {
            var index = words.IndexOf(word);
            result.Append(@$"(lower(t.""Name"") = @word{index})");
            if (!words.Last().Equals(word))
            {
                result.Append(" OR ");
            }
        }

        return result.ToString();
    }

Answer 1

In-memory DB does not support raw SQL queries, for obvious reasons: it's not an SQL provider.

You can write a normal LINQ query like this

var results = await _context.Contents
    .Where(c => (
         phraseWords.All(word => c.Title.Contains(word))
         ||
         phraseWords.Any(word => c.Tags.Any(t => t.Name == word))
       )
       && string.Equals(c.LanguageCode, language, StringComparison.OrdinalIgnoreCase))
    .Skip(offsetValue)
    .Take(resultsPerPage)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

Given that the DISTINCT is on c.CmsId it is unnecessary if you are querying Contents directly, as there are no joins.

You should probably rethink GetTitleFilters and GetTagFilters anyway as they are doing SQL injection.

The benefits of using await on an in-memory DB are not clear, I don't think it will give much performance benefit.