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

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

Postby 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
glennn
 
Posts: 3
Joined: Thu 13 Nov 2014 20:12

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

Postby 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.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

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

Postby 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
glennn
 
Posts: 3
Joined: Thu 13 Nov 2014 20:12

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

Postby 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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

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

Postby 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."
sunece87
 
Posts: 1
Joined: Fri 29 May 2015 11:30

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

Postby 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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for Oracle