0

I want to effectively get results from this query.

// Get all people whose name starts with F or later alphabets

Select * from MyTable where PersonName >'F'

When i run this code using Entity Framework Core 3.0, 

                 context.MyTable 
                 .Where(t=>  String.Compare(t.PersonName ,"F")>0);

I got error,

query 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()

My current work around is to use FromSQL method and write my own sql. Is there any way to achieve the same using LINQ syntax?

whatsinaname
  • 186
  • 9
  • Try String.Contains instead of String.Compare. – Thangadurai Apr 21 '20 at 03:06
  • What database type are you targeting? Because the sample query works with SqlServer (tried both latest EFC 3.1.3 and EFC 3.0.0). – Ivan Stoev Apr 21 '20 at 08:22
  • Also you may try the solution from https://stackoverflow.com/questions/59090817/enitityframework-is-very-slow-to-compare-strings-because-create-a-nvarchar-sqlpa/59095579#59095579 – Ivan Stoev Apr 21 '20 at 08:28

1 Answers1

1

From Microsoft's doc Where

The query represented by this method is not executed until the object is enumerated either by calling its GetEnumerator method directly

You should call method ToList() or ToListAsync() after Where for forces immediate query evaluation

In your code

context.MyTable
             .TakeWhile(t => t.PersonName.First() > 'F')
             .ToList();

Best solution for

Get all people whose name starts with F or later alphabets

    context.MyTable
             .OrderBy(t => t.PersonName)
             .SkipWhile(t => t.PersonName.First() < 'F')
             .ToList();

Take care with Upper and lower case

RoadRunner
  • 25,803
  • 6
  • 42
  • 75
krlosmederos
  • 176
  • 3
  • 11
  • 1
    Might be nicer to link the documentation as well. I've added it for `Where` for you. – RoadRunner Apr 21 '20 at 04:06
  • I tried this code and got this error (No idea why generated query is casting my column into int) Code var batch = await context.MyTable .OrderBy(t => t.PersonName) .SkipWhile(t => t.PersonName.First() < 'F') .ToListAsync(); Error: Processing of the LINQ expression 'DbSet .OrderBy(t => t.PersonName) .SkipWhile(t => (int)t.PersonName .AsQueryable() .First() < 70)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. – whatsinaname Apr 21 '20 at 08:19
  • How to declare MyTable and his property PersonName? – krlosmederos Apr 21 '20 at 13:56