Chapter 10
Object-Relational Metadata Mapping Design Patterns
Introduction
To organize the object-relational metadata mapping, the design patterns of this category can be divided into the following three main sections:
Metadata mapping: Tries to store information about the object in metadata.
Query object: An object is responsible for creating queries.
Repository: Tries to implement CRUD processes optimally by placing a layer between the data access layer and the rest of the system and providing it to the user.
Structure
In this chapter, we will discuss the following topics:
Object-relational metadata mapping design patterns
Metadata mapping
Query object
Repository
Objectives
In this chapter, you will learn how to store information about objects and make centralized database queries. Next, using the repository design pattern, you will learn how to separate the data source from the rest of the system by placing a layer between the data access layer and the rest of the program. These design patterns are suitable for enterprise applications with extensive and sometimes complex business logic and where it is important to separate business logic concerns from data access and storage concerns.
Object-relational metadata mapping design patterns
When we are producing software, we need to implement the mapping between tables and classes. The software production process will be a process that contains a significant amount of repetitive code, and this will increase production time. To avoid this, you can use the metadata mapping design pattern. In this case, it is possible to avoid writing duplicate codes and extracting relationships from metadata using code generators or techniques related to reflection.
When the necessary infrastructure for creating queries is provided using metadata mapping, queries can be created and presented using query objects. In this case, the programmer no longer needs to know SQL and how to make queries. Now, if all the necessary queries to be sent to the database are presented through the query object, then with the help of a repository, the database can be hidden from the rest of the program.
Metadata mapping
Name:
Metadata mapping
Classification:
Object-relational metadata mapping design patterns
Also known as:
---
Intent:
By using this design pattern, information about the object is stored in the form of metadata.
Motivation, Structure, Implementation, and Sample code:
Often, a series of repetitive codes have been written in the examples that have been reviewed so far. The codes related to the mapping of objects to tables and attributes to columns have been constantly repeated. The metadata mapping design pattern helps avoid writing duplicate codes by extracting and storing the metadata related to the object and storing it. For example, consider the following class:
public class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
}
Suppose this class is mapped to the people table in the database. The properties of this class are also mapped exactly to the columns of the people table. With this assumption, to be able to write a query to get the list of all people, the following T-SQL code should be written:
SELECT Id, FirstName, LastName, Age FROM people
Now if we assume that we also have the following class:
public class Car
{
public int Id { get; set; }
public string Name { get; set; }
}
If we assume that this class is also mapped to the cars table in the database, then we will have the following query to get the list of all cars:
SELECT Id, Name FROM cars
After preparing the queries and executing them in the database, when the results are returned, each column's data should be mapped into its corresponding property in the target object to be presented to the user.
What happens in the preceding codes is that some codes must be repeated constantly. Suppose we have the metadata (or, in other words, the required information about the classes) and the list of tables and know how to map the tables and classes while avoiding writing duplicate codes. We can use this metadata to create and execute various queries.
To prepare metadata, we first need to get table and class information. The most important information we need is which class is mapped to which table and which column is mapped to each class property. To do this, we define the DataMap class as follows:
public class DataMap
{
public Type DomainClass { get; set; }
public string TableName { get; set; }
public ICollection
public DataMap(Type domainClass, string tableName)
{
DomainClass = domainClass;
TableName = tableName;
ColumnMaps = new List
}
public string? GetKeyColumn()
=>ColumnMaps.FirstOrDefault(x=> x.IsKey)?.ColumnName;
public string GetColumns()
{
StringBuilder sb = new();
if (ColumnMaps.Any())
sb.Append(ColumnMaps.First().ColumnName);
foreach (var column in ColumnMaps.Skip(1))
{
sb.Append($",{column.ColumnName}");
}
return sb.ToString();
}
}
In the preceding class, the DomainClass property is used to store the class information, TableName is used to store the table name, and ColumnMaps is used to store the column mapping information. Also, in this class, the GetKeyColumn method returns the name of the column that is the primary key. Please note that if the primary key is composite, the code of this method must be changed. The GetColumns method also returns a string containing the names of the columns, and this string separates column names with commas.
Now, we need to check how to implement the ColumnMap class. This class maps class properties and table columns to each other:
public class ColumnMap
{
public string ColumnName { get; }
public string PropertyName { get; }
public bool IsKey { get; }
public PropertyInfo Property { get; private set; }
public DataMap DataMap { get; }
public ColumnMap(
string columnName, string propertyName,
DataMap dataMap,bool isKey = false)
{
DataMap = dataMap;
ColumnName = columnName;
FieldName = fieldName;
IsKey = isKey;
Property = DataMap.DomainClass.GetProperty(FieldName);
}
public void SetValue(object obj, object columnValue)
=> Property.SetValue(obj, columnValue);
public object GetValue(object obj) => Property.GetValue(obj);
}
In this class, the ColumnName property is used for the column name, PropertyName is used for the property name, IsKey is used to determine whether the column is the primary key or not, Property is used to store property information to receive and store the value, and DataMap is used to specify the related class. The SetValue method is used to put the returned value from the database into the property, and the GetValue method is used to read the property value and send it to the database.
So far, we have reviewed the codes to prepare and obtain metadata so far. In the following, we will see how these codes can be used. For example, suppose that we want to implement the search operation. The search operation can include creating and sending queries to the database, receiving values, and placing them in the desired object. For this, we check the following codes:
Public abstract class Mapper
{
protected DataMap DataMap { get; set; }
public object Find(TKey key)
{
string query = $"" +
$"SELECT {DataMap.GetColumns()}" +
$"FROM {DataMap.TableName}" +
$"WHERE {DataMap.GetKeyColumn()} = {key}";
var reader = new SqlCommand(query, DB.Connection).ExecuteReader();
reader.Read();
var result = Load(reader);
return result;
}
public object Load(IDataReader reader)
{
var obj = Activator.CreateInstance(DataMap.DomainClass);
LoadProperties(reader, obj);
return obj;
}
private void LoadProperties(IDataReader reader, object obj)
{
foreach (var item in DataMap.ColumnMaps)
{
item.SetValue(obj, reader[item.ColumnName]);
}
}
}
The preceding code defines the Mapper class as an abstract class. This class is responsible for creating queries based on metadata, sending them to the database, receiving the results, and mapping them to the class's properties according to the metadata. The DataMap property in this class specifies which metadata we will work with, and this property is later set by classes that inherit Mapper. The Find method in this class is designed to search for records in the table based on the primary key. In this method, according to the methods available in the DataMap class, the information of the table and columns is read, and a query is made. After creating the query, the query is sent to the database, and in return, the results are sent to the Load method.
The Load method first creates an object of the desired class and then uses the LoadProperties method to set the value of each of the properties of this object based on the columns returned from the database.
According to the preceding codes, the search operation can be performed based on the metadata of the classes. Finally, to perform the search in the Person class, we create the PersonMapper class. This class is responsible for setting Person metadata:
public class PersonMapper : Mapper
{
public PersonMapper() => LoadDataMap();
protected void LoadDataMap()
{
DataMap = new DataMap(typeof(Person), "people");
DataMap.ColumnMaps.Add(
new("personId", nameof(Person.PersonId), DataMap, true));
DataMap.ColumnMaps.Add(
new("firstName", nameof(Person.FirstName), DataMap));
DataMap.ColumnMaps.Add(
new("lastName", nameof(Person.LastName), DataMap));
DataMap.ColumnMaps.Add(
new("age", nameof(Person.Age), DataMap));
}
public Person Get(int personId) => (Person)Find(personId);
}
The LoadDataMap method is placed in the PersonMapper constructor in the preceding code. Since this information is immutable, this method can be executed only during the initial loading of the program. Finally, if we want to communicate with the cars table, it will only be enough to implement the CarMapper class like the PersonMapper class.
To get the metadata of the tables, it is enough to serialize the DataMap property to the desired format. The following is a part of the Person class metadata in JSON format:
{
"DomainClass": "Person",
"TableName": "people",
"ColumnMaps": [{
"ColumnName": "personId",
"PropertyName": "PersonId",
"IsKey": true
}, {
"ColumnName": "firstName",
"PropertyName": "FirstName",
"IsKey": false
}, {
"ColumnName": "lastName",
"PropertyName": "LastName",
"IsKey": false
}, {
"ColumnName": "age",
"PropertyName": "Age",
"IsKey": false
}]
}
Notes:
Metadata can be produced in two different ways at two different times:
The first method is using code generators. Using this method, metadata can be given as input to the code generator. The Mapper classes can be delivered and placed next to the rest of the source code. You can even prepare these codes in the build process before starting to compile. Since code generators create these codes and classes during the Build process, it is unnecessary to include them in the source code controller, such as Git and so on.
The second method is Reflection (as in the example provided). This method gives good flexibility to the codes, and on the other hand, it reduces the efficiency. This loss of efficiency should be checked against the advantage that is provided.
It is often possible to save the generated metadata in XML or JSON format. You can even save the metadata of the tables in the database. When the metadata is stored in an external data source, to use it, in the LoadDataMap method seen in PersonMapper, instead of providing the necessary settings, we just need to deliver the existing metadata to the DataMap.
Consequences: Advantages
Using this design pattern significantly reduces the work and codes required for database mapping.
Adding a new model or table is enough to write the relevant Mapper and present the desired logic for mapping (LoadDataMap) in its format.
Consequences: Disadvantages
Using this design pattern makes the code refactoring process difficult. Because after generating the metadata, if the class's property name changes or the table's column name changes, then the program will face a serious problem.
Applicability:
When faced with many models and we must map each to a table in the database, then using this design pattern can be useful.
Related patterns:
Some of the following design patterns are not related to metadata mapping design patterns, but to implement this design pattern, checking the following design patterns will be useful:
Query Object
Query object
Name:
Query object
Classification:
Object-relational metadata mapping design patterns
Also known as:
---
Intent:
By using this design pattern, an object takes the task of creating queries.
Motivation, Structure, Implementation, and Sample code:
Suppose we are implementing a software requirement. The team responsible for generating and implementing this requirement does not have information about how to write queries in T-SQL language. In this case, implementing the requirement will be a bit difficult. The members of this team may not be able to write good queries, which may seriously damage the program's overall efficiency.
In this situation, the way of generating queries can be entrusted to a class, and the users of this class, instead of being related to the table and its columns, face the classes and properties that they have produced in the form of models. As an example, consider the example of the person that we discussed in the metadata mapping design pattern section. The class related to the Person model can be considered as follows:
public class Person
{
public int PersonId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
}
Suppose we want to generate the following query using the query object design pattern:
SELECT PersonId, FirstName, LastName, Age FROM Person WHERE Age > 33
We learned how metadata can be created and used in the metadata mapping design pattern. Also, in that design pattern, we learned how to map the properties of a class to table columns. The remaining point is how to generate the condition section or WHERE clause. The condition created in the WHERE section is often in the Property Operator Value format (For example, Age > 33).
With these explanations, to generate the WHERE section, we define the Criteria class as follows:
public class Criteria
{
public string @Operator { get; set; }
public string Property { get; set; }
public object Value { get; set; }
public Criteria(string @operator, string property, object value)
{
Operator = @operator;
Property = property;
Value = value;
}
}
As shown in the preceding code, the @Operator property is considered for the operator (such as >, <, =, and so on), the Property is considered for the property name, and the Value property is for the value. According to the preceding sample query, this class should be able to make the condition related to Age > 33. For this purpose, we define the GreaterThan method as follows within the Criteria class:
public static Criteria GreaterThan(string property, int value)
=> new Criteria(">", property, value);
As it is clear in the preceding query, the GreaterThan method returns a Criteria object after receiving the property name and value. Next, we need to convert these created Criteria into an understandable query for the T-SQL language. For this purpose, we define the GenerateTSQL method as follows in the Criteria class:
public string GenerateTSQL(PersonMapper mapper)
{
var columnMap = mapper.DataMap.ColumnMaps
.FirstOrDefault(x => x.PropertyName == Property);
return $"{columnMap.ColumnName} {Operator} {Value}";
}
Upon receiving a PersonMapper type object, the preceding method reads the relevant metadata (this class has already been implemented in the metadata mapping design pattern) and returns the column related to the provided property and the condition statement from the metadata in T-SQL language.
The objects related to the condition section have been created, and the corresponding T-SQL code has been generated. We should be able to assign it to our query. Therefore, we define the QueryObject class as follows:
public class QueryObject
{
public ICollection
}
The QueryObject class has a property called Criteria, which stores all the conditions of a query. Since the number of conditions in the WHERE section can be more than one condition, this property is defined as an ICollection. Also, the QueryObject class has a method called GenerateWhereClause, which adds various conditions. The implementation of this method will be as follows:
public string GenerateWhereClause()
{
StringBuilder sb = new();
foreach (var item in Criterias)
{
if (sb.Length > 0)
{
sb.Append("AND");
}
sb.Append(item.GenerateTSQL(mapper));
}
return sb.ToString();
}
In the preceding code, for the case of simplicity, different conditions are linked to each other using the AND operator.
According to the preceding codes, the WHERE section is made. To use and run a query, you can define the search method in the Mapper class as follows:
public IDataReader FindByWhere(string where)
{
string query = $"" +
$"SELECT {DataMap.GetColumns()}" +
$"FROM {DataMap.TableName}" +
$"WHERE {where}";
return new SqlCommand(query, DB.Connection).ExecuteReader();
}
Next, using the Execute method in the QueryObject class, you can issue a query execution request:
public IDataReader Execute() => mapper.FindByWhere(GenerateWhereClause());
And also, we can use it as follows:
QueryObject qb = new QueryObject();
qb.Criterias.Add(Criteria.GreaterThan(nameof(Person.Age), 33));
var result = qb.Execute();
As you can see, the programming team can create and execute T-SQL queries using classes and their properties without needing T-SQL knowledge.
Notes:
This design pattern aligns with the interpreter design pattern from the GoF design patterns.
Instead of writing the QueryObject class with all the capabilities, it is recommended to create its capabilities based on the need over time.
The metadata mapping or UnitOfWork design pattern can be very beneficial in using this design pattern.
Today, Object-Relational Mappers (ORMs) do exactly what can be done with query objects, so if you use ORM in your program, you will not need to use this design pattern.
Using this design pattern, the database structure is encapsulated.
Consequences: Advantages
When the database structure is different from the structure of the models, using this design pattern can be useful.
Combining this design pattern with the identity map design pattern can increase efficiency. For example, suppose that the list of all people has already been read from the database, and the result is placed in the identity map. Now, a new query has been created to apply a condition on the list of people. The query can be answered without sending it to the database and only by referring to the data in the identity map.
Consequences: Disadvantages
Usually, producing queries that answer the needs of a program, with this design pattern, can be a time-consuming or complex task, and often using existing tools can be a better option.
Applicability:
When the database structure is different from the structure of the models, using this design pattern can be useful.
If the production team lacks the knowledge to write queries, this design pattern can be useful by producing an interpreter.
Related patterns:
Some of the following design patterns are not related to query object design patterns, but to implement this design pattern, checking the following design patterns will be useful:
Interpreter
Metadata mapping
UnitOfWork
Identity map
Repository
Name:
Repository
Classification:
Object-relational metadata mapping design patterns
Also known as:
---
Intent:
This design pattern tries to implement CRUD1 processes optimally and provide them to the user by placing a layer between the data access layer and the rest of the system.
Motivation, Structure, Implementation, and Sample code:
Suppose it is requested to provide a function through which a new user can be defined in the system, edit and delete existing users, find a specific user, and display the list of users. There are different ways to implement this scenario. One of these ways is to design a class to make appropriate queries. The problem with this type of design is that as new entities are added, it will be necessary to write separate classes for each one. Naturally, we will face many duplicate codes in this method.
Another method is to define the desired operation for working with data in the form of an interface, and each entity implements this interface. In this implementation, each entity defines and sets its appropriate queries. This approach is what the repository design pattern tries to provide. According to the preceding explanations, the following class diagram can be imagined:
Figure%2010.1.png
Figure 10.1: Repository design pattern UML diagram
As shown in Figure 10.1 diagram, the IRepository interface defines the necessary methods for working with data. The user entity in the UserRepository class implements this interface and creates appropriate queries for each operation. With these explanations, the following codes can be considered for the repository design pattern:
public class UserDbSet
{
public static List
{
new User() { Id = 1, Name = "Vahid" },
new User() { Id = 2, Name = "Ali" },
new User() { Id = 3, Name = "Reza" },
new User() { Id = 4, Name = "Maryam" },
new User() { Id = 5, Name = "Hassan" }
};
}
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public override string ToString() => $"Id: {Id}, Name: {Name}";
}
public interface IRepository
{
User Find(int id);
List
void Add(User user);
void Update(User user);
void Delete(int id);
}
public class UserRepository : IRepository
{
public void Add(User user)
{
if (UserDbSet.Users.All(x => x.Id != user.Id))
UserDbSet.Users.Add(user);
}
public void Delete(int id) => UserDbSet.Users.Remove(Find(id));
public User Find(int id)
{
UserDbSet.Users.ToDictionary(x => x.Id).TryGetValue(id, out User result);
return result;
}
public List
public void Update(User user)
{
var originalUser = Find(user.Id);
if (originalUser != null)
{
originalUser.Name = user.Name;
}
}
}
Now, to use this Repository, you can proceed as follows:
IRepository repository = new UserRepository();
repository.Add(new User { Id = 6, Name = "Narges" });
repository.Update(new User { Id = 3, Name = "Alireza" });
repository.Delete(4);
Console.WriteLine(repository.Find(1));
foreach (User user in repository.GetAll())
{
Console.WriteLine(user);
}
As can be seen, to work with data, the Client does not need to engage with queries and write queries and submits his request only through the Repository of the data source.
If we pay attention to the interface code of IRepository, we will notice that this Repository receives a User object in a method like Add. This method will create a problem: if we need to define a Repository for many entities, we will need to define a separate interface for each. To solve this problem, the repository design pattern can be implemented using the concept of Generics in the C#.NET programming language. For this purpose, the preceding code can be rewritten as follows:
public interface IRepository
{
TEntity Find(TKey id);
List
void Add(TEntity user);
void Update(TEntity user);
void Delete(TKey id);
}
Instead of being dependent on the user, the IRepository interface receives the required types at runtime. This interface has two generic types in the preceding code. TEntity is the entity we want to add or edit to the data source. TKey is also a data type of the entity key field, with the help of which we find or delete the entity from a data source. To have a better interface, you can also define a limit on the above Generic types to prevent possible runtime errors. According to the above interface, the UserRepository class will be rewritten as follows:
public class UserRepository : IRepository
{
// Class implementation…
}
With the preceding change applied to the IRepository definition, defining an interface for each entity is unnecessary.
The next point about this design pattern is that implementing the IRepository interface methods may be consistent across all entities. In any case, defining the Repository associated with each entity will be necessary. To prevent this from happening, the entire Repository implementation can be done using Generic. For this purpose, it will be necessary to define a general class that implements the IRepository interface and provides the implementation of the methods. Then any entity that needs a repository should use this class. If an entity needs its Repository or needs to make changes in the default implementation of the provided Repository, it can do so by using inheritance.
With these explanations, the Repository implementation can be changed as follows:
public class Repository
{
DbSet _set;
public Repository()
{
_set = DbContext.Set
}
//…
}
As seen in the preceding code, the Repository class is also defined as Generic, like the IRepository interface. In the implementation of this class, the important point is that with the help of the Set method provided by the Entity Framework, by providing the table type, you can access the corresponding object (_set object). When this object is available, it is easy to implement various data operations. This way of implementing the repository design pattern is called a generic repository. By implementing this pattern in the generic repository method, there will often be no need to define an interface or class for each entity, and the amount of code will be significantly reduced.
Notes:
Today, Entity Framework has implemented the Repository design pattern within itself. The question arises: Although this design pattern is implemented in the entity framework, do we need to re-implement it in our codes? The answer to this question can be very challenging. But it seems that for small systems, re-implementing the Repository design pattern may not be justified, but for larger systems, placing an additional layer between the client and entity framework can be very useful. Some of the advantages of this method are:
LINQ queries will enter the business code if the client and entity framework is directly connected, increasing the dependency between the two layers. While in the presence of a repository layer between these two layers, all queries will be placed inside this intermediate layer, and loose coupling between the business layer and the data access layer will happen.
When we need to write unit tests in the presence of the Repository, we can simply connect the tests to a mock repository and write and run the tests easily.
This design pattern is very similar to the query object design pattern, and combining query objects with metadata mapping produces the required queries.
In using the repository design pattern, it is not necessary to have relational databases.
Usually, this design pattern is used next to the UnitOfWork design pattern.
Consequences: Advantages
The codes related to the business layer are separated from the data access layer. As a result, if the data source needs to be changed, applying this change will be accompanied by minimal changes in the code.
Using this approach, codes can be tested more easily.
Repetitive queries will be reduced during the program.
Separation of Concern (SoC) is observed to separate the business logic from data access.
Consequences: Disadvantages
Considering that an additional abstraction layer is created, this design pattern for small programs can increase complexity.
Applicability:
This design pattern can be useful when there is a need to separate the business logic layer from the data access layer.
Using this design pattern can be very useful for large and complex programs.
Related patterns:
Some of the following design patterns are not related to the repository design pattern, but to implement this design pattern, checking the following design patterns will be useful:
Query object
Metadata mapping
UnitOfWork
Conclusion
In this chapter, you learned how to extract class metadata using different methods, such as reflection. You also learned how to focus the creation of T-SQL queries (Or any other database-related queries) with the help of query object design patterns. Also, in this chapter, you learned how to create a layer between the data access layer and the rest of the system and use it to communicate with the data source to perform various CRUD operations.
In the next chapter, you will learn about Web Presentation design patterns.
1 Create-Read-Update-Delete
Join our book's Discord space
Join the book's Discord Workspace for Latest updates, Offers, Tech happenings around the world, New Release and Sessions with the Authors:
https://discord.bpbonline.com