Converting Oracle Data Access to EF6

[MB34]

We have an old WCF web service that I'm attempting to convert to WebApi. It uses the old Oracle Data Access libraries and I'm attempting to convert it to EF6. I can't find much on accessing Oracle ref cursors with EF6.

I've got this function:

Code: Select all

    public List<Course> QrySuggestedCourses(int PID)
    {
        NaData v_NaDataAccess = new NaData();
        List<OracleParameter> param = new List<OracleParameter> 
                  { new OracleParameter {ParameterName = "PID", Value = PID,
                                         OracleDbType = OracleDbType.Varchar2}};
        DataSet v_DataSet = v_NaDataAccess.RefCursorFunction("W_SUGGESTED_COURSES_BY_PART", param);
        IEnumerable<DataRow> Enum = v_DataSet.Tables[0].Rows.Cast<DataRow>();
        List<Course> suggestedCourses = Enum.Select((x) => new Course(x)).ToList();

        return suggestedCourses;
    }

And need to convert it to EF6 which would be something like this:

Code: Select all

    public List<Course> GetSuggestedCourses(int PID)
    {
        var res = _Db.Database.SqlQuery<List<Course>>("begin select W_SUGGESTED_COURSES_BY_PART(" + PID.ToString() + ") from dual end;");
        return res.FirstOrDefault();
    }

The function being called returns a REF CURSOR.

We are using the DevArt Oracle libraries for EF6.

Any help here?

[Shalex]

1. Please specify the exact text of the error and its call stack you are currently receiving with your code.

2. Are you using XML or Fluent or Attribute mapping in your model?

3. JIC
We recommend you to use Entity Developer (Devart Entity Model, *.edml) instead of EDM Designer (ADO.NET Entity Data Model, *.edmx) because it is adjusted for working with Oracle and has an advanced functionality: http://www.devart.com/entitydeveloper/ed-vs-edm.html. Additionally, Entity Developer adds registration of EF6-provider in app.config automatically.
Entity Developer includes a set of the predefined templates which can generate the code for calling stored procedures from the code. Be aware the code generation for stored procedures is currently not supported by a predefined DbContext template with FluentMapping=True (it works in case of FluentMapping=False): http://blog.devart.com/entity-developer ... plate.html > Fluent Mapping Limitations. As a temporary workaround, you can implement methods to call stored procedures in a partial (to avoid overwriting by the designer) class manually. Does this help?

[MB34]

1. Haven't gotten an error because I haven't gotten that far, yet.
2. Not using mapping at all, I'm doing code-first because I know what columns are being returned.
3. Installed and ran. It doesn't even see the Oracle connector.

[MB34]

WTH would I need ANOTHER tool to incorporate EF using Oracle?

[Shalex]

Haven't gotten an error because I haven't gotten that far, yet.

1. Ok, there are no runtime errors yet. Are there any compilation errors in the meantime?

Installed and ran. It doesn't even see the Oracle connector.

2. Which EF6 provider ("Devart.Data.Oracle" or "Oracle.DataAccess.Client") are you going to use?
3. Have you installed dotConnect for Oracle Professional Trial (for using "Devart.Data.Oracle") or Entity Developer Professional Trial (for employing "Oracle.DataAccess.Client")?

[MB34]

I already have a commercial version of dotConnect but I don't see any tool to do this.

[Shalex]

Please specify the exact version (x.x.x) and edition of your dotConnect for Oracle. You can find out this information via the Tools > Oracle > About menu of Visual Studio.