Entity Framework, LINQ: can't use Any with StartsWith?

Question

I have a simple Entity Framework LINQ query. The goal is to find all the Carriers that start with A, B, or C:

var letters = new List<string>() { "A", "B", "C" }; // Dynamic, can be many
var results = db.Carriers.AsNoTracking()
    .Where(c => letters.Any(val => c.Name.StartsWith(val)))
    .ToList();

I get

System.InvalidOperationException: 'The LINQ expression 'DbSet .Where(c => __letters_0 .Any(val => val == "" || c.Name != null && val != null && c.Name.StartsWith(val)))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

Is there no way to do this?

Answer 1

In Entity Framework 6 this query would run fine. EF6 supports SQL translation of StartsWith and it supports using local sequences (letters) in a query expression.

EF core 3 (the version in the question as the exception message indicates) also supports SQL translation of StartsWith. The problem here (which the other answer misses completely) is that the local sequence is used in a way that's not supported. A query like...

var results = db.Carriers.AsNoTracking()
    .Where(c => letters.Contains(c.Name))
    .ToList();

...would be supported because letters can simply be translated into an IN clause. But of course that's an entirely different query.

Using letters.Any requires EF to convert letters into "something" that can be joined with in SQL. EF6 does this by building a result set in the SQL query:

    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM  (SELECT 
            N'A' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
        UNION ALL
            SELECT 
            N'B' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
        UNION ALL
            SELECT 
            N'C' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
        WHERE ( CAST(CHARINDEX([UnionAll2].[C1], [Extent1].[Name]) AS int)) = 1

Which works, but isn't scalable at all. EF core 3 doesn't support it and there's no easy work-around as suggested in the other answer.

A possible work-around is to build a predicate with || (Or) predicates, for example:

var pred = letters.Aggregate(PredicateBuilder.False<Carrier>(), 
    (p,x) => p = p.Or(c => c.Name.StartsWith(x)));
var results = db.Carriers.AsNoTracking()
    .Where(pred)
    .ToList();

Where PredicateBuilder is a predicate builder like Linqkit or this one. But this method isn't scalable either. EF creates a parameter for each entry in letters, so you may hit the 2100-parameters threshold in Sql Server.

Answer 2

The Entity Framework doesn't allow usage of custom predicates. That's the reason why your query is failing with a message:

... could not be translated. Either rewrite the query in a form that can be translated ...

So, this most probably means that your EF version either doesn't support StartsWith or there is some combination, like StartsWith and Any that can't be translated.

A workaround could be:

1. First, you might filter your query with the closest way, by using the Contains method.

var results = students.Where(s => letters.Any(l => s.name.Contains(l)));

Here is a similar example I wrote for .NET fiddle.

---

2. Next in case if you still want to use StartsWith but your EF doesn't support that you can convert your query to IN-MEMORY collection :

var results = filter_by_contains_query
              .ToList() // convert filtered result to in-memory collection
              .Where(i => letters.Any(l => i.StudentName.StartsWith(l)));

where filter_by_contains_query is a query from the 1st part.

---

More:

• About EF supported methods check here.
• Discussions around the StartsWith,EndsWith and Contains here