====== Orchard Custom Data Access Template ======

Assuming the module = Module.Name

Notes:

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

===== Create the models =====

Yes, you need the 'Record' suffix.

<code c#>
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; }
    }
}
</code>

===== Create the migration =====

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?

<code c#>
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;
        }
    }
}
</code>