SQL with LIKE to LINQ-CodePudding

I have two Models below:

public class Transaction
{
    public int TransactionId { get; set; }
    public string Description { get; set; }
}


public class TransactionRule
{
    public int TransactionRuleId { get; set; }
    public string Pattern;
    public string action;
}

There is no formal relationship between the two tables in my DB. However, the TransactionRules contains the column "pattern" which contains a SQL LIKE pattern such as "Hell World%". The Transactions.Descriptions column is matched against the TransactionRules.pattern column. This would allow me to geta list of all Transactions and any rules which matcvh against the description. Hopefully the query below describes this better.

select 
    t.*, 
    tr.pattern,
from dbo.Transactions t
left join dbo.TransactionRules tr ON t.Description LIKE tr.pattern

I can get this working in SQL without much problem but I am struggling to produce a LINQ equivalent. I would post what I have attempted so far, but they all produce syntax errors and therefore would add little value to my post.

While I could resort to using SQL, I would really prefer to try this in Linq as it will help me understand Linq better (this is an exercise in learning Linq).

CodePudding user response：

In case anyone else runs into the same problem, I managed to produce the following LINQ Query which implements a left join using the EF LIKE function as the condition for the join:

        var list = (from t in db.Transactions
                     from tr in db.TransactionRules.Where(tr => EF.Functions.Like(t.Description, tr.Pattern)).DefaultIfEmpty()
                     select new { t.TransactionId, t.Description, tr.Pattern, tr.TransactionClass });

CodePudding user response：

The equivalent of LIKE is Contains(). Try to use this in the where clause.