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
How to use the UNDERSCORE (_) find/replace in my LINQ query
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
There are three alternative ways to solve the issue:
1. NoEscapeLike
2. OracleFunctions.Like
3. StatsWith / EndsWith / Length
JIC: use the dbMonitor tool (documentation, download link) to enable tracing of the generated SQL.
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();
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();
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();
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
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
I presently am referencing:
Devart.Data.Linq.dll
Devart.Data.dll
Devart.Data.Oracle.Linq.dll
Devart.Data.Oracle.dll
Thank you,
Glennn
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
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();
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
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."
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."
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
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."
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).glennn wrote:I presently am referencing:
Devart.Data.Linq.dll
Devart.Data.dll
Devart.Data.Oracle.Linq.dll
Devart.Data.Oracle.dll
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.