How To: Model Inheritance in Databases
Playing with some Object Relational Mapping technologies these days such as Linq to SQL and ADO.Net Entity Framework, one of the capabilities that is important to check is which strategies of inheritance modeling is supported by each technology.
This post talks about how to model inheritance in the database, regardless the technology that we will use later to talk to the database.
Assuming that the business entities look somewhat similar to this:
There are three common strategies of modeling this class hierarchy in the database:
Table per Class Hierarchy
This strategy means that all the class hierarchy is stored in a single table in the database. The top level columns can allow nulls but all the lower level columns must allow nulls. There is an additional column (in the following example: PersonType) which is the discriminator - it holds a value indicating the level in the hierarchy to which the record matches.
This strategy is very useful when selecting rows from the database of multiple person types. The select goes only to this table and therefore more efficient. It is also very simple to add a column to all the person types - we just have to add it to this table. The downside of this strategy is that it cannot enforce constrains such as not allowing nulls in columns that are not in the top level. It also may lead to many columns with null values and therefore to wasting some disk space.
Table per Subclass
This strategy means that each level in the class hierarchy is represented in a table in the database. The top level table (Person) contains the columns that are common to all levels and as we go down in the hierarchy, we get only the additional columns for the specific level. The root table contains the discriminator columns, for joining with the appropriate table when selecting rows.
This strategy there can require less disk space, and can enforce required columns. It also allows adding new columns to subclasses without having to change other tables. The down side of this strategy is the cost of each select statements and the amount of joins it requires.
Table per Concrete Class
Since not all the classes in the above diagram are concrete (Person and Employee classes are abstract), there is no need to create tables for each level in the hierarchy. In this strategy each concrete class has its own table.
Notice that there isn't any table that contain any common columns. This means that adding columns to all tables should be done on each of the tables. Selecting rows is very efficient when selecting a single type of person, but can cost much more when we need to union between the tables in order to get multiple types or find the type of the person only by its primary key.
Conclusion
There are 3 common ways of modeling inheritance in databases, each on of them has its pros and cons. It is very important to be familiar with those strategies when investigating ORM technologies.
Enjoy!
Web Service Software Factory Modeling Edition Roadmap
Don Smith has published the September Drop of the new Web Service Software Factory Modeling Edition (formerly known as Service Factory v3). This drop contains only a newer version of the Service Factory Documentation, and can be found here.
Along with this drop, Don has updated the roadmap page for this software factory. Here are the highlights from this page:
- The final community release of the Web Service Software Factory Modeling Edition is planned for October 30th, 2007.
- This release contains the Service Contract Designer, Data Contract Designer and Host Designer.
- The WSDL Import feature will not be included in this release.
- The Data Access Guidance Package (Repository Factory) and the Security Guidance Package that were part of the December 2006 release of the Service Factory will not be included.
- The release will work on Visual Studio 2005 Team Editions only (Standard and Professional not supported).
- Support for Visual Studio 2008 is planned for a later release, after the final release of Visual Studio 2008. There may be drops along the way.
- Team System Rosario will ship with a Software Factory Platform built into it, and drops of the Service Factory built on top of this platform will ship up in the future CTPs of Rosario.
Enjoy!
In the last post I talked about Linq to SQL Attribute Based Mapping that lets you map CLR Objects to database objects using attributes. Although this approach is very simple and easy, it is compiled with the code and cannot be changed without recompiling. Another approach is Linq to SQL Xml Based Mapping that maps entities to database objects according to an xml file that is loaded in runtime.
So, given the two entities from the previous post, that have no mapping attributes at all:
class Order
{
public int Id { get; set; }
public DateTime? OrderDate { get; set; }
public string CustomerId { get; set; }
private EntityRef<Customer> _customerRef;
public Customer Customer
{
get { return this._customerRef.Entity; }
set { this._customerRef.Entity = value; }
}
}
and:
public class Customer
{
public string CustomerId { get; set; }
}
The Xml Based Mapping schema should look like:
<Database Name="Northwind"
xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
<Table Name="dbo.Customers" Member="Customers">
<Type Name="XmlBasedMapping.Customer">
<Column Name="CustomerID"
Member="CustomerId"
CanBeNull="false"
IsPrimaryKey="true" />
</Type>
</Table>
<Table Name="dbo.Orders" Member="Orders">
<Type Name="XmlBasedMapping.Order">
<Column Name="OrderID"
Member="Id" IsPrimaryKey="true" IsDbGenerated="true" />
<Column Name="CustomerID"
Member="CustomerId" DbType="NChar(5)" />
<Column Name="OrderDate"
Member="OrderDate" DbType="DateTime" />
<Association Name="Orders_Customers"
Member="Customers"
ThisKey="CustomerId"
OtherKey="CustomerId"
IsForeignKey="true" />
</Type>
</Table>
</Database>
The root element is the Database element. The child elements are the database objects the are included in the mapping - Customers and Orders tables from the Northwind database. Each table can have child types that are mapped to entities in the application. This hierarchy sits also with the concept of inheritance in Linq to SQL since it only supports the Table per Class Hierarchy strategy. In the above example each table is mapped to a single entity. Notice that each table column is mapped to a member in the class.
To work with this mapping source, we should load it from a file / stream / url or any other resource, and supply it as a parameter for the DataContext instance we want to work with.
string connectionString = "...";
// Load the Mapping from a file
XmlMappingSource mapping = XmlMappingSource.FromUrl("NorthwindMap.xml");
// Create a DataContext to the database, and supply
// the url for the mapping file
DataContext ctx = new DataContext(connectionString, mapping);
var query = from order in ctx.GetTable<Order>()
where order.CustomerId == "ALFKI"
select order;
foreach (Order order in query)
{
Console.WriteLine(order.Id + " " + order.OrderDate + " " + order.CustomerId);
}
Enjoy!
Linq to SQL Attribute Based Mapping
Linq to SQL has two mapping sources to map entities to tables in the database: Xml Mapping Source and Attribute Mapping Source. Developers use the graphic Object Relational Designer to design a model, and Visual Studio generates the code of the entities along with the attributes with mapping information.
In order to understand the mapping capabilities and how it works, I decided to start from an empty project, and create some entities without using the designer at all. This post is a step by step walkthrough of what I did in order to get things to work just like they would have worked with the designer and default generated code.
To get started, I created a new Console Application and added references to System.Data.Linq with contains the typed and extensions for using Linq to SQL. I also created a connection to Northwind sample database and copied the connection string.
Creating a Simple Entity
I created a new file with an empty class for the Order entity, and mapped it to the Orders table in the database.
[Table(Name="Orders")]
class Order
{
}
Notice that this entity has no fields in it, so I cannot really query by any field. In order to test that this mapping is working, I displayed the number of rows in the table.
string connectionString = "...";
DataContext ctx = new DataContext(connectionString);
var ordersTable = ctx.GetTable<Order>();
Console.WriteLine(ordersTable.Count());
Adding Entity Fields
The simplest way to map a property to a column is using the Column Attribute:
[Column(Name="OrderID")]
public int Id { get; set; }
and the property will be mapped to the table column. Very similar for a column that can except null values:
[Column(Name="OrderDate", CanBeNull=true)]
public DateTime? OrderDate { get; set; }
If the column has more complex DbType, it can be also expressed. Such as:
[Column(Name="CustomerID", DbType= "NChar(5)")]
public string CustomerId { get; set; }
With these properties in place, and the column mapping on top of them, we can query the Orders table:
DataContext ctx = new DataContext(connectionString);
var query = from order in ctx.GetTable<Order>()
where order.CustomerId == "ALFKI"
select order;
foreach (Order order in query)
{
Console.WriteLine(order.Id + " " + order.OrderDate + " " + order.CustomerId);
}
Insert, Update, Delete Support
Now that I queried for entities, I want to be able to add new entities, update entities and delete them.
If I try to insert a new order:
Order newOrder = new Order { CustomerId = "ALFKI", OrderDate = DateTime.Now };
ctx.GetTable<Order>().Add(newOrder);
ctx.SubmitChanges();
Console.WriteLine(newOrder.Id);
I get an exception: "Can't perform Create, Update or Delete operations on 'Table(Order)' because it is read-only." This is because the Order entity has no primary key mapping. Adding the required mapping and setting the primary key value to be database generated:
[Column(Name="OrderID", IsPrimaryKey=true, IsDbGenerated=true)]
public int Id { get; set; }
The insert command succeeds, and the generated Id is printed to the console.
Trying to delete an entity (the newly added order):
ctx.GetTable<Order>().Remove(newOrder);
ctx.SubmitChanges();
or trying to update an existing entity:
Order existing = ctx.GetTable<Order>().First();
existing.OrderDate = existing.OrderDate - new TimeSpan(1, 0, 0, 0);
ctx.SubmitChanges();
The commands succeed.
Relations (Association) with Other Entities
Each order is related to a single customer, according to the Customer ID column. In Linq to SQL, we would like to navigate between entities easily, and not selecting them again and again.
When associating one entity to another (1:1 relationship), we use a private member of type System.Data.Linq.EntityRef<T>, even though the property has the type of the target entity:
private EntityRef<Customer> _customerRef;
[Association(ThisKey = "CustomerId", IsForeignKey = true)]
public Customer Customer
{
get { return this._customerRef.Entity; }
set
{
this._customerRef.Entity = value;
}
}
where the Customer entity looks like:
[Table(Name="Customers")]
public class Customer
{
[Column(Name = "CustomerID", IsPrimaryKey = true, CanBeNull = false)]
public string CustomerId { get; set; }
}
And in order to query for a single order with its customer:
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Order>(ord => ord.Customer);
ctx.LoadOptions = dlo;
Order order = ctx.GetTable<Order>().First();
Conclusion
Linq to SQL Attribute Based Mapping is very straight forward and easy to get started with. It provides a simple way of mapping between the POCO entities (Plain Old CLR Objects) and database tables.
Astoria September 2007 CTP for Visual Studio 2008 Beta 2 is available
After the release of Visual Studio 2008 Beta 2, and the release of the ADO.Net Entity Framework Beta 2, here comes the new CTP of Project "Astoria" that works with it.
If you are not familiar with Astoria - it's goal is to enable applications to expose data as a data service that can be consumed by web clients within a corporate network and across the internet. The data service is reachable over HTTP, and URIs are used to identify the various pieces of information available through the service. Interactions with the data service happens in terms of HTTP verbs such as GET, POST, PUT and DELETE, and the data exchanged in those interactions is represented in simple formats such as XML and JSON.
To feel Astoria, check out Astoria - Hello World Style Walkthrough by Alexander Strauss.
Download the new bits from here, and read the announcement and what's new in this CTP here.
Enjoy!
Facebook is another social network that allows you to connect to people around you, and be in touch with them.
Recently, Microsoft has launched a partnership with Facebook which allows developers to create Facebook applications with Microsoft technologies such as Windows Forms, ASP.Net and Popfly.
To get started, download the Download the Facebook Developer Toolkit and see the QuickStarts.
Read more about the partnership and the toolkit here.
הוספת קוד לפוסטים - איך לעשות את זה נכון
פוסט זה הוא פוסט המשך בסדרה "טיפים וטריקים: כתיבת פוסטים באתר הבלוגים".
באפריל האחרון פיסרמתי את הפוסט אליה וקוד בה... המדבר על הוספת קטעי קוד לפוסטים ע"י שימוש ב- Windows Live Writer. מאז השתנו מספר דברים, יצאה גירסא חדשה של Windows Live Writer וגם גירסא חדשה של Visual Studio, ולכן החלטתי לכתוב על הנושא פעם נוספת.
כאשר מנסים לעשות Copy & Paste של קטע קוד מתוך Visual Studio לפוסט ב- WLW, זה יראה ככה:
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
כלומר ללא הפורמט (צבעים + סוג פונט) שהופכים את קוד לקריא עבור מפתחים.
על מנת להעתיק קוד מתוך Visual Studio ולשלב אותו בתוך פוסט יש להתקין את Copy Source As Html (שימו לב שיש גירסא שונה עבור Visual Studio 2005 ועבור 2008).
אחרי ההתקנה, אפשר לסמן קוד ב- Visual Studio ובלחיצה על הכפתור הימני מתקבלת האפשרות Copy As Html. בלחיצה על האפשרות הזאת הקוד שסימנתם מועתק ל- clipboard בצורה של HTML עם כל הגדרות הסגנון שלו כולל גודל וצבע.
כדי לחסוך את ביצוע הקליק הימני ובחירה אני יצרתי קיצור מקלדת ב- Visual Studio עבור הפקודה הזאת. אפשר לגשת ל- Tools ומשם ל- Options. במסך האפשרויות, תחת Environment לבחור ב- Keyboard. במסך שפתח, יש לחפש את הפקודה CopySourceAsHtml.Connect.CopyNow, ולשייך לה קיצור מקלדת נוח. אני בחרתי להשתמש ב- Ctrl + Shift + H.
אחרי שהעתקתם את הקוד המפורמט ל- Clipboard ואתם רוצים לשלב אותו בפוסט, כל מה שנשאר לעשות הוא לעשות Paste Special. אפשר להגיע לזה מתפריט ה- Edit, או ללחוץ על Ctrl + Shift + V. במסך שנפתח, יש לבחור באפשרות Keep Formatting, וללחוץ על OK.
בצורה כזאת הקוד בפוסט שלכם יראה ככה:
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
לסיכום-
אם אתם משתמשים ב- Windows Live Writer בשביל לכתוב פוסטים שמכילים קוד, מומלץ שתתקינו את Copy Source As Html ותגדירו את קיצור המקלדת בשבילו. בצורה כזאת קל מאד ונוח לשלב קטעי קוד בתוך הפוסטים.
משהו חשוב!
אם אתם כותבים פוסטים בעברית, ומקפידים להשתמש בתגית div align=right dir=rtl מסביב לטקסט העברי, הרי שכשתכתבו קוד תרצו שהוא יוצמד לשמאל. במקרה כזה, עליהם לסגור את ה- div העברי, ולפתוח div אנגלי (div align=left dir=ltr). אם אתם חוזרים לעברית אחרי הקוד, לא לשכוח לסגור את ה- div האנגלי ולחזור לעברי, וכן הלאה.
ואם יש לכם עוד שאלות, אשמח לעזור!
תהנו!