Question 200139 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    Try :

                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))));
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search