How to convert sql to EF linq query

Question

I am converting a sql query with 2 table Joins to an entity Framework linq query in a .net6 application.

The sql query as I mentions joning 2 tables on the below condition,

substr(a.anystring,0,length(a.anystring) -(length(a.anystring)-instr(a.anystring,'_')+1))

I changed it into EF linq code as below, but I am getting an error saying unable to translate IndexOf to sql.

anystring.Substring(0, anystring.Length - (anystring.IndexOf('_') + 1))

Error : "Additional information: Translation of method 'string.IndexOf' failed"

Answer 1

its hard to tell because your sample sql statement is not valid sql.

anyway you use the sqlfunctions class to map c# functions to sql functions if EF 6

https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.sqlclient.sqlfunctions?redirectedfrom=MSDN&view=netframework-4.8.1

if EF core then you use the DbFunctions;

https://learn.microsoft.com/en-us/ef/core/querying/database-functions

in EF core some clr functions are mapped by the sqlserver provider:

https://learn.microsoft.com/en-us/ef/core/providers/sql-server/functions