Does Dapper support the like operator?

sql

parameterization

sql-injection

database-behavior

byNikita Barsukov·Jan 21, 2025

Yes! Dapper elegantly handles LIKE queries. To use, just include the operator within your SQL as part of your query string. Ensure your search term is properly parameterized to avoid SQL injection. Here's a succinct example:

// The doctor said, "An Apple a day..." Let's find those apples!
var results = connection.Query<Product>(
    "SELECT * FROM Products WHERE Name LIKE @Term", 
    new { Term = "%apple%" }
);

This code samples effectively demonstrate how to search for products with "apple" in their names using Dapper's parameterized LIKE query capability.

Crafting safe and efficient LIKE queries with Dapper

The art of parameterization

Embedding parameters in your query boosts security and enhances overall query performance by enabling SQL query caching. When masterfully done, parameterization averts SQL injection. Use parameters in this fashion:

// Let's pick all the apples!
var searchTerm = "%apple%";
var results = connection.Query<Product>(
    "SELECT * FROM Products WHERE Name LIKE @Term",
    new { Term = searchTerm }
);

Wildcard handling – a delicate dance

While a leading wildcard (%term) can be an incredibly powerful tool, its tactile use could potentially lead to less efficient full table scans. Avoid them if you can, or explore full-text search functionalities that many DBMS provide.

Code those terms

If wildcard characters ('%' or '_') could appear in your search term, then they require escaping to be interpreted literally in the search. Each database has its unique method of escaping, adhere to the syntax expected by your DBMS.

The '%' concatenation technique

This technique allows for dynamic search patterns, ensuring the advantages of parameters are sustained:

// Let's shake up the apple tree!
var results = connection.Query<Product>(
    "SELECT * FROM Products WHERE Name LIKE '%' + @Term + '%'",
    new { Term = searchWord }
);

Deeper adventures with similar values

Understanding fuzzy searches

A fuzzy search can yield results similar to the searched term, providing more flexibility. Here's how you can perform one with Dapper:

// Some say tomato, some say to-mah-to...
var partialName = "apple";
var results = connection.Query<Product>(
    "SELECT * FROM Products WHERE Name LIKE '%' + @Term + '%'",
    new { Term = partialName }
);

Handling compounds of LIKE criteria

Combining multiple LIKE criterias can fine-tune the result set. Each criterion should be parameterized separately for best results:

// Finding out all about those red and sweet apples...
var colorSearch = "%red%";
var flavorSearch = "%sweet%";
var results = connection.Query<Product>(
    "SELECT * FROM Products WHERE Color LIKE @Color AND Flavour LIKE @Flavour",
    new { Color = colorSearch, Flavour = flavorSearch }
);

Best practices with LIKE operator in Dapper

Embrace the power of Parameters

Using Dapper queries with parameters HELPS AVOID SQL injection and significantly optimizes the execution speed by providing a reusable cache enabled execution plan.

Know your database's behavior with pattern matching

Different databases handle LIKE patterns differently. Understanding the specific behavior of your DBMS assists in crafting efficient LIKE operations in Dapper.