Navigation Properties with Database.NET

Find working examples on GetHub

For those that have used Entity Framework, navigation properties work in much the same way. There are some key differences however, in the way that they are setup. For those that are not familier with Entity Framework, navigation properties allow you to create relationships between your table's objects. For example instead of performing:

    
using (var shopContext = new ShopContext(FILE_NAME))
{
    var order = shopContext.Orders.First(o => o.OrderId == 5);
    var orderItems = shopContext.OrderItems.Where(i => i.Order_Id == order.OrderId);
}
        
    

You can use:

    
using (var shopContext = new ShopContext(FILE_NAME))
{
    var order = shopContext.Orders.First(o => o.OrderId == 5);
    //Figures out the order items referenced by the order automatically
    var orderItems = order.OrderItems; 
}
        
    

This is an oversimplification and does not fully define the benefit of having navigation properties. For a more in depth understanding you can refer to Entity Framework. Please keep in mind that although the purpose of navigation properties in Database.NET is the same as Entity Framework, the implementation is different.

Setting Up Navigation Properties

In this example we are going to have three POCO class that represent tables in our database.
Order - To contain our customer information OrderItem - To identify the item and quantity purchased Item - The description and price of the product

Order will be related to OrderItem and OrderItem will be related to Item
The Order POCO class id defined as follows:

    
public class Order
{
    [Key]
    [AutoGenerated]
    public int OrderId { get; set; }

    [Size(50)]
    public string CustomerName { get; set; }

    //The NavigationCollection attributes tells Database.Net
    //what property to look at on the related(OrderItems) table to find
    //the relationship
    [NavigationCollection("Order")]
    public virtual NavigationCollection OrderItems { get; set; }
}
        
    

You will notice this property is virtual and the property type is of type NavigationCollection. The NavigationCollection class is created when the class is instantiate by Database.Net. Either through the CreateProxy method or by requesting a POCO object from the DbContext. In order for a Navigation Property to work the property must be virtual.
The OrderItems property tells Database.net that there is a reference to the OrderItems table. It does not however, tell the system what keys define that relationship. In order for Database.Net to identify the keys that make up the relationship is looks at the NavigationCollectionAttribute for the name of the Property on the OrderItem class that defines the relationship.
Take a look at the OrderItems class.

    
public class OrderItem
{
    [Key]
    [AutoGenerated]
    public int OrderItemId { get; set; }

    //This is the foreign key to the order
    public int Order_Id { get; set; } 

    //This is the foreign key to the item
    public int Item_Id { get; set; }

    public int Quantity { get; set; }

    [Relationship("Order_Id", "OrderId")]
    public virtual Order Order { get; set; }

    [Relationship("Item_Id", "ItemId")]
    public virtual Item Item { get; set; }
}
        
    

You will notice that the OrderItems class has a property called Order. This property has a RelationshipAttribute that defines the reference between the Orders and the OrderItems table.
The first parameter in the RelationshipAttribute is the name of the property that is the Foreign Key of the table, in this case the OrderItems table. The second parameter is Primary Key on the referenced table. In this case the Orders table.
And again the Order property is virtual.

The RelationshipAttribute does not only work with Foreign Key to Primary Key relationships. A Foreign Key to Foreign Key relationship is also supported.
You can create relationships that depend on multiple values by adding additional RelationshipAttributes to the property.

The OrderItem class also has a property named Item. This property is defining a relationship to the Items table. Again the first parameter in the RelationshipAttribute is the local property that defines the Foreign Key (Item_Id). And the second is the name of the parameter on the Items table that is the Primary Key (ItemId).
Take a look at the Item class.

    
public class Item
{
    [Key]
    [AutoGenerated]
    public int ItemId { get; set; }

    [Size(50)]
    public string Name { get; set; }

    public decimal Price { get; set; }
}
        
    

You can see the Item class does not have any references defined. We could add one but it is unlikely that we would want one in this scenaro.
You will also hopefully note that neither the OrderItem class nor the Item class have a property of type NavigationCollection defined. That is because NavigationCollection is only required in scenarios with one to many relationships.
An order can have many OrderItems, but an OrderItem can only have one Order and one Item.

Using Navigation Properties

Once Navigation Properties are setup you can more easily manage relationships between tables. The following example shows how you can add OrderItems to a newly created order.

    
using (var shopContext = new ShopContext(FILE_NAME))
{
    var order = shopContext.Orders.CreateProxy();
    order.CustomerName = "John Doe";

    var pillow = shopContext.Items.First(i => i.Name == "Pillow");
    var blanket = shopContext.Items.First(i => i.Name == "Blanket");

    var orderItem1 = shopContext.OrderItems.CreateProxy();
    orderItem1.Item = pillow;
    orderItem1.Quantity = 2;
    order.OrderItems.Add(orderItem1);

    var orderItem2 = shopContext.OrderItems.CreateProxy();
    orderItem2.Item = blanket;
    orderItem2.Quantity = 1;
    order.OrderItems.Add(orderItem2);

    shopContext.Orders.Add(order);

    shopContext.SaveChanges();
}
        
    

What makes this nice is that we didn't need to know any Id's, we just needed the objects. In addition this order and all of it's OrderItems will be created and saved in a Transaction. Transactions will be discussed in detail in another section.
Removing an OrderItem from and order is simple to.

    
public static void RemoveItem()
{
    using (var shopContext = new ShopContext(FILE_NAME))
    {
        var order = shopContext.Orders.First();

        var orderItem = order.OrderItems.First(i => i.Item.Name == "Pillow");

        //Does not delete the item from the database but set the foreign key to the default value
        order.OrderItems.Remove(orderItem); 

        shopContext.SaveChanges();       
    }
}
        
    

It is important to know that when removing the OrderItem from the order using the navigation property it does not delete the OrderItem from the database. Instead it sets the Order_Id property on the OrderItem to default value (null if a nullable value is supported) effectively removing it from the order. This is so that we don't accidentally delete rows that are being referenced by other tables. It does have the potential to create orphaned rows if you're not careful.
To remove the records from the database you would instead write the code like this:

    
public static void RemoveItem()
{
    using (var shopContext = new ShopContext(FILE_NAME))
    {
        var order = shopContext.Orders.First();

        var orderItem = order.OrderItems.First(i => i.Item.Name == "Pillow");
        
        //If you want to delete the item from both the order and the database do this line
        shopContext.OrderItems.Remove(orderItem);

        shopContext.SaveChanges();
    }
}
        
    

Now let's say you wanted to display all the order information. Navigation Properties make it easy.

    
private static void DisplayOrderItems(Order order)
{    
    Console.WriteLine(Order.CustomerName);

    Console.WriteLine("Order Items");
    foreach(var orderItem in order.OrderItems)
    {
        Console.WriteLine($"ID: {orderItem.Item.ItemId} Name: {orderItem.Item.Name} Price: {orderItem.Item.Price} OrderId: {orderItem.Order.OrderId} Quantity: {orderItem.Quantity}");
    }
}