Converting SQL LIKE Queries to LINQ

Data and Databases
1

I have two classes defined as follows:

public class Transaction
{
    public int Id { get; set; }
    public string Details { get; set; }
}

public class Rule
{
    public int Id { get; set; }
    public string QueryPattern;
    public string Action;
}

There is no direct relationship between these two models within my database. However, the QueryPattern in the Rule class includes a SQL LIKE pattern, for example, ‘Hello World%’. I want to compare the Details in Transaction with the QueryPattern in Rule to retrieve a collection of all transactions along with any applicable rules based on the description.
I have crafted the following SQL statement to achieve this:

SELECT  
    tr.*,  
    r.QueryPattern  
FROM dbo.Transactions tr  
LEFT JOIN dbo.Rules r ON tr.Details LIKE r.QueryPattern

While I can execute this query in SQL successfully, I am having difficulty translating it to LINQ. I haven’t shared my previous attempts due to frequent syntax errors. My goal is to learn LINQ through this process rather than reverting back to SQL.

2

Hey Maya! Have you tried using the .Where() and .Contains() together in LINQ to achieve this? Though it’s tricky since LIKE and Contains aren’t exactly one-to-one. It’d be interesting to hear more about what pattern complexities you’re facing! Any workarounds you’ve considerd so far?

3

I understand your difficulty, especially since LINQ does not directly support SQL-style pattern matching. One approach I have found effective is using the StartsWith, EndsWith, or Contains methods depending on the pattern requirements. You can utilize these in combination with SelectMany to pair up each transaction with rules. For example, you can loop through each transaction, apply the Any() method to find matching rules, and potentially use regular expressions via C#'s System.Text.RegularExpressions to handle more complex patterns. This enables a similar pattern-matching capability within LINQ as you would achieve with SQL LIKE syntax.