Tutorial 6: Working with Entities

Description Article
Table of Contents
Summary

CUD operations over objects are directly mapped to table and table rows at database level.

Data Model followed on code samples

Operations
Add new object entity

Creates a new record on a database table:

1) Instantiate new entity object;
2) Add to Table;
3) Propagate changes to the database through the data context

How to do it
using (TrainingDataContext dc = new TrainingDataContext(lvConnectionString))
{
	Customer lvCustomer = new Customer();
	lvCustomer.FirstName = "João";
	lvCustomer.LastName = "Seixas";

	dc.Customers.InsertOnSubmit(lvCustomer);

	dc.SubmitChanges();
}
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

How to do it
using (TrainingDataContext dc = new TrainingDataContext(lvConnectionString))
{
	Customer lvCustomer = dc.Customers.Single(c => c.CustomerID == "JPPS");
	lvCustomer.FirstName = "João Paulo";
	lvCustomer.LastName = "Seixas";

	dc.SubmitChanges();
}
Remove object entity

Remove an existent record on a database table:

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

How to do it
using (TrainingDataContext dc = new TrainingDataContext(lvConnectionString))
{
	Customer lvCustomer = dc.Customers.Single(c => c.CustomerID == "JPPS");
	dc.Customers.DeleteOnSubmit(lvCustomer);

	dc.SubmitChanges();
}
Add new hierarchy of object entities (one-to-one and one-to-many relations)

Creates new records on multiple database tables:

1) Instantiate new entity objects;
2) Add to correspondent Table;
3) Propagate changes to the database through the data context

How to do it
using (TrainingDataContext dc = new TrainingDataContext(lvConnectionString))
{
	Customer lvCustomer = new Customer {
								FirstName = "João",
								LastName = "Seixas"};

	Order lvOrder = new Order {
							Number = "ORD1",
                            RequiredDate = DateTime.Now,
                            Order_Details = new Order_Detail {
                                                    UnitPrice=10,
                                                    Quantity=10 }
							};

	lvCustomer.Orders.Add(lvOrder);

	dc.Customers.InsertOnSubmit(lvCustomer);

	dc.SubmitChanges();
}
Update hierarchy of object entities (one-to-one and one-to-many relations)

Update existent records on multiple database tables:

1) Retrieve main entity object from the database;
2) Change main entity data;
3) Change related-entities data;
4) Propagate changes to the database through the data context

Remarks

i. If a new sub-entity item is instantiated and added to the collection within the parent entity then the parent reference is automatically populated (e.g. new Order item added to the collection of Orders of a Customer);

ii. If instead a sub-entiity item is created or changed isolated of the parent entiity then the parent reference must be explicitly populated (e,g. new Order is created an saved)

iii. In a relation of type one-one, if a child data is altered then it is advised to 1) remove the parent+child entities; and 2) add the parent+new child entiity

How to do it
using (TrainingDataContext dc = new TrainingDataContext(lvConnectionString))

	Customer lvCustomer = dc.Customers.Single(c => c.CustomerID == "JJPS");
	lvCustomer.CompanyName = "João Seixas SA";

	// Update existent order item...
	Order lvOrder = lvCustomer.Orders.Where(item => item.OrderID == "ORD1").Single();
	lvOrder.Order_Details.Quantity = 20;

	// Add new order item...

	Order lvOrder2 = new Order
                    {
                        Number = "ORD2",
                        RequiredDate = DateTime.Now,
                        Order_Details = new Order_Detail {
                                                    UnitPrice=15,
                                                    Quantity=20 }
                    };

	lvCustomer.Orders.Add(lvOrder2);

	dc.SubmitChanges();
}
Delete hierarchy of object entities (one-to-one and one-to-many relations)

Remove existent records on multiple database tables:

1) Retrieve parent entity object from the database;
2) Remove on child entity objects on each Table objects;
3) Propagate changes to the database through the data context

Remarks

In case of one-to-many relationship, the child entiity objects must be removed before remove the parent. This operation is not required if the “DELETE CASCADE” property is activated on the foreign key (FK) at database level

How to do it
using (TrainingDataContext dc = new TrainingDataContext(lvConnectionString))
{
	Customer lvCustomer = dc.Customers.Single(c => c.CustomerID == "JPPS");

	// Delete child items...
	foreach (Order item in lvCustomer.Orders)
	{
		dc.Order_Details.DeleteOnSubmit(item.Order_Details);
        dc.Orders.DeleteOnSubmit(item);
    }

	// Delete parent item...
	dc.Customers.DeleteOnSubmit(lvCustomer);

	dc.SubmitChanges();
}