Posts
12
Comments
10
Trackbacks
0
March 2011 Entries
[EF + ORACLE] Updating and Deleting Entities

Prologue

In previous chapters we have seen how to insert data through EF, with and without sequences. In this one, we are going to see how to Update and delete Data from the DB.

Updating data

The update of the Entity Data (properties) is a very common and easy action.

Before of change any of the properties of the Entity, we can check the EntityState property, and we can see that is EntityState.Unchanged.

 

For making an update it is needed to get the Entity which will be modified. In the following example, I use the GetEmployeeByNumber to get a valid Entity:

   1: EMPLEADOS emp=GetEmployeeByNumber(2);
   2: emp.Name="a";
   3: emp.Phone="2";
   4: emp.Mail="aa";

After modifying the desired properties of the Entity, we are going to check again Entitystate property, which now has the EntityState.Modified value.

To persist the changes to the DB is necessary to invoke the SaveChanges function of our context.

   1: context.SaveChanges();

After modifying the desired properties of the Entity, we are going to check again Entitystate property, which now has the EntityState.Modified value.

To persist the changes to the DB is necessary to invoke the SaveChanges function of our context.

If we check again the EntityState property we will see that the value will be EntityState.Unchanged.

 

Deleting Data

Another easy action is to delete an Entity.

 

The first step to delete an Entity from the DB is to select the entity:

   1: CLIENTS  selectedClient = GetClientByNumber(15);
   2: context.CLIENTES.DeleteObject(clienteSeleccionado);

Before invoking the DeleteObject function, we will check EntityStet which value must be EntityState.Unchanged. After deleting the object, the state will be changed to EntitySate.Deleted.

To commit the action we have to invoke the SaveChanges function. Aftar that, the EntityState property will be EntityState.Detached.

Cascade

Entity Framework lets cascade updates and deletes, although I never see cascade updates.

What is a cascade delete?

A cascade delete is an action that allows to delete all the related object to the object we desire to delete.

This option could be established in the DB manager, or it could be in the EF model designer.

For example:

With a given relation (1-N) between clients and requests.

The common situation must be to let delete those clients whose have no requests.

If we select the relation between both entities, and press the second mouse button, we can see the properties panel of the relation. The props are:

This grid shows the relations indicating the Master table(Clients) and the end point (Cabecera or Requests)

The property “End 1 OnDelete” indicates the action to do when a Entity from the Master will be deleted. There are two options:

- None: No action will be done, it is said, if a Entity has details entities it could not be deleted.

- Cascade: It will delete all related entities to the master Entity.

If we enable the cascade delete in a relation, and we invoke the DeleteObject function of the set, we could observe that all the related object indicates a Entitystate.Deleted state.

Like an update, insert or common delete, until we commit the changes with SaveChanges function, the data would not be commited.

Si habilitamos el borrado en cascada de una relación, e invocamos a la función DeleteObject del conjunto, podremos observar que todas las entidades de Detalle (de la relación indicada) presentan el valor EntityState.Deleted en la propiedad EntityState.

Del mismo modo que en el borrado, inserción o actualización, hasta que no se invoque al método SaveChanges, los cambios no van a ser confirmados en la Base de Datos.

Finally

In this chapter we have seen how to update a Entity, how to delete an Entity and how to implement Cascade Deleting through EF.

In next chapters we will see how to query the DB data.

Posted On Wednesday, March 9, 2011 11:19 AM | Comments (1)
[EF + Oracle] Inserting Data (Sequences) (2/2)

Prologue

In the previous chapter we have see how to create DB records with EF, now we are going to Some Questions about Oracle.

 

ORACLE

One characteristic from SQL Server that differs from Oracle is “Identity”.

To all that has not worked with SQL Server, this property, that applies to Integer Columns, lets indicate that there is auto increment columns, by that way it will be filled automatically, without writing it on the insert statement.

In EF with SQL Server, the properties whose match with Identity columns, will be filled after invoking SaveChanges method.

