This discussion is archived
8 Replies Latest reply: Jan 16, 2013 12:32 PM by 979840 RSS

How to handle Autoincrement Sequence using oracle entity framework

892530 Newbie
Currently Being Moderated
How can auto sequence number can be handled without using trigger/stored procedure in oracle.
  • 1. Re: How to handle Autoincrement Sequence using oracle entity framework
    user10898605 Newbie
    Currently Being Moderated
    Yes,I'll keeping watch it!
  • 2. Re: How to handle Autoincrement Sequence using oracle entity framework
    Tridus Journeyer
    Currently Being Moderated
    To the best of my knowledge, it can't. Entity Framework expects the database to handle this (SQL Server has it as a column property for example).

    Oracle simply doesn't work that way, so you have to use a trigger to populate the value if you don't supply it. Personally I just write my code such that I've got a simple helper function that can select the sequence and I populate the value myself as part of the create.
  • 3. Re: How to handle Autoincrement Sequence using oracle entity framework
    user10898605 Newbie
    Currently Being Moderated
    Thank you! This is seem to better than trigger on the table!

    I beg you publish your code, thanks a lot!

    My email is Jarry_liu@hotmail.com

    Edited by: user10898605 on 2011-12-6 上午9:09
  • 4. Re: How to handle Autoincrement Sequence using oracle entity framework
    Tridus Journeyer
    Currently Being Moderated
    Sure, though it's nothing terribly fancy. All you have to do is set up a function somewhere that's something like this:

    public int GetNextSequenceValue(CommitteeConstants.SequenceTypes sequenceType)
    {

    using (CommitteeModelConnection ctx = new CommitteeModelConnection())
    {
    IList<decimal> sequence;
    switch (sequenceType)
    {
    case CommitteeConstants.SequenceTypes.COMMITTEE:
    sequence = ctx.Database.SqlQuery<decimal>("SELECT " + ConfigurationManager.AppSettings["DatabaseSchema"] + "CMTE_ID_SEQ.NEXTVAL FROM DUAL").ToList();
    return Convert.ToInt32(sequence[0]);
    case CommitteeConstants.SequenceTypes.AGREEMENT:
    sequence = ctx.Database.SqlQuery<decimal>("SELECT " + ConfigurationManager.AppSettings["DatabaseSchema"] + "AGMT_ID_SEQ.NEXTVAL FROM DUAL").ToList();
    return Convert.ToInt32(sequence[0]);
    case CommitteeConstants.SequenceTypes.CONTACT:
    sequence = ctx.Database.SqlQuery<decimal>("SELECT " + ConfigurationManager.AppSettings["DatabaseSchema"] + "CONTACT_ID_SEQ.NEXTVAL FROM DUAL").ToList();
    return Convert.ToInt32(sequence[0]);
    case CommitteeConstants.SequenceTypes.ADDRESS:
    sequence = ctx.Database.SqlQuery<decimal>("SELECT " + ConfigurationManager.AppSettings["DatabaseSchema"] + "ADDRESS_ID_SEQ.NEXTVAL FROM DUAL").ToList();
    return Convert.ToInt32(sequence[0]);
    case CommitteeConstants.SequenceTypes.PHONE_NUMBER:
    sequence = ctx.Database.SqlQuery<decimal>("SELECT " + ConfigurationManager.AppSettings["DatabaseSchema"] + "PHONENUM_ID_SEQ.NEXTVAL FROM DUAL").ToList();
    return Convert.ToInt32(sequence[0]);
    case CommitteeConstants.SequenceTypes.ORGANIZATION:
    sequence = ctx.Database.SqlQuery<decimal>("SELECT " + ConfigurationManager.AppSettings["DatabaseSchema"] + "CMTEORG_ID_SEQ.NEXTVAL FROM DUAL").ToList();
    return Convert.ToInt32(sequence[0]);
    default:
    throw new ArgumentException("Unknown sequence type: " + sequenceType.ToString());



    }
    }
    }

    The sequence is pretty simple:

    public enum SequenceTypes
    {
    COMMITTEE,
    AGREEMENT,
    CONTACT,
    ADDRESS,
    PHONE_NUMBER,
    ORGANIZATION
    }


    Then you just call it and set the ID of something you're adding to whatever it returns.

    Note that I'm using the DbContext stuff, which isn't the default EF generates. To use the ObjectContext stuff instead, you'll need to call context.ExecuteStoreQuery(). Here's a sample on how to do that: http://stackoverflow.com/questions/3962541/how-to-run-sql-queries-on-ef

    Edited by: Tridus on Dec 7, 2011 10:05 AM
  • 5. Re: How to handle Autoincrement Sequence using oracle entity framework
    user10898605 Newbie
    Currently Being Moderated
    thank you very much!
    But why do you define IList<decimal> sequence and return Convert.ToInt32(sequence[0]) ?

    Can you define Decimal sequece and return Convert.ToDecimal(sequence) ?

    it seem to better...

    I hope to have a better performance!

    Edited by: user10898605 on 2011-12-8 上午4:14
  • 6. Re: How to handle Autoincrement Sequence using oracle entity framework
    Tridus Journeyer
    Currently Being Moderated
    Well, some of that is because this is old code that I haven't cleaned up since beta 1. My bad. :)

    In order to use decimal sequence you'd have to add a First() onto the query, like this:

    sequence = ctx.Database.SqlQuery<int>("SELECT " + ConfigurationManager.AppSettings["DatabaseSchema"] + "CMTE_ID_SEQ.NEXTVAL FROM DUAL").First();
    return Convert.ToInt32(sequence);

    That would be better.

    As for why I'm using Convert.ToInt32, it's because the query returns a decimal type but what I actually want is an int because that's the type of the Id fields where I'm going to use the value. So I just convert it here and output what I want in order to avoid having to convert it later. If your autoincrement field is a decimal type in your model, then you won't have to convert it to anything.
  • 7. Re: How to handle Autoincrement Sequence using oracle entity framework
    user10898605 Newbie
    Currently Being Moderated
    Thank you very much!
    that's a good ideal! But I'll continued to improve it.
  • 8. Re: How to handle Autoincrement Sequence using oracle entity framework
    979840 Newbie
    Currently Being Moderated
    Thank you for the code. Worked Great! Here is my little take on it:

    public class OracleHelperService : IOracleHelperService
    {
    public int GetNextSequenceValue(SequenceNumber sequenceNumber)
    {
    using (WSDBContext context = new WSDBContext())
    {
    IList<int> sequence;
    switch (sequenceNumber)
    {
    case SequenceNumber.LeadToSFDC:
    sequence =
    context.Database.SqlQuery<int>("SELECT ws_lead_to_sfdc_lead_out.NEXTVAL FROM DUAL")
    .ToList();
    return sequence[0];
    default:
    return 0;

    }
    }
    }
    }


    IOracleHelperService oracleHelperService = new OracleHelperService();

    int nextValue = oracleHelperService.GetNextSequenceValue(SequenceNumber.LeadToSFDC);

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points