How to use the UNDERSCORE (_) find/replace in my LINQ query

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
glennn
Posts: 3
Joined: Thu 13 Nov 2014 20:12

How to use the UNDERSCORE (_) find/replace in my LINQ query

Post by glennn » Wed 13 May 2015 22:09

Hi,
In Oracle, I can use the underscore in SQL to search for a single character in its place.
Can you tell me how I can do that in a query like the below? What I am trying to do is find these records:
LETTERSA345
LETTERSB345
LETTERS1345
but NOT
LETTERSBB345
LETTERS33345

using (VtDataContext dContext = new VtDataContext(ConfigManager.GetOracleConnectionString()))
{
List<VtLog> logs = (from vtLog in dContext.VtLogs
where vtLog.Title.Contains("LETTERS_345")
orderby vtLog.LogId descending
select vtLog).ToList();

return vtLogs;
}

Thank you,
Glennn
PS I think I am using: dotConnect for Oracle 8.4

Shalex
Devart Team
Posts: 8083
Joined: Thu 14 Aug 2008 12:44

Re: How to use the UNDERSCORE (_) find/replace in my LINQ query

Post by Shalex » Thu 14 May 2015 13:22

There are three alternative ways to solve the issue:

1. NoEscapeLike

Code: Select all

var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
config.QueryOptions.NoEscapeLike = true;
...
List<VtLog> logs = (from vtLog in dContext.VtLogs
where vtLog.Title.Contains("LETTERS_345")
orderby vtLog.LogId descending
select vtLog).ToList();
2. OracleFunctions.Like

Code: Select all

List<VtLog> logs = (from vtLog in dContext.VtLogs
where Devart.Data.Oracle.Entity.OracleFunctions.Like(vtLog.Title, "LETTERS_345")
orderby vtLog.LogId descending
select vtLog).ToList();
3. StatsWith / EndsWith / Length

Code: Select all

List<VtLog> logs = (from vtLog in dContext.VtLogs
where vtLog.Title.StartsWith("LETTERS") && vtLog.Title.EndsWith("345") && vtLog.Title.Length == 11
orderby vtLog.LogId descending
select vtLog).ToList();
JIC: use the dbMonitor tool (documentation, download link) to enable tracing of the generated SQL.

glennn
Posts: 3
Joined: Thu 13 Nov 2014 20:12

Re: How to use the UNDERSCORE (_) find/replace in my LINQ query

Post by glennn » Thu 14 May 2015 15:03

Thanks, Shalex! Is there a particular reference that i need to include to access Devart.Data.Oracle.Entity?

I presently am referencing:
Devart.Data.Linq.dll
Devart.Data.dll
Devart.Data.Oracle.Linq.dll
Devart.Data.Oracle.dll

Thank you,
Glennn

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: How to use the UNDERSCORE (_) find/replace in my LINQ query

Post by MariiaI » Fri 15 May 2015 10:23

glennn wrote:Thanks, Shalex! Is there a particular reference that i need to include to access Devart.Data.Oracle.Entity?

The Shalex's post is related to Entity Framework support and the suggested code requires additional assemblies, i.e. from the \Program Files (x86)\Devart\dotConnect\Oracle\Entity directory.

As for the LinqConnect support, you can use one of these solutions (the analogues of the 2nd and 3rd solutions from the previous post):
1) SqlMethods.Like:

Code: Select all

 List<VTLOG> logs = (from vtLog in dContext.VTLOGs
      where SqlMethods.Like(vtLog.Title, "LETTERS_345")
      orderby vtLog.LogId descending
      select vtLog).ToList();

2) StatsWith / EndsWith / Length:

Code: Select all

 List<VTLOG> logs = (from vtLog in dContext.VTLOGs
     where vtLog.Title.StartsWith("LETTERS") && vtLog.Title.EndsWith("345") && vtLog.Title.Length == 11
     orderby vtLog.LogId descending
     select vtLog).ToList();
Please notify us about the results.

sunece87
Posts: 1
Joined: Fri 29 May 2015 11:30

Re: How to use the UNDERSCORE (_) find/replace in my LINQ query

Post by sunece87 » Fri 29 May 2015 13:40

Hi Shalex,

This is just a continuation to Glenn's post.

I tried using NoEscapeLike and OracleFunctions.Like but none of them worked. NoEscapeLike didn't throw any error but the query didn't return the right data either.

OracleFunctions.Like is throwing the below error. Not sure if it is supported by dotConnect for Oracle 8.4.

"An exception of type 'System.InvalidOperationException' occurred in Devart.Data.Linq.dll but was not handled in user code

Additional information: Method 'Boolean Like(System.String, System.String)' is not supported for execution as SQL."

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: How to use the UNDERSCORE (_) find/replace in my LINQ query

Post by MariiaI » Tue 02 Jun 2015 06:02

sunece87 wrote:I tried using NoEscapeLike and OracleFunctions.Like but none of them worked. NoEscapeLike didn't throw any error but the query didn't return the right data either.
sunece87 wrote:"An exception of type 'System.InvalidOperationException' occurred in Devart.Data.Linq.dll but was not handled in user code
Additional information: Method 'Boolean Like(System.String, System.String)' is not supported for execution as SQL."
glennn wrote:I presently am referencing:
Devart.Data.Linq.dll
Devart.Data.dll
Devart.Data.Oracle.Linq.dll
Devart.Data.Oracle.dll
The NoEscapeLike and OracleFunctions.Like solutions are applicable only for Entity Framework support, however, according to the details that you have specified (i.e., the referenced assemblies and the error message), you are using LinqConnect ORM (the *.lqml model).
Thus, please use the solutions described here http://forums.devart.com/viewtopic.php?t=31775#p109492 and notify us about the results.

If this doesn't help, please send us the test project, so that we are able to investigate it more clearly and find the solution for you in a shortest time.

Post Reply