Efcore.pg: Backslash treated as escape character in parameterized StartsWith LIKE value

[ExerciseBook]

https://github.com/npgsql/efcore.pg/issues/1146

---

My environment:

    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="5.0.0-rc2" />

# SELECT VERSION();
                          version
------------------------------------------------------------
 PostgreSQL 13.0, compiled by Visual C++ build 1914, 64-bit
(1 row)

My Problem

I used

.Where(s => s.StartsWith("\\1"));
````

And I got
```sql
WHERE ($1 = '') OR ((f."Path" LIKE $1 || '%') AND (left(f."Path", length($1))::text = $1::text));

$1 = \1

from DBMS's log. And get null from the result.

I tested It will work when I use

WHERE ($1 = '\1') OR ((f."Path" LIKE '\\1%') AND (left(f."Path", length('\1'))::text = '\1'));

---

Some Attempts

At first, I think the problem that comes from I searched "\user" for "\uHHHH" is an escape of Unicode. So I tried "\234". It can't work either.

emm. maybe it is a bug? I have no idea.

[roji]

Are you sure you're using EFCore.PG 5.0.0-rc2? When I run your code I get:

WHERE b."Name" LIKE '\\1%'

[ExerciseBook]

I tried another way to reproduce this problem.

failed

2020-11-08 08:14:01.020 HKT [30208] LOG:  execute <unnamed>: SELECT f."Id", f."CreatedAt", f."Folder", f."Guid", f."Md5", f."Name", f."Path", f."Size", f."Status", f."StorageName", f."Type", f."UpdatedAt", f."UserId"

    FROM "Files" AS f

    WHERE ($1 = '') OR ((f."Path" LIKE $1 || '%') AND (left(f."Path", length($1))::text = $1::text))
2020-11-08 08:14:01.020 HKT [30208] DETAIL:  parameters: $1 = '\users\'

succeed

2020-11-08 08:16:16.973 HKT [32200] LOG:  execute <unnamed>: SELECT f."Id", f."CreatedAt", f."Folder", f."Guid", f."Md5", f."Name", f."Path", f."Size", f."Status", f."StorageName", f."Type", f."UpdatedAt", f."UserId"

    FROM "Files" AS f

    WHERE f."Path" LIKE '\\users\\%'

It is so strange. 😂

[ExerciseBook]

A minimal example to reproduce this problem.

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace BugReport
{
    public class StringSet
    {
        public long Id { get; set; }
        public string Value { get; set; }
    }

    public class Database : DbContext
    {
        public DbSet<StringSet> StringSet { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseNpgsql("Host=localhost;Database=emptydb;Username=root;Password=123456");
    }

    class Program
    {
        static void Main(string[] args)
        {
            var db = new Database();

            // Empty the table
            db.StringSet.RemoveRange(db.StringSet.AsQueryable());
            db.SaveChanges();

            // Add the test
            List<StringSet> s = new List<StringSet>();
            s.Add(new StringSet() {Id = 1, Value = "123"});
            s.Add(new StringSet() {Id = 2, Value = "124"});
            s.Add(new StringSet() {Id = 3, Value = "125"});
            s.Add(new StringSet() {Id = 4, Value = "123\\123"});
            s.Add(new StringSet() {Id = 5, Value = "123\\123\\1234"});
            s.Add(new StringSet() {Id = 6, Value = "123\\1234\\123"});
            s.Add(new StringSet() {Id = 7, Value = "789\\4567\\123"});
            db.StringSet.AddRange(s);
            db.SaveChanges();

            // Test
            var t1 = db.StringSet.Where(s => s.Value.StartsWith("123")).ToList();
            if (t1.Count != 4)
            {
                // Succeed
                throw new Exception();
            }

            // Test
            var t2 = db.StringSet.Where(s => s.Value.StartsWith("123\\")).ToList();
            if (t2.Count != 3)
            {
                // Succeed
                throw new Exception();
            }

            // Test
            const string t3s = "123\\";
            var t3 = db.StringSet.Where(s => s.Value.StartsWith(t3s)).ToList();
            if (t3.Count != 3)
            {
                // Succeed
                throw new Exception();
            }

            // Test
            var t4s = "123\\";
            var t4 = db.StringSet.Where(s => s.Value.StartsWith(t4s)).ToList();
            if (t4.Count != 3)
            {
                // Failed
                throw new Exception(); // <--------
            }

            ////////////////////////////////

            // Test
            var t5 = db.StringSet.Where(s => s.Value.EndsWith("123")).ToList();
            if (t5.Count != 4)
            {
                // Succeed
                throw new Exception();
            }

            // Test
            var t6 = db.StringSet.Where(s => s.Value.EndsWith("\\123")).ToList();
            if (t6.Count != 3)
            {
                // Succeed
                throw new Exception();
            }

            // Test
            const string t7s = "\\123";
            var t7 = db.StringSet.Where(s => s.Value.EndsWith(t7s)).ToList();
            if (t7.Count != 3)
            {
                // Succeed
                throw new Exception();
            }

            // Test
            var t8s = "\\123";
            var t8 = db.StringSet.Where(s => s.Value.EndsWith(t8s)).ToList();
            if (t8.Count != 3)
            {
                // Succeed
                throw new Exception();
            }
        }
    }
}

In addition, I tested EndsWith. EndsWith works well.

Test 7 :

2020-11-09 22:02:40.950 HKT [32036] LOG:  execute <unnamed>: SELECT s."Id", s."Value"

    FROM "StringSet" AS s

    WHERE (s."Value" IS NOT NULL) AND (s."Value" LIKE '%\\123')

Test 8 :

2020-11-09 22:02:40.997 HKT [32036] LOG:  execute <unnamed>: SELECT s."Id", s."Value"

    FROM "StringSet" AS s

    WHERE ($1 = '') OR ((s."Value" IS NOT NULL) AND (right(s."Value", length($1))::text = $1::text))
2020-11-09 22:02:40.997 HKT [32036] DETAIL:  parameters: $1 = '\123'

[roji]

@ExerciseBook sorry for not having taken a closer look earlier - I can confirm there's an issue in the provider. I'll prepare a fix for the 5.0.2 release.