To start, I’m not sure how to set up this question. I’ve worked with linq queries and have a good understanding of how to build any type of query to get the data I need from a database. As I understand it, in particular with MSSQL:

• Linq queries convert the query code into T-SQL to execute against the SQL Server.
• Linq code logic doesn’t always seamlessly convert to T-SQL, which usually needs additional steps to make code logic to work. Example: Adding .ToList() to a context table call will pull all records into memory that the rest of the code generating T-SQL can query against.

This last point is where I am needing help with. Let me start with the goal I am trying to reach. I want to be able to create a generic method that can be called from a linq query, passing in a list of table fields, and a list of string values, that will return true or false if any of the string values are partially found in any of the table fields. I have the following method that I use that currently works:

    public static bool SearchTermMatch(List<string> t, List<string> f)
    {
        return
        (
            (t.Count() == 0) ||
            (t.Count(_t => f.Any(_f => _f != null && _f.ToLower().Contains(_t.ToLower())) || _t == "") == t.Count())
        );
    }

I can call it like this, for example:

var termlist = (string.IsNullOrEmpty(terms)) ? new List<string>() : terms.Split(' ').ToList();
var results = (from tbl in context.MyTable.ToList() where SearchTermMatch(termlist, new List<string>() { tbl.Field1, tbl.Field2, tbl.Field3 }) select new { tbl.Field1 }).Take(10).ToList();

As I’ve mentioned above, this works, but only because I have to apply .ToList() to the context table. However, there is a cost that I don’t want to absorb the way this is working. Basically all the records in MyTable are pulled down into memory, and the rest of the SearchTermMatch method is being applied to the results in memory. This becomes very expensive when MyTable has hundreds of thousands of records, because for every record in MyTable, SearchTermMatch is comparing the VALUES of Field1, Field2, and Field3 against the list of strings I’m looking for partial matches from. So in essence, T-SQL is performing quickly by passing back ALL the records in MyTable to the server’s memory, where the rest of linq is processing the resulting field values for each record against the list of strings from the server’s memory. This is inefficient.

I believe the answer is to convert the SearchTermMatch method into an Expression that will create the table fields being compared to the list of strings into T-SQL that can be executed with the MyTable query. Basically the goal is to have SearchTermMatch method pass back T-SQL to the linq query so I can get rid of the memory-inducing .ToList() addendum. I just want my linq to be fully executed on the SQL Server.

Does anyone have any suggestions on how I should approach this? I have been trying to wrap my brain around the concept of Expressions and feel that’s the direction I need to go in, but I’m having a hard time figuring out how to put together an alternative SearchTermMatch method as that.

Thank you for any help you can provide.