Can I rewrite this query to LINQ?

by

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();
    }

  • Post the code of GetTitleFilters and GetTagFilters, because that will determine what the LINQ query will look like. Instead of using raw SQL construct the query dynamically. Every LINQ operator returns a new query, it doesn’t modify an existing one. That means you can apply multiple Where calls one after the other to add the filters you need.

    – 

  • BTW lower(c.""LanguageCode"") = @language prevents the database from using any indexes on LanguageCode. If you want case-insensitive searching use a case-insensitive collation for that column

    – 

  • @Panagiotis Kanavos Thanks for your tip, added missing code

    – 

  • Essentially you’re doing a LIKE '%word% for every word on titles and Name in (w1, w2, w3) for tags. You can generate the IN clause easily in LINQ with words.Contains(c.Name). This will get converted to c.Name IN ('word1','word2',...)

    – 

  • For tags you’re right, but for title filter I need to have all words inside

    – 

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.

Leave a Comment