VITA Tutorial
Part 2. More attributes, entity lists, computed attributes, paging
Outline
In this part of the tutorial we will see how to create and use list-type properties of entities mapping to either one-to-many or many-to-many relationships in the database; we will use Enum-typed properties; computed properties; we will use various VITA-defined attributes in our entities to invoke some automatic behavior; we will introduce a convenient practice of using extension methods for manipulating entities; finally, we will see how to perform paged queries.
Extending the Books model: more entities and attributes
Let's start with an expanded definition of the
IPublisher entity:
[Entity, OrderBy("Name")]
public interface IPublisher {
[PrimaryKey, Auto]
Guid Id { get; } // no need for setter with Auto attribute
string Name { get; set; }
IList<IBook> Books { get; }
}
There are a few new things in this in this definition.
- Auto attribute on Id property - this simply tells the framework to auto-generate the Id value for new publisher instances - we no longer need to assign it in the code.
- OrderBy on the interface itself - this specifies the default ordering when we return entity lists using 'session.GetEntities<IPublisher>()' method. You can specify more than one property name here (names separated by comma), each property name may be followed by ':DESC' specification.
- Books property, a list of IBook entities. It returns a list of books published by a publisher. It all happens automatically - when the system 'sees' this property during initial model analysis, it tries to find a back reference from IBook to IPublisher - there is such property, IBook.Publisher. So it puts an automatic query action that is invoked when application code first time reads the Books property - the returned list is filled up with related IBook entities.
Note on entity list properties. If there are two or more foreign keys on the target entity (like IBook) pointing back to parent - then you should use
OneToMany attribute and explicitly specify which back-reference property to use.
Next, the
IBook entity. We define two enum types that we use as types for new properties:
[Flags]
public enum BookEdition {
Paperback = 0x01,
Hardcover = 0x02,
EBook = 0x04,
}
public enum BookCategory {
Programming,
Fiction,
Kids,
}
[Entity, OrderBy("PublishedOn:DESC"), Paged]
public interface IBook {
[PrimaryKey, Auto]
Guid Id { get; }
[Size(50)]
string Title { get; set; }
DateTime PublishedOn { get; set; }
[Size(250), Nullable]
string Description { get; set; }
[Memo, Nullable]
string Abstract { get; set; }
BookCategory Category { get; set; }
BookEdition Editions { get; set; }
double Price { get; set; }
IPublisher Publisher { get; set; }
[ManyToMany(typeof(IBookAuthor))]
IList<IAuthor> Authors { get; }
}
New things in this entity definition:
- Enum-typed properties - VITA supports these just like other basic .NET types.
- We had seen already the OrderBy attribute; here the property name is appended with ':DESC' to indicate that the default sort order for books is by descending publishing date.
- Paged attribute instructs the framework to execute paging in the database. With this attribute or not, any query can use 'take' and 'skip' paging parameters. But for entities without Paged attribute the paging will be performed outside the database. This might be reasonable if the target table contains limited number of rows, so it is simpler to get all and then cut the page in .NET code, rather than running more complex query with paging.
- Size attribute - we use it to specify the size of a string (nvarchar) column in the database. Without this attribute the framework assumes the default size specified in the EntityModelSetup class (50) - you can change this default when you start up your application.
- Nullable attribute specifies that the value for the property is optional. It has two effects: first, it specifies that the corresponding column in the database allows NULL values. Secondly, the validation routine for the value (run before saving the data) checks each property value; with Nullable attribute it would allow a null or empty value to be submitted to the database. Without it, the value is required and if not set, the SaveChanges() call will be aborted and the database operation is not even started.
- Memo attribute identifies a property as an "unlimited string" type. In the database, the corresponding column would have a Memo data type (nvarchar(Max) for MS SQL Server).
- Authors property is decorated with ManyToMany attribute. We now have multiple authors for a book. The Authors property obviously returns the authors which is a list of IAuthor entities, linked to IBook through many-to-many relationship implemented through IBookAuthor 'linking' entity:
[Entity, Paged, OrderBy("LastName,FirstName")]
public interface IAuthor {
[PrimaryKey, Auto]
Guid Id { get; }
string FirstName { get; set; }
string LastName { get; set; }
[ManyToMany(typeof(IBookAuthor))]
IList<IBook> Books { get; }
[Computed(typeof(BookExtensions), "GetFullName"),
DependsOn("FirstName,LastName")]
string FullName { get; }
}
[Entity, PrimaryKey("Book,Author")]
public interface IBookAuthor {
IBook Book { get; set; }
IAuthor Author { get; set; }
}
- The OrderBy attribute has two field names separated by comma to specify multiple columns for default sorting order of IAuthor entities.
- The PrimaryKey attribute on IBookAuthor entity is a bit different from what we had seen before. First, it is placed at entity level, not property. Second, it has a parameter that lists two property names. This is the way to specify composite primary key for entities that have them. Note that actual key in the database will contain the ID columns - foreign keys referencing the Book and Author tables - the framework will make the substitute automatically.
- Computed FullName property - see section below for a detailed discussion of this facility.
- Another many-to-many list property Books on IAuthor entity, also decorated with ManyToMany attribute.
Entity lists: one-to-many and many-to-many relations
Both one-to-many and many-to-many relations in the database are expressed as entity references and entity-list typed properties in the entity model. Notice the difference in the definition.
The one-to-many
IPublisher.Books property did not use any extra attributes . For one-to-many, the system is able to deduce automatically all information for property implementation. For many-to-many relation, there is an extra entity/table involved - linking entity (
IBookAuthor). We have to specify it explicitly using
ManyToMany attribute.
There is another difference between these two relations - in the way we add/remove links at runtime. For one-to-many, we simply set the reference property on child entity to target parent; for many-to-many, we add the child to the list in the property; the system will add the link entity automatically:
book.Publisher = pub; //one-to-many
book.Authors.Add(johnSharp); //many-to-many - creates link entity automatically
Important: For entity list properties, always use IList<> -based type (based on interface), not List<> generic type.
Another important thing about entity lists: all entity lists in VITA implement
INotifyCollectionChanged interface, which is used in WPF binding engine, so the lists are readily bindable. This includes list-type properties on entities, and lists returned by
IEntitySession.GetEntities() method.
Explicitly ordered lists
Sometimes child entity lists are not ordered using some property, but the order is specified explicitly by the application code. Usually the solution is to define an additional property on child entity like
LineNumber, and use it to save the order. VITA defines an attribute
[PersistOrderIn(propertyName)] - just add it to the list property, and VITA will take care of assigning proper numbers whenever your code rearranges entities in the list.
Computed properties
It is a very common situation for an entity to have a property with the value that is derived from other properties of the entity. For example, we may associate a
FullName property with a person, and its value is a combination of the first and last names. While you can define a helper method
GetFullName() to compute full name, it is often convenient and natural to have a property
FullName on the entity itself. For some UI binding implementations it might be a requirement to have a property, not a method as a binding target.
VITA provides an easy way to define a computed property on an entity. The
Computed attribute identifies a property as computed and specifies the method that performs the computation.
The method
GetFullName is static and is defined in
BookExtensions class:
public static string GetFullName(IAuthor author) {
return author.FirstName + " " + author.LastName;
}
The method used for computed property must be a function returning a value, with single parameter matching the type of the entity. We can now use this property as any other "real" property:
Console.WriteLine(" Author: " + author.FullName);
The optional attribute
DependsOn on
FullName property lists the properties that this property depends on. Entity instances at runtime are objects implementing the standard .NET
INotifyPropertyChanged interface. Each time you modify a property, the entity fires the property-changed event. For complex binding scenarios, it is convenient to have the object fire property-changed for computed property whenever any of its 'parts' change - this behavior is provided using the
DependsOn attribute. Each time you change
FirstName or
LastName, the entity raises 2 PropertyChanged events: one for the property you assign like
FirstName, and the other one for the
FullName.
Extension methods
For complex entity models, it is convenient to define various extension methods for creating new entities, or for some other complex operations on entities. This way the code for a new entity collapses into a single line. Let's define a static extension class for entities in our model - we will use these methods in our sample code:
public static class BookExtensions {
public static IPublisher NewPublisher(this IEntitySession session, string name) {
var pub = session.NewEntity<IPublisher>();
pub.Name = name;
return pub;
}
public static IAuthor NewAuthor(this IEntitySession session, string firstName,
string lastName) {
var auth = session.NewEntity<IAuthor>();
auth.FirstName = firstName;
auth.LastName = lastName;
return auth;
}
public static IBook NewBook(this IEntitySession session, BookEdition editions,
BookCategory category, string title, string description, IPublisher publisher,
DateTime publishedOn, double price) {
var book = session.NewEntity<IBook>();
book.Editions = editions;
book.Category = category;
book.Title = title;
book.Description = description;
book.Publisher = publisher;
book.PublishedOn = publishedOn;
book.Price = price;
return book;
}
}
We now can use these methods to create entities in one line:
var john = session.NewAuthor("John", "Sharp");
Opening an entity store and creating sample entities
The definition of entity model is very similar to the code in Part 1, except we now register more entities in the entity module constructor:
public class MainBooksModule : EntityModule {
public MainBooksModule(EntityArea area) : base(area, "Main") {
RegisterEntities(typeof(IBook), typeof(IPublisher), typeof(IAuthor), typeof(IBookAuthor));
}
}
The startup code to open an entity store is the same. Let's create a few sample entities using the extension methods:
var session = entityStore.OpenSession();
var msPub = session.NewPublisher("MS Publishing");
var john = session.NewAuthor("John", "Sharp");
var jack = session.NewAuthor("Jack", "Pound");
var csBook = session.NewBook(BookEdition.Hardcover, BookCategory.Programming,
"c# Programming", "Expert c# programming", msPub, DateTime.Today.AddYears(-1), 25);
csBook.Authors.Add(john);
var vbBook = session.NewBook(BookEdition.EBook | BookEdition.Paperback,
BookCategory.Programming, "VB Programming", "Expert VB programming", msPub,
DateTime.Today, 15);
vbBook.Authors.Add(jack);
vbBook.Authors.Add(john);
session.SaveChanges(); //Submit to database
//let's remember some IDs, we'll use them later
var msPubId = msPub.Id;
var vbBookId = vbBook.Id;
Notice how we link books and authors - we simply add an author to the
book.Authors list property - the framework automatically creates the link entity
IBookAuthor. Could not be easier!
Let's see now how the relations work. First let's load a publisher and print out all its books using the
publisher.Books property:
session = entityStore.OpenSession();
var pub = session.GetEntity<IPublisher>(msPubId);
Console.WriteLine(" Loaded publisher: " + pub.Name);
foreach(var bk in pub.Books)
Console.WriteLine(" Book: " + bk.Title);
The above code prints 2 books from MS publisher. Next, let's try the many-to-many relation. The following code loads a book and prints all its authors:
var book = session.GetEntity<IBook>(vbBookId);
Console.WriteLine(" Loaded a book: " + book.Title);
foreach(var author in book.Authors)
Console.WriteLine(" Author: " + author.FullName);
The code prints a book title and its two authors. Just like in the previous case for
publisher.Books list, the
book.Authors list is loaded "on-demand", in lazy fashion - when we read the property, the system runs a query loading all book authors. Notice that we used a computed property
FullName to print a full name of the author.
Paging entity lists
In this section I would like to show how to perform paging of the query results. In the real-world applications, most of the tables in the database contain a huge number of rows. It is not possible to load the entire table into memory for processing - so we need some paging mechanism, to load entities page-by-page. VITA framework supports a simple and convenient paging mechanism. All you need to do is specify two optional parameters
skip and
take when you call the
session.GetEntities<T>() method.
Let's look at the example:
var allBooks = session.GetEntities<IBook>();
// Books are ordered by Published date, desc.
// CS book is published a year ago, VB book is published today.
// If we query with skip=1, we should get CS book only.
var pagedBooks = session.GetEntities<IBook>(skip: 1, take: 1);
var title = pagedBooks[0].Title;
Console.WriteLine(" Loaded book: " + title);
// the code should print the title "c# Programming"
There are in fact two paging mechanisms supported by VITA.
If you specify the
Paged attribute on the entity definition (as we did for the
IBook entity), then paging is performed in the database. VITA generates a SELECT stored procedure with paging parameters - have a look at the 'BookSelectAllPaged' stored procedure to see how it is implemented.
Some tables are expected to be small and contain a limited number of rows. For example, we may expect a limited number of publishers in our model. For these entities running a complex paging query does not bring any benefits. So we did not put the
Paged attribute on the
IPublisher entity. As a result, queries against this entity/table do not use paged SQL. You can still use
skip and
take parameters in the
GetEntities<>() call, but cutting off a page will be performed in c# code, after retrieving all entities.
Conclusion
The following image shows the sample code output:
Tutorial Home