How to convert sql to EF linq query

Newbie Dev 151 Reputation points
2023-05-23T17:34:17.5233333+00:00

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"

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
725 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,384 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Bruce (SqlWork.com) 61,186 Reputation points
    2023-05-23T18:25:07.7433333+00:00

    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

    0 comments No comments