Respect leading and trailing spaces when querying SQL Server

In my current project, I have to implement a search functionality where users can provide a search term and the search term is to be matched exactly. This also means that any leading and trailing in the search term must be respected.

Let’s say we have these Posts in the database

    public class Post
    {
        [Key]
        public Guid PostId { get; set; }
        public string Title { get; set; }
        public DateTime PublishedDate { get; set; }
    }

And we initialise the database with 2 posts like these

        public static void Initialize(SampleContext context)
        {
            context.Database.EnsureCreated();
            var posts = new List<Post>
            {
                new Post
                {
                    PostId = Guid.NewGuid(),
                    Title = "ABC"
                },
                new Post
                {
                    PostId = Guid.NewGuid(),
                    Title = "ABC "
                }
            };
            context.Posts.AddRange(posts);
            context.SaveChanges();
        }

And we have an endpoint like this to search for posts

        [HttpGet("search")]
        public async Task<IEnumerable<Post>> Search(string searchTerm)
        {
            var posts = _sampleContext.Posts.Where(x => x.Title == searchTerm);

            return await posts.ToListAsync();
        }

If we search for “abc “, I expect only one result returned but both posts are returned.

After a bit of research, it turns out that Transact-SQL considers the strings ‘abc’ and ‘abc ‘ to be equivalent for most comparison operations due to padding for the character strings used in comparisons so that their lengths match before comparing them. You can read the full documentation from Microsoft here.

Based on that documentation, to make it respect the trailing space, we would need to use the Like predicate. Let’s fix the endpoint to do so.

        [HttpGet("search")]
        public async Task<IEnumerable<Post>> Search(string searchTerm)
        {
            var posts = _sampleContext.Posts.Where(x => EF.Functions.Like(x.Title, searchTerm));

            return await posts.ToListAsync();
        }

And now the result only returns 1 post as expected.