Orchard Custom Data Access Template

Assuming the module = Module.Name

Notes:

1. Don't use a SQL reserved word for any of the columns names, e.g. 'Order'.

Yes, you need the 'Record' suffix.

namespace Module.Name.Models {
    public class CustomerRecord {
        public virtual int Id { get; set; }
 
        public virtual ICollection<OrderRecord> Orders { get; set; }
 
        public virtual string Name        { get; set; }
        public virtual string Description { get; set; }
    }
 
    public class OrderRecord {
        public virtual int Id { get; set; }
 
        [JsonIgnore]  // Required if returning this from a Web API call.
        public virtual CustomerRecord Customer { get; set; }
 
        public virtual ICollection<OrderItemRecord> OrderItems { get; set; }
 
        public virtual DateTime PlacedDate { get; set; }
        public virtual DateTime SentDate   { get; set; }
        public virtual string   Notes      { get; set; }
    }
 
    public class OrderItemRecord {
        public virtual int Id { get; set; }
 
        [JsonIgnore]  // Required if returning this from a Web API call.
        public virtual OrderRecord OrderRecord { get; set; }
 
        public virtual string  Description  { get; set; }
        public virtual int     Quantity     { get; set; }
        public virtual decimal UnitCost     { get; set; }
    }
}

Notes:

• Yes, you have to use the '_Id' suffix.
• To determine the current migration number is, run SELECT * FROM Orchard_Framework_DataMigrationRecord.
• TODO: Why no foreign keys?

namespace Module.Name {
    public class DatabaseMigrations : DataMigrationImpl {
 
        public int Create() {
            SchemaBuilder.CreateTable(typeof(CustomerRecord).Name,
                table => table
                    .Column<int>("Id", column => column.PrimaryKey().Identity())
                    .Column<string>("Name")                                      // Will use nvarchar(255)
                    .Column<string>("Description", column => column.Unlimited()) // Will use nvarchar(max)
                );
 
            SchemaBuilder.CreateTable(typeof(OrderRecord).Name,
                table => table
                    .Column<int>("Id", column => column.PrimaryKey().Identity())
                    .Column<int>("CustomerRecord_Id")
                    .Column<DateTime>("PlacedDate")
                    .Column<DateTime>("SentDate")
                    .Column<string>("Notes", column => column.Unlimited())
                );
 
            SchemaBuilder.CreateTable(typeof(OrderItemRecord).Name,
                table => table
                    .Column<int>("Id", column => column.PrimaryKey().Identity())
                    .Column<int>("OrderRecord_Id")
                    .Column<string>("Description", column => column.Unlimited())
                    .Column<int>("Quantity")
                    .Column<decimal>("UnitCost")
                );
 
            return 1;
        }
    }
}