Page 1 of 1
How to use the UNDERSCORE (_) find/replace in my LINQ query
Posted: Wed 13 May 2015 22:09
by glennn
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
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
Posted: Thu 14 May 2015 13:22
by Shalex
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.
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
Posted: Thu 14 May 2015 15:03
by glennn
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
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
Posted: Fri 15 May 2015 10:23
by MariiaI
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.
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
Posted: Fri 29 May 2015 13:40
by sunece87
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."
Re: How to use the UNDERSCORE (_) find/replace in my LINQ query
Posted: Tue 02 Jun 2015 06:02
by MariiaI
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.