Models

In this section we will complete our model design by adding additional classes that have a close relationship with the queries and commands we want to implement. But first, let's take a closer look at the table/class layout from the previously generated Database.cs.

1
2
3
4
5
6
7
8
9
10
11
12
[Table("dbo", "Customer")]
public class Customer	
{
    [Id, Key("PK_Customer", 1)]
    public int Id { get; set; }
    [Ref("PK_CustomerAddress", 1, "FK_Customer_CustomerAddress")]
    public int? AddressId { get; set; }
    public DateTime CreatedDate { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public string Name { get; set; }
}

For the database model, each class has the usual annotations that describe different types of metadata for the are really what defines object-relational mappings of types and properties property to tables, columns, identities and primary/foreign keys.

These classes are an important part of our model design and are typically used in two distinct scenarios. The first is what you would expect from most ORMs, which is basic CRUD behavior for mapping complete rows to and from your database.

The advantage of this approach is that most database operations can be expressed with general-purpose SQL that is easy to write and maintain. On the other hand, this simplistic approach restricts us to work with our database in a row-centric fashion, which often gives a too general view of our data and can result in unnecessary data going through the wire.

Wanting to keep our data layer concise and optimal, we remedy this by designing a domain model that represents our data sets more explicitly and is able to contain all data we want to send or retrieve from the database.

The usual approach when designing the domain model is to separate classes that represent input values (i.e. parameters) and output results (i.e. mapping) in different areas of your project, i.e. the Models and Views folder we created earlier.

Views

Views have a simple definition and are designed for the purpose of representing a minimal row set for the data received from a specific query. Therefore when designing a view the first thing you should consider is the exact data set you want your query to return.

Take for example a view for showing a list of customers containing only their name, email and number of orders.

1
2
3
4
5
6
public class CustomerStatsView
{
    public string CustomerName { get; set; }
    public string CustomerEmail { get; set; }
    public decimal NumberOfOrders { get; set; }
}

Using an exact type for each query ensures that we only pass data through the wire that is actually used by the caller of our query, and we can even design views for complex queries that map multiple subviews with any type of data relationship. This is possible by adding keys and references that describe these relationships, as with our combined one-to-one (addresses) and one-to-many (order items) relationships below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public class OrderReceiptView
{
    [Key] // pk
    public int OrderId { get; set; }
    public string CustomerName { get; set; }
    public string CustomerEmail { get; set; }
    public AddressView CustomerAddress { get; set; }
    public AddressView BillingAddress { get; set; }
    public IList<OrderItemView> Items { get; set; }

    public class AddressView
    {
        [Key] // pk
        public int Id { get; set; }
        public string Street { get; set; }
        public string Country { get; set; }
    }

    public class OrderItemView
    {
        [Ref] // fk
        public int OrderId { get; set; }
        public string Product { get; set; }
        public decimal Total { get; set; }
    }
}

If you are using a CRUD-based approach you can design a similar composite which will automatically use the keys and references defined on our database model.

1
2
3
4
5
6
7
8
9
10
11
12
public class OrderReceiptCrudView : Order
{
    public Customer Customer { get; set; }
    public CustomerAddress CustomerAddress { get; set; }
    public CustomerAddress BillingAddress { get; set; }
    public IList<OrderLineView> Lines { get; set; }
    
    public class OrderLineView : OrderLine
    {
        public Product Product { get; set; }
    }
}

Having learned the basics of views for representing the data from the database, we can move on to parameters which help us pass data to the database.

Parameters

While views are abstract in nature representing the shape of our query results, parameter classes are as concrete instances for the purpose of sending named parameters along with your SQL.

For queries their usage is to filter the rows of data you are reading.

1
2
3
4
5
public class CustomerFilter
{
    public DateTime MaxAge { get; set; }
    public DateTime MinAge { get; set; }
}
1
2
3
4
5
6
7
8
@result CustomerStatsView
@model CustomerFilter
@project Customer c

SELECT
    -- snipped --
WHERE
    @c.Col(m => m.Created) BETWEEN @M.Par(m => m.MinAge) AND @M.Par(m => m.MaxAge)

And for commands they represent the new data to be persisted in the database.

1
2
3
4
5
public class ProductChangePriceModel
{
    public int ProductId { get; set; }
    public decimal NewPrice { get; set; }
}
1
2
3
4
5
6
7
8
9
@model ProductChangePriceModel
@project Product p

UPDATE
    @p.TblName()
SET
    @p.ColName(m => m.Price) = @M.Par(m => m.NewPrice)
WHERE
    @p.ColName(m => m.Id) = @M.Par(m => m.ProductId)

Similar to views, they can also be designed for multiple dimensions of data to support batch SQL generation.

1
2
3
4
5
6
7
8
9
10
11
12
public class NewCustomerAndOrder
{
    public string CustomerEmail { get; set; }
    public string CustomerName { get; set; }
    public IList<OrderItem> Items { get; set; }
    
    public class OrderItem
    {
        public int ProductId { get; set; }
        public int Quantity { get; set; }
    }
}