How to do a LINQ-SQL query on a delimited column, without resorting to client-side evaluation

I am trying to query a view, which has an "Operator" column. Sample data:

• Ed
• Edward
• Ed;Adam
• [null]
• Bob; Edward
• Bob; Ed; Tom

Given a user input of a List<string>, let's say "Ed","Peter", "Mark", I want to return rows that fully contain one of the user inputs, in my example rows #1, #3, and #6.

An equivalent SQL query that returns what I want would be:

SELECT * 
FROM myview 
WHERE "Operator" LIKE 'Ed' OR "Operator" LIKE 'Ed;%' OR "Operator" LIKE '%;Ed' OR "Operator" LIKE '%;Ed;%' 
--repeated for "Peter" and "Mark" etc

I've tried #1:

var query = IQueryable<Myview> farms;
var List<string> operators = new List<string> { "Ed","Peter", "Mark" };

var filteredFarms = query.Where(t => t.Operator != null && operators.Any(op => t.Operator.Split(';').Any(o => o == op)));

Which unsurprisingly gives "The LINQ expression ... could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'."

I also tried #2:

var filteredFarms = query.Where(t => t.Operator != null && operators.Any(op => EF.Functions.Like(t.Operator, ""+op+";%")));

As a test to see if I could build the "like" query manually, but that failed with the same error.

Is there any way I can do this using LINQ without client-side evaluation? You may assume that ";" is only used as a separator.

There's no such thing as delimited columns in relational databases. These are just string values. They can't be queried or indexed. Trying to find a string in the middle of such a column will force the database to scan the entire table.

There's no need for such values in PostgreSQL. PostgreSQL has array types that can be searched using array operators:

CREATE TABLE Farms (
    ...
    Operators  text ARRAY,
    ...
);
CREATE INDEX idx_farm on "Farms" USING GIN ("Operators");
...

SELECT *
FROM Farms
WHERE Operators @> ARRAY['Joe']

NpgSQL supports array mapping and querying. This means you can easily write a LINQ query that checks for an element in the array:

var query=dbContext.Farms.Where(farm=>farm.Operators.Contains("Joe"));

Array fields can be indexed with a GIN index, providing fast querying. Only a few of the array operators take advantage of indexing though, which is why I used @> instead of ANY.

In other databases you could use their JSON or XML support to store, query and sometimes index values instead of using string manipulations

Try :

<!-- begin snippet: js hide: false console: true babel: false -->

            List<string> query = new List<string>()
            {
                "Ed",
                "Edward",
                "Ed; Adam",
                null,
                "Bob; Edward",
                "Bob; Ed; Tom"
            };
            List<string> operators = new List<string> { "Ed", "Peter", "Mark" };

            var results = query.Where(x => x != null && x.Split(new char[] { ';' }).Any(y => operators.Any(z => y.Trim().Contains(z))));

<!-- end snippet -->

I ended up moving part of the logic to an SQL function:

CREATE OR REPLACE FUNCTION public."FarmContainsOperator"(param_op TEXT, id bigint)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
DECLARE
	count integer; 
BEGIN
count :=  (SELECT COUNT(*)
	FROM public.myview WHERE 
		("Operator" LIKE param_op OR 
		"Operator" LIKE param_op||';%' OR
		"Operator" LIKE '%;'||param_op OR
		"Operator" LIKE '%;'||param_op||';%') AND "farmID"=id
		);
return count;
END;
$function$

Then I declared that function in my model:

public static int FarmContainsOperator(string opName,long id) => throw new NotSupportedException();

And in the context:

modelBuilder
                .HasDbFunction(typeof(Myview).GetRuntimeMethod(nameof(Myview.FarmContainsOperator), new[] { typeof(string), typeof(long) }))
                .HasName("FarmContainsOperator");

So now I can do this:

var query = IQueryable<Myview> farms;
var List<string> operators = new List<string> { "Ed","Peter", "Mark" };

var predicate = PredicateBuilder.New<Myview>(false);

foreach (string op in operators)
{
	predicate = predicate.Or(x => Myview.FarmContainsOperator(op, x.farmID) > 0);
	
}
query = query.Where(predicate);

And it works. Probably it would be more efficient to use a TVF instead of scalar function, but it's sufficient for now.