none
Problems with bind variable in entity framework with Oracle data provider RRS feed

  • Question

  • We are testing entity framework with ODP.NET and linq to entities and are having a bit of a problem.

    We are running a simple linq to entities statement with a bind variable and our problem is that the column in the oracle database is of the type CHAR but entity framework is sending the bind variable in as NVARCHAR2. This causes oracle to not use the index on the column and instead run a full table scan.

    The entities are generated from the database.

    Linq:
    public string Name(string value)
    {
    var subject = (from s in ctx3.TABLE
    where s.COLUMN1== value
    select s.COLUMN2).ToList();
    return subject[0].ToString();
    }

    Is there a way to define the datatype for a bind variable when using linq?


    Monday, March 12, 2012 12:51 PM

Answers

  • Hi Allen_Li1988!

    I've already tried your suggestion, but its is not possible to change the type to anything else than primitive types.

    But fortunately I found the solution :)

    Got the answer from the oracle forums:

    -----------------------------------------------------

    Because .NET string is Unicode, by default NVARCHAR2 is used in parameter binding for string variables.

    Use EntityFunctions.AsNonUnicode() should be able to tell ODP.NET that you intend to treat a string as non Unicode.
    In this case, VARCHAR2 should be used for the binding.
    Hopefully that will avoid table scan for you when there is no more data conversion.

    using System.Data.Objects;
    public string Name(string value)
    {
    var subject = (from s in ctx3.TABLE
    where s.COLUMN1== EntityFunctions.AsNonUnicode(value)
    select s.COLUMN2).ToList();
    return subject[0].ToString();
    }

    Please let me know if that works for you.

    ----------------------------------

    This works perfectly.


    • Marked as answer by Birol Kilinc Thursday, March 15, 2012 9:10 AM
    Thursday, March 15, 2012 9:09 AM

All replies

  • Just a little followup:

    This code works fine, is there a way to set the parameter to AnsiString with Linq?:

    public string getValue2(string value)

    {

         stringtestSQL = "select s.column2 from Entities4.TABLE1 as s where s.column1 = @value";

         EntityConnectionec = newEntityConnection("name=Entities4");

         ec.Open();

         EntityCommandecmd = ec.CreateCommand();

         ecmd.CommandText = testSQL;

         varparam = newEntityParameter

         {

             ParameterName = "value",

             DbType = DbType.AnsiString,

             Value = value

         };

         ecmd.Parameters.Add(param);

     

         EntityDataReaderereader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess);

     

         while(ereader.Read())

         {

             returnereader.GetValue(0).ToString();

         }

         returnnull;

    }

    Tuesday, March 13, 2012 1:41 PM
  • Hi BirolKilinc,

    Welcome to MSDN Forum.

    Because the database is 3rd party, so I can't repro the issue, so I tested it in SQL Server2008 R2. After generating entities from database, you can modify the type of the conceptual model's properties in EDMX Designer. Right click the property in the model, and select Properties in context menu, then you can modify the type of this property in Properties Window.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, March 14, 2012 4:59 AM
    Moderator
  • Hi Allen_Li1988!

    I've already tried your suggestion, but its is not possible to change the type to anything else than primitive types.

    But fortunately I found the solution :)

    Got the answer from the oracle forums:

    -----------------------------------------------------

    Because .NET string is Unicode, by default NVARCHAR2 is used in parameter binding for string variables.

    Use EntityFunctions.AsNonUnicode() should be able to tell ODP.NET that you intend to treat a string as non Unicode.
    In this case, VARCHAR2 should be used for the binding.
    Hopefully that will avoid table scan for you when there is no more data conversion.

    using System.Data.Objects;
    public string Name(string value)
    {
    var subject = (from s in ctx3.TABLE
    where s.COLUMN1== EntityFunctions.AsNonUnicode(value)
    select s.COLUMN2).ToList();
    return subject[0].ToString();
    }

    Please let me know if that works for you.

    ----------------------------------

    This works perfectly.


    • Marked as answer by Birol Kilinc Thursday, March 15, 2012 9:10 AM
    Thursday, March 15, 2012 9:09 AM
  • Hi BirolKilinc,

    I'm glad to hear that you have solved the issue, and thanks for sharing the experience!

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, March 16, 2012 2:02 AM
    Moderator
  • IMHO this is a just a workaround, not an answer.  The model that was built by ODP.NET EF is fully aware of storage types, so it should figure out the issue on its own.  In our application this is a big problem, and I do not want to to start tracing every line of code in my app to add this function call everywhere.

    Is there a patch maybe for the fix?  I consider this behavior to be a bug in ODP.NET.

    Thanks.

    Sergey.


    Sergey

    Monday, May 13, 2013 6:14 PM
  • Thanks for sharing. According to this post, the issue is solved in ODAC 12c:

    https://forums.oracle.com/thread/2418340

    I did not try it out yet. Can anyone confirm the fix?

    -Henning

    Friday, December 13, 2013 2:23 PM
  • I was told by dev lead from Oracle that it was.

    Sergey

    Saturday, December 14, 2013 4:11 PM