Working with Entities

Description and code samples
Table of Contents
Operations
Add new object entity

Creates a new record on a database table:

1) Instantiate new entity object;
2) Register it in the context using the Add() method on the correspondent DbSet;
3) Propagate changes to the database through the data context

Remarks

Entity Framework uses the configured mapped information to construct an SQL INSERT statement that targets the entity database table.

If the PK of the new record is an IDENTITY column, Entity Framework includes some additional SQL to fetch this newly created value after the INSERT statement has executed. Entity Framework will then take the returned value and assign it to the primary key property of the entity object that was added.

How to do it
using (var context = new BreakAwayContext())
  {
    var machuPicchu = new Destination
    {
      Name = "Machu Picchu",
      Country = "Peru"
    };

    context.Destinations.Add(machuPicchu);

    context.SaveChanges();
  }

Generated SQL:

exec sp_executesql N'
insert [baga].[Locations]
    ([LocationName], [Country], [Description], [Photo])
    values (@0, @1, null, null)

select [LocationID]
    from [baga].[Locations]
    where @@ROWCOUNT > 0 and [LocationID] = scope_identity()',

N'@0 nvarchar(max) ,@1 nvarchar(max) ',
@0=N'Machu Picchu',@1=N'Peru'
Update object entity

Update an existent record on a database table:

1) Retrieve entity object from the database;
2) Change entity data;
3) Propagate changes to the database through the data context

Remarks

Entity Framework uses the configured mapped information to construct an SQL UPDATE statement that targets the entity database table. It uses the object primary key property to identify the record on the database.

How to do it
  using (var context = new BreakAwayContext())
  {
    var canyon = (from d in context.Destinations
                       where d.Name == "Grand Canyon"
                       select d).Single();

    canyon.Description = "227 mile long canyon.";

    context.SaveChanges();
  }

Generated SQL:

exec sp_executesql N'
update [baga].[Locations]
    set [Description] = @0
    where ([LocationID] = @1)
',N'@0 nvarchar(500),@1 int',
@0=N'227 mile long canyon.',@1=1
Remove object entity

Remove an existent record on a database table:

1) Retrieve entity object from the database;
2) Remove on the correspondent DbSet using the Remove() method;
3) Propagate changes to the database through the data context

Remarks

Calling DbSet.Remove() method on an entity that has been added but not yet saved to the database will cancel the addition of the entity: the entity is removed from the change tracker and is no longer tracked by DbContext.

Note: The object must be tracked by the change tracker and marked as state Deleted in order for DbContext.SaveChanges() to construct a SQL DELETE command to propagate the deletion to the database.

How to do it
 using (var context = new BreakAwayContext())
  {
    var bay = (from d in context.Destinations
               where d.Name == "Wine Glass Bay"
               select d).Single();

    context.Destinations.Remove(bay);
    context.SaveChanges();
  }

Gnerated SQL:

exec sp_executesql N'
delete [baga].[Locations]
    where ([LocationID] = @0)',
N'@0 int',
@0=3
Remove object entity without load from database

If the entity to delete is knows but not yet in memory, it´s not necessary to load the entity into memory: a stub (i.e. an instance of the class with only the KEY property populated) can be created and attached to the context:

1) Create stub class iinstance;
2) Attach to the data context using the Attach() method on the correspondent DbSet;
3) Propagate changes to the database through the data context

you can attach a stub that represents the entity to be deleted, and then delete this stub. A stub is an instance of an entity that just has the key value assigned. The key value is all that’s required for deleting entities.

When attaching a stub you use the DbSet.Attach method to let Entity Framework know that it’s an existing entity. Once an entity is attached, it behaves just like an entity that was retrieved from the database. So calling DbSet.Remove will cause a DELETE statement to be sent to the database during SaveChanges. For example, the following code would delete the Destination with an ID of 2, without loading it from the database:

Remarks

An entity can also be deleted without loading from the database using the DbContext.Database.ExecuteSQLCommand() to execute raw SQL

How to do it

Removing an entity using a stub object:

using (var context = new BreakAwayContext())
{
  var toDelete = new Destination { DestinationId = 2 };

  context.Destinations.Attach(toDelete);

  context.Destinations.Remove(toDelete);

  context.SaveChanges();
}

Removing an entity using a raw SQL command :

context.Database.ExecuteSqlCommand(
  "DELETE FROM baga.Locations WHERE LocationName = 'Hawaii'");
Remove object entity with related data (Optional one-to-one and one-to-many relations)

Deleting objects that have related data, may obligate to update related data for the delete to succeed.
The required updates to related data depend on whether the relationship is optional or required, i.e. a foreign key exist:

  • Optional relationships mean that the child entity can exist in the database without a parent assigned (i.e. FK column can be set to NULL)
  • Required relationships mean the child entity cannot exist without a parent assigned (i.e. FK column cannot be set to NULL).

To delete successfuly an optional relationship:

1) Load into memory the entity object to delete;
2) Load into memory the entity or entities objects related to the entity to delete;
3) Remove the entity object obtained on (1)

The delete operation will originate two SQL statements (see remarks):

1) An UPDATE statement to set the FK column on the related record, mapped to the related object entity, to NULL;
2) A DELETE statement to delete the record mapped to the entity object to delete.

Remarks

When deleting an entity that is the parent of an optional relationship, the relationship between the parent and any child entities can be deleted too. This means the child entities will be updated so that they are no longer assigned to a parent, i.e. the foreign key column in the database will be set to NULL.

If the child entity or entities have been loaded into memory from the database, when calling the () method Entity Framework will automatically delete the relationship, otherwise an exception of type DbUpdateException that encapsulates a SqlException originated because exists a record with a FK pointing to the record being deleted.

How to do it

Removing a relationship in error:

using (var context = new BreakAwayContext())
{
    var trip = (from t in context.Trips
                where t.Description == "Trip from the database"
                select t).Single();

    context.Trips.Remove(trip);
    context.SaveChanges();
 }

SQL exception:

The DELETE statement conflicted with the REFERENCE constraint ‘FK_Reservations_Trips_Trip_Identifier

Removing a relationship successfully:

using (var context = new BreakAwayContext())
{
    var trip = (from t in context.Trips
                where t.Description == "Trip from the database"
                select t).Single();

    var res = (from r in context.Reservations
                where r.Trip.Description == "Trip from the database"
                select r).Single();

    context.Trips.Remove(trip);
    context.SaveChanges();
}

 

Remove object entity with related data (Required one-to-one and one-to-many relations)

Deleting objects that have related data, may obligate to update related data for the delete to succeed.
The required updates to related data depend on whether the relationship is optional or required, i.e. a foreign key exist:

  • Optional relationships mean that the child entity can exist in the database without a parent assigned (i.e. FK column can be set to NULL)
  • Required relationships mean the child entity cannot exist without a parent assigned (i.e. FK column cannot be set to NULL).

When deleting an entity that is the parent of an required relationship, the child entities must be deleted too. This can be done automatically or manually if a CASCADE delete option is configured either in the model or database.

To delete successfuly a required relationship (CASCADE option defined in the model):

1) Load into memory the entity object to delete;
2) Load into memory the entity or entities objects related to the entity to delete;
3) Remove the entity object obtained on (1)

To delete successfuly a required relationship (CASCADE option defined in the database):

1) Load into memory the entity object to delete;
2) Remove the entity object obtained in (1)

To delete successfuly a required relationship (no CASCADE option defined neither in the model nor database):

1) Load into memory the entity object to delete;
2) Load into memory the entity or entities objects related to the entity to delete;
3) Remove each of the related object entities obtained on (2)
4) Remove the entity object obtained in (1)

The remotion operation will originate DELETE SQL statements (see remarks):

1) One DELETE statement to delete each of the related child records, mapped to the related object entities;
2) A DELETE statement to delete the record mapped to the entity object to delete.

A fourth alternative is to assign to the child entities a new parent before remove the target entitiy:

1) Load into memory the entity object to delete;
2) Load into memory the entity or entities objects related to the entity to delete;
3) Load the new parent object entity;
4) Assign the new parent obtained on (3) to each of the child entities obtained on (2) / Assign the foreign key proeprty to the primary key of the new parent entity (in case of a Foreign Key Association);
5) Remove the entity object obtained on (1)

Remarks

For an automatically deletion of the child entity or entities by Entity Framework, they must have been loaded into memory from the database, otherwise when calling the () method will throw an exception.

How to do it

Option 1: CASCADE option defined in the model

using (var context = new BreakAwayContext())
  {
    var canyon = (from d in context.Destinations
                  where d.Name == "Grand Canyon"
                  select d).Single();

    context.Entry(canyon)
      .Collection(d => d.Lodgings)
      .Load();

    context.Destinations.Remove(canyon);
    context.SaveChanges();
  }

Option 2: CASCADE option defined in the database

using (var context = new BreakAwayContext())
  {
    var canyon = (from d in context.Destinations
                  where d.Name == "Grand Canyon"
                  select d).Single();

    context.Destinations.Remove(canyon);
    context.SaveChanges();
  }

Option 3: No CASCADE option defined

using (var context = new BreakAwayContext())
{
    var canyon = (from d in context.Destinations
                  where d.Name == "Grand Canyon"
                  select d).Single();

    context.Entry(canyon)
      .Collection(d => d.Lodgings)
      .Load();

    foreach (var lodging in canyon.Lodgings.ToList())
    {
      context.Lodgings.Remove(lodging);
    }

    context.Destinations.Remove(canyon);
    context.SaveChanges();
}
Add a graph of disconnected or new entiy objects

The process of finding related entities is recursive, therefore all the related entity objects are added to the context.

Remarks

If a reference is found to an entity that is already tracked by the context, the entity state is left in its current state.

How to do it
var essex = new Destination
  {
    Name = "Essex, Vermont",
    Lodgings = new List<Lodging>
    {
      new Lodging { Name = "Big Essex Hotel" },
      new Lodging { Name = "Essex Junction B&B" },
    }
  };

  using (var context = new BreakAwayContext())
  {
    context.Destinations.Add(essex);

    Console.WriteLine(
      "Essex Destination: {0}",
      context.Entry(essex).State);

    foreach (var lodging in essex.Lodgings)
    {
      Console.WriteLine(
        "{0}: {1}",
        lodging.Name,
        context.Entry(lodging).State);
    }

    context.SaveChanges();
  }