In Oracle DB, there is no Identity Property, but there is something similar.

Sequences

Sequences are DB objects, that allow to create auto increment, but there are not related directly to a Table.

The syntax is as follows: name, min value, max value and begin value.

   1: CREATE SEQUENCE nombre_secuencia
   2: INCREMENT BY numero_incremento 
   3: START WITH numero_por_el_que_empezara 
   4: MAXVALUE valor_maximo | NOMAXVALUE 
   5: MINVALUE valor_minimo | NOMINVALUE 
   6: CYCLE | NOCYCLE 
   7: ORDER | NOORDER
   8:  

 

How to get sequence value?

To obtain the next val from the sequence:

   1: SELECT nb_secuencia.Nextval 
   2: From Dual

Due to there is no direct way to indicate that a column is related to a sequence, there is several ways to imitate the behavior: Use a Trigger (DB), Use Stored Procedures or Functions(…) or my particularly option.

EF model, only, imports Table Objects, Stored Procedures or Functions, but not sequences.

By that, I decide to create my own extension Method to invoke Next Val from a sequence:

   1: public static class EFSequence 
   2:     {
   3:         public static int GetNextValue(this ObjectContext contexto, string SequenceName) 
   4:         {
   5:             string Connection = ConfigurationManager.ConnectionStrings["JTorrecillaEntities2"].ConnectionString;
   6:             Connection=Connection.Substring(Connection.IndexOf(@"connection string=")+19);
   7:             Connection = Connection.Remove(Connection.Length - 1, 1);
   8:             using (IDbConnection con = new Oracle.DataAccess.Client.OracleConnection(Connection))
   9:             {
  10:                 using (IDbCommand cmd = con.CreateCommand())
  11:                 {
  12:                     con.Open();
  13:                     cmd.CommandText = String.Format("Select {0}.nextval from DUAL", SequenceName);
  14:                     return Convert.ToInt32(cmd.ExecuteScalar());
  15:                 }
  16:             }
  17:  
  18:          }
  19:     }

This Object Context’s extension method are going to invoke a query with the Sequence indicated by parameter. It takes the connection strings from the App settings, removing the meta data, that was created by VS when generated the EF model. And then, returns the next value from the Sequence.

The next value of a Sequence is unique, by that, when some concurrent users are going to create records in the DB using the sequence will not get duplicates.

This is my own implementation, I know that it could be several ways to do and better ways. If I find any other way, I promise to post it.

To use the example is needed to add a reference to the Oracle (ODP.NET) dll.

Posted On Thursday, March 3, 2011 4:56 PM | Comments (0)
[EF + Oracle] Inserting Data (1/2)

Prologue

Following EF series (I ,II y III) in this chapter we will see how to create DB record from EF.

Inserting Data

Like we indicated in the 2º post: “One Entity matches with a DB record, and one property match with a Table Column”.

To start, we need to create an object from one of the Entities:

   1: EMPLEADOS empleado = new EMPLEADOS();

Also like, I told previously, Exists the possibility to use the Static Function defined by VS for each Entity:

Once we have created the object, we can Access to it properties to fill like a common class:

 

   1: empleado.NOMBRE = "Javier Torrecilla";

 

After finish of fill our Entity properties, it must be needed to add the object to the appropriate ObjectSet in the ObjectContext:

   1: enti.EMPLEADOS.AddObject(empleado);

or

   1: enti.AddToEMPLEADOS(empleado);

Both methods will do the same action, create an insert statement.

Have we finished?

No.

Any Entity has a property called “EntityState”. This prop is an Enum from “EntityState”, which has the following:

  • Detached: the Entity is created, but not added to the Context.
  • Unchanged: There is no pending changes in the Entity.
  • Added: The entity is added to the ObjectSet, but it is not yet sent to the DB.
  • Deleted: The object is deleted form the ObjectSet, but not yet from the DB.
  • Modified: There is Pending Changes to confirm.

Let’s see, the several values of the property during the Creation steps:

1. While the Object is created and we are filling the props: EntityState.Detached;

2. After adding to the ObjectSet: EntityState.Added. This not indicated that the record is in the DB

3. Saving the Data:
To sabe the data in the DB, we are going to call “SaveChanges” method of the Object Context. After invoke it, the property will be EntityState.Unchanged.

     

    What does SaveChanges Method?

    This function will synchronize and send all pending changes to DB.

    It will add, modify or delete all Entities, whose EntityState property, is setted to Added, Deleted or Modified.

    After finishing, all added or modified entities will be change the State to “Unchanged”, and deleted Entities must take the “Detached” state.

    Posted On Thursday, March 3, 2011 4:24 PM | Comments (0)
    [EF + Oracle]Object Context

    Prologue

    After EF episodes I and II, we are going to see the Object Context.

    What is Object Context?

    It is a class which manages the DB connection, and the different Entities of our model.

    When Visual Studio creates the EF model, like I explain previously, also generates a Class that extends ObjectContext.

    ObjectContext provides:

    - DB connection

    - Add, update and delete functions.

    - Object Sets of Entities.

    - State of Pending Changes.

    This class will give a function, for each Entity, like 

    Esta clase va a contar con una función, para cada entidad, del tipo “AddTo{ENTITY}({Entity_Type } value)”, which are going to add a Entity to the related ObjectSet.

    In addition, it has a property, for each Entity, like “ObjectSet<TEntity> Entity”, does will keep the related record set. It will be filled with the CreateObjectSet<TEntity> function of Base class (ObjectContext).

    What is an ObjectSet?

    It is a class that allows us to manage the Entity Set from a Type.

    It inherits from:

    · ObjectQuery<TEntity>

    · IObjectSet<TEntity>

    · IQueryAble<TEntity

    · IEnumerable<TEntity

    · IQueryAble

    · IEnumerable

    An ObjectSet is a class property that allows query, insert, delete and update records from a determinate Entity.

    In following chapters we will see how to query Entities.

    LazyLoadingEnabled

    A very important property of the Context is “LazyLoadingEnabled”. This Boolean property lets indicate if the data loading is lazy, in other words, the Object will not be created and query until not be needed.

    Finally

    In this post we have seen what the VS generated context is, some of the characteristics, and where to see Entity data.

    In next chapters we will see, CRUD operations, and how to query ObjectSets.

    Posted On Thursday, March 3, 2011 3:53 PM | Comments (0)
    [EF + Oracle] Entities

    Prologue

    Following with the Serie I started yesterday about Entity Framework with Oracle, Today I am going to start talking about Entities.

    What is an Entity?

    A Entity is an object of the EF model corresponding to a record in a DB table. For example, let’s see, in Image 1 we can see one Entity from our model, and in the second one we can see the mapping done with the DB.

    (Image 1)

    (Image 2)

    More in depth a Entity is a Class inherited from the abstract class “EntityObject”, contained by the “System.Data.Objects.DataClasses” namespace. At the same time, this class inherits from the following Class and interfaces:

    • StructuralObject: It is an Abstract class that inherits from INotifyPropertyChanging and INotifyPropertyChanged interfaces, and it exposes the events that manage the Changes of the class, and the functions related to check the data types of the Properties from our Entity.
    •  IEntityWithKey: Interface which exposes the Key of the entity.
    • IEntityWithChangeTracker: Interface which lets indicate the state of the entity (Detached, Modified, Added…)
    • IEntityWithRelationships: Interface which indicates the relations about the entity.

    Which is the Content of a Entity?

    A Entity is composed by: Properties, Navigation Properties and Methods.

    What is a Property?

    A Entity Property is an object that represents a column from the mapped table from DB. It has a data type equivalent in .Net Framework to the DB Type.

    When we create the EF model, VS, internally, create the code for each Entity selected in the Tables step, such all methods that we will see in next steps.

    For each property, VS creates a structure similar to:

    · Private variable with the mapped Data type.

    · Function with a name like On{Property_Name}Changing({dataType} value): It manages the event which happens when we try to change the value.

    · Function with a name like On{Property_Name}Change: It manages the event raised when the property has changed successfully.

    · Property with Get and Set methods:

      • The Set Method manages the private variable and do the following steps:
        • Raise Changing event.
        • Report the Entity is Changing.
        • Set the prívate variable. For it, Use the SetValidValue function of the StructuralObject. There is a function for each datatype, and the functions takes 2 params: the value, and if the prop allow nulls.
      • Invoke that the entity has been successfully changed.
      • Invoke the Changed event of the Prop.

    ReportPropertyChanging and ReportPropertyChanged events, let, respectively, indicate that there is pending changes in the Entity, and the changes have success correctly. While the ReportPropertyChanged is raised, the Track State of the Entity will be changed.

    What is a Navigation Property?

    Navigation Properties are a kind of property of the type: EntityCollection<TEntity>, where TEntity is an Entity type from the model related with the current one, it is said, is a set of record from a related table in the DB.

    The EntityCollection class inherits from:

    · RelatedEnd: There is an abstract class that give the functions needed to obtein the related objects.

    · ICollection<TEntity>

    · IEnumerable<TEntity>

    · IEnumerable

    · IListSource

    For the previous interfaces, I wish recommend the following post from Jose Miguel Torres.

    Navigation properties allow us, to get and query easily objects related with the Entity.

    Methods?

    There is only one method in the Entity object. “Create{Entity}”, that allow us to create an object of the Entity by sending the parameters needed to create it.

    Finally

    After this chapter, we know what is an Entity, how is related to the DB and the relation to other Entities.

    In following chapters, we will se CRUD operations(Create, Read, Update, Delete).

    Posted On Wednesday, March 2, 2011 3:36 PM | Comments (0)
    [EF + Oracle] Intro

    Prologue

    I have a busy personal and working time, and at this moment that I start to get more free time, I decided to start a Serie about Entity Framework with Oracle.

    A few time ago, I got my first experience with EF and Oracle with Oracle 10 g express and Oracle 10 g with the same results, Doesn’t work.

    Now I download Oracle 11 g to Test again.

    Tools

    To start using EF with Oracle we need the following:

    1. Visual Studio 2010. No Express Edition

    2. Oracle 11g

    3 Oracle Driver for EF (ODAC)

    Intro

    People, who are starting with EF developments, I recommend to take a look into Unai Zorrilla’s Blog, the post were written in Spanish but they are great!

    To this Serie, we are going to define the DB from the Oracle administrator. For that we need to follow the next steps:

    1. Create a User with a PassWord. In my example the user will be Jtorrecilla

    2. Create a TableSpace

    3. Define some example tables

     

    (Image1)

    When we have created the DB, we are going to start a new project in VS 2010. I will start a C# Project.

    To start with EF, we need to add a new objet to our Project “ADO .NET Entity Data Model".

    (Image2)

    The next step will be to indicate that our model will be based on an existing DB, and indicate the connection string (Images 3 and 4):

    (Imagen3)

    (Imagen4)

    Once we selected the connection string, we will need to indicate that in the connection will be saved “Sensitive” data (Image 5), and in the next step we are going to select the DB objets to use in the project(Image 6).

     

    (Image 5)

    (Image 6)

    At the end, we will press Finish button, and it will generate a EDMX file to add to our solution, and in the IDE will appear the DB Schema with the selected Tables and Relations.

    (Imagen7)

    One Entity is composed by a set of properties (each matches with a column from the Table in the DB) and Navigation Properties that represents any relation with other Entities.

     

    Finally

    With this chapter we have installed the environment, defined a DB and configured the solution to start using EF with Oracle.

    In the next chapter we are going to see What is a Entity and how it works.

    I hope you enjoy this Serie!

    Posted On Tuesday, March 1, 2011 4:27 PM | Comments (6)