Ignore accents on search using linq to entities (EF) and Oracle 12.1

Question

I need to perform a search on a table whith a string field that contains accents, many operators could be applied: start with, contains, equal, in the list ... If I do a search for Müller I want retrieve also Mueller (ue is the translate of ü in German), the same for the other letters having accents, I know that it is possible to achieve this by modifiying the NLS_COMP and NLS_SORT

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
SQL> ALTER SESSION SET NLS_SORT=BINARY_AI;

I know also that it is possible to Collation at column level but this is availble only since 12.2 version, Any idea please ?

Thank you for your help,

Bilel

Possible duplicate of [Compare Strings ignoring accents in SQL (ORACLE)](https://stackoverflow.com/questions/35689157/compare-strings-ignoring-accents-in-sql-oracle) — Chrᴉz remembers Monica, Feb 12 '19 at 10:38
No, this is not a duplicate, this approach not working for all letters — Bilel Chaouadi, Feb 12 '19 at 10:41
Then please add some more examples, your `Mestlé` works with the solution there — Chrᴉz remembers Monica, Feb 12 '19 at 10:45
Finally I'll use NLS_COMP and NLS_SORT parameters, do you have any idea hoaw can set oracle session parameters using entity framework before the query execution ? — Bilel Chaouadi, Feb 13 '19 at 08:52
I remember we once had this done in the dbcontext constructor, but I'm not sure. see [this](https://stackoverflow.com/questions/40886762/entity-framework-setting-session-context-using-idbconnectioninterceptor) and [this](https://stackoverflow.com/questions/14372297/execute-sql-alter-commands-for-every-session-with-entity-framework-5-talking-to?rq=1) link, they might help you. — Chrᴉz remembers Monica, Feb 13 '19 at 09:16
I know that we can use the Database.Connection.State Change event in the constructor, but we want ignore accents on search only if a user check a checkbox. — Bilel Chaouadi, Feb 13 '19 at 10:55

score 2 · Accepted Answer · answered Feb 13 '19 at 13:52

I've used oracle NLS session parameters to resolve my issue.

 if(condition == true)
   AlterSortSession(context);

 public void AlterSortSession(MyContext context)
 {
   var connection = (OracleConnection)context.Database.Connection;
   connection.StateChange += AlterSortSession;
 }

 private static void AlterSortSession(object sender, StateChangeEventArgs e)
 {
  if (e.CurrentState != ConnectionState.Open)
    return;

  var connection = (OracleConnection)sender;
  OracleGlobalization info = connection.GetSessionInfo();

  info.Sort = "XGERMAN_DIN_AI";
  info.Comparison = "LINGUISTIC";

  connection.SetSessionInfo(info);
}

Documentation is available here for OracleGlobalization

score 0 · Answer 2 · answered Jan 21 '20 at 22:48

This worked for me for EF4:

using (var context = new Entities()) {

    // Set Case Insensitive, Accent Insensitive

    var orcl = (OracleConnection)(((System.Data.EntityClient.EntityConnection)(context.Connection)).StoreConnection);

    if (context.Connection.State != System.Data.ConnectionState.Open)
    {
        context.Connection.Open();
    }

    var sInfo = orcl.GetSessionInfo();

    sInfo.Sort = "GENERIC_M_AI";
    sInfo.Comparison = "LINGUISTIC";

    orcl.SetSessionInfo(sInfo);

    // Execute linq query

    var row = context.table.Where(a => a.varcharField.Contains("match str")).FirstOrDefault();

}

Ignore accents on search using linq to entities (EF) and Oracle 12.1

2 Answers2