CYCLE_STORE Database Schema and Entity Framework Model

The application’s persistence layer is built on a SQL Server database named CYCLE_STORE. All product data lives under the Production schema in three related tables: Product, ProductCategory, and ProductSubcategory. Entity Framework 5 (Database-First, EDMX) maps these tables to C# entity classes that are auto-generated by T4 templates and consumed throughout the AdventureWorks.Business namespace.

Database Overview

The CYCLE_STORE database organises its product catalogue into a three-level hierarchy. Categories group broad families of products (e.g., Bikes, Accessories). Each category owns one or more subcategories (e.g., Mountain Bikes, Road Bikes). Individual products belong to a subcategory via a nullable foreign key, allowing products that have not yet been fully classified to exist without breaking referential integrity.

ProductCategory

Top-level grouping. Each row has a unique name and owns a collection of subcategories.

ProductSubcategory

Mid-level grouping. Each row belongs to exactly one category and owns a collection of products.

Product

Leaf-level item. Contains all pricing, physical, and lifecycle attributes for a single SKU.

Production.Product

SQL Schema

CREATE TABLE [Production].[Product](
    [ProductID]              [int]              NOT NULL,
    [Name]                   [nvarchar](50)     NOT NULL,
    [ProductNumber]          [nvarchar](25)     NOT NULL,
    [MakeFlag]               [bit]              NOT NULL,
    [FinishedGoodsFlag]      [bit]              NOT NULL,
    [Color]                  [nvarchar](15)     NULL,
    [SafetyStockLevel]       [smallint]         NOT NULL,
    [ReorderPoint]           [smallint]         NOT NULL,
    [StandardCost]           [money]            NOT NULL,
    [ListPrice]              [money]            NOT NULL,
    [Size]                   [nvarchar](5)      NULL,
    [SizeUnitMeasureCode]    [nchar](3)         NULL,
    [WeightUnitMeasureCode]  [nchar](3)         NULL,
    [Weight]                 [decimal](8, 2)    NULL,
    [DaysToManufacture]      [int]              NOT NULL,
    [ProductLine]            [nchar](2)         NULL,
    [Class]                  [nchar](2)         NULL,
    [Style]                  [nchar](2)         NULL,
    [ProductSubcategoryID]   [int]              NULL,
    [ProductModelID]         [int]              NULL,
    [SellStartDate]          [datetime]         NOT NULL,
    [SellEndDate]            [datetime]         NULL,
    [DiscontinuedDate]       [datetime]         NULL,
    [rowguid]                [uniqueidentifier] NOT NULL,
    [ModifiedDate]           [datetime]         NOT NULL,
    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([ProductID] ASC)
)

C# Entity Class

namespace AdventureWorks.Business
{
    public partial class Product
    {
        public int ProductID { get; set; }
        public string Name { get; set; }
        public string ProductNumber { get; set; }
        public bool MakeFlag { get; set; }
        public bool FinishedGoodsFlag { get; set; }
        public string Color { get; set; }
        public short SafetyStockLevel { get; set; }
        public short ReorderPoint { get; set; }
        public decimal StandardCost { get; set; }
        public decimal ListPrice { get; set; }
        public string Size { get; set; }
        public string SizeUnitMeasureCode { get; set; }
        public string WeightUnitMeasureCode { get; set; }
        public Nullable<decimal> Weight { get; set; }
        public int DaysToManufacture { get; set; }
        public string ProductLine { get; set; }
        public string Class { get; set; }
        public string Style { get; set; }
        public Nullable<int> ProductSubcategoryID { get; set; }
        public Nullable<int> ProductModelID { get; set; }
        public System.DateTime SellStartDate { get; set; }
        public Nullable<System.DateTime> SellEndDate { get; set; }
        public Nullable<System.DateTime> DiscontinuedDate { get; set; }
        public System.Guid rowguid { get; set; }
        public System.DateTime ModifiedDate { get; set; }
    }
}

Key Field Reference

Field	Type	Notes
`ProductID`	`int`	Primary key — uniquely identifies each SKU.
`ProductSubcategoryID`	`int?`	Nullable FK to `ProductSubcategory`. A null value means the product has not been assigned to a subcategory.
`MakeFlag`	`bool`	`true` = manufactured in-house; `false` = purchased from a vendor.
`FinishedGoodsFlag`	`bool`	`true` = sellable finished good; `false` = component or sub-assembly not sold directly.
`ListPrice`	`decimal`	The customer-facing price. A value of `0.00` indicates the product is not sold individually.
`StandardCost`	`decimal`	Internal manufacturing or procurement cost used for margin calculations.
`Color`	`string`	Nullable. Used by `ProductManager.GetProductColor` to build filter lists.
`Size`	`string`	Nullable. Stores values like `"S"`, `"M"`, `"L"`, `"58"` (frame size in cm).
`Weight`	`decimal?`	Nullable. Stored in the unit identified by `WeightUnitMeasureCode` (e.g., `"LB"` or `"G"`).
`SellStartDate`	`DateTime`	The date the product became available for sale — always populated.
`SellEndDate`	`DateTime?`	Nullable. Populated when a product is retired from active sale.
`DiscontinuedDate`	`DateTime?`	Nullable. Marks permanent discontinuation, distinct from end-of-sale.

Production.ProductCategory

SQL Schema

CREATE TABLE [Production].[ProductCategory](
    [ProductCategoryID] [int]              NOT NULL,
    [Name]              [nvarchar](50)     NOT NULL,
    [rowguid]           [uniqueidentifier] NOT NULL,
    [ModifiedDate]      [datetime]         NOT NULL,
    CONSTRAINT [PK_ProductCategory] PRIMARY KEY CLUSTERED ([ProductCategoryID] ASC)
)

C# Entity Class

namespace AdventureWorks.Business
{
    public partial class ProductCategory
    {
        public ProductCategory()
        {
            this.ProductSubcategories = new HashSet<ProductSubcategory>();
        }

        public int ProductCategoryID { get; set; }
        public string Name { get; set; }
        public System.Guid rowguid { get; set; }
        public System.DateTime ModifiedDate { get; set; }

        public virtual ICollection<ProductSubcategory> ProductSubcategories { get; set; }
    }
}

The constructor initialises ProductSubcategories to an empty HashSet<ProductSubcategory> so that navigation-property access never returns null, even before EF lazy-loads the collection. The virtual modifier enables EF’s proxy-based lazy loading.

Production.ProductSubcategory

SQL Schema

CREATE TABLE [Production].[ProductSubcategory](
    [ProductSubcategoryID] [int]              NOT NULL,
    [ProductCategoryID]    [int]              NOT NULL,
    [Name]                 [nvarchar](50)     NOT NULL,
    [rowguid]              [uniqueidentifier] NOT NULL,
    [ModifiedDate]         [datetime]         NOT NULL,
    CONSTRAINT [PK_ProductSubcategory] PRIMARY KEY CLUSTERED ([ProductSubcategoryID] ASC)
)

C# Entity Class

namespace AdventureWorks.Business
{
    public partial class ProductSubcategory
    {
        public int ProductSubcategoryID { get; set; }
        public int ProductCategoryID { get; set; }
        public string Name { get; set; }
        public System.Guid rowguid { get; set; }
        public System.DateTime ModifiedDate { get; set; }

        public virtual ProductCategory ProductCategory { get; set; }
    }
}

ProductCategoryID is the non-nullable FK column. The virtual ProductCategory navigation property allows EF to lazy-load the parent category from a subcategory instance without an explicit join in calling code.

Entity Relationships

The three entities form a strict parent-child-grandchild hierarchy:

ProductCategory  (1)
    └── ProductSubcategory  (many)
            └── Product  (many, nullable FK)

ProductCategory → ProductSubcategory: One-to-many. A category has many subcategories; each subcategory belongs to exactly one category (ProductCategoryID NOT NULL).
ProductSubcategory → Product: One-to-many. A subcategory has many products; a product optionally belongs to one subcategory (ProductSubcategoryID NULL).

EF resolves both relationships through navigation properties — ProductCategory.ProductSubcategories for the first link, and the ProductSubcategory navigation on Product (materialised by the EDMX, accessed in LINQ joins within ProductManager).

CYCLE_STOREEntities DbContext

namespace AdventureWorks.Business
{
    public partial class CYCLE_STOREEntities : DbContext
    {
        public CYCLE_STOREEntities()
            : base("name=CYCLE_STOREEntities")
        { }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public DbSet<Product> Products { get; set; }
        public DbSet<ProductCategory> ProductCategories { get; set; }
        public DbSet<ProductSubcategory> ProductSubcategories { get; set; }
    }
}

The three DbSet properties are the primary entry points for LINQ queries across the application:

DbSet	Maps To
`Products`	`Production.Product`
`ProductCategories`	`Production.ProductCategory`
`ProductSubcategories`	`Production.ProductSubcategory`

The connection string name "name=CYCLE_STOREEntities" is resolved from the <connectionStrings> section of Web.config at runtime.

Database-First with EDMX

CycleModel.edmx is a Database-First Entity Framework model. It was generated by pointing Visual Studio’s ADO.NET Entity Data Model Wizard at the live CYCLE_STORE SQL Server instance, which introspected the schema and produced the XML model file. The EDMX contains three sections — the Storage Model (SSDL, mirrors the SQL schema), the Conceptual Model (CSDL, the C# entities), and the Mappings (MSL, how columns map to properties). The T4 template CycleModel.tt reads the CSDL section and emits a .cs file for each entity. CycleModel.Context.tt reads the same EDMX and emits CycleModel.Context.cs containing CYCLE_STOREEntities. The OnModelCreating override deliberately throws UnintentionalCodeFirstException. This is EF’s safeguard: if something causes EF to treat the context as a Code-First context (e.g., an incorrect constructor call), the exception fires immediately rather than silently creating a wrong schema. In normal Database-First operation, OnModelCreating is never called because the model is loaded from the EDMX’s embedded metadata, not derived from the entity classes.

Product.cs, ProductCategory.cs, and ProductSubcategory.cs are auto-generated output from the T4 templates. Any manual edits made directly to these files will be silently overwritten the next time the EDMX is saved or the template is re-run. To add custom behaviour to an entity, create a separate partial class file in the same namespace — the partial keyword on each generated class exists precisely for this purpose.

Overview

Getting Started

Application Deep Dive

Modernization Path

Reference

CYCLE_STORE Database Schema and Entity Framework Model

Database Overview

ProductCategory

ProductSubcategory

Product

Production.Product

SQL Schema

C# Entity Class

Key Field Reference

Production.ProductCategory

SQL Schema

C# Entity Class

Production.ProductSubcategory

SQL Schema

C# Entity Class

Entity Relationships

CYCLE_STOREEntities DbContext

Database-First with EDMX

Build docs developers (and LLMs) love

Overview

Getting Started

Application Deep Dive

Modernization Path

Reference

Documentation Index

​Database Overview

ProductCategory

ProductSubcategory

Product

​Production.Product

​SQL Schema

​C# Entity Class

​Key Field Reference

​Production.ProductCategory

​SQL Schema

​C# Entity Class

​Production.ProductSubcategory

​SQL Schema

​C# Entity Class

​Entity Relationships

​CYCLE_STOREEntities DbContext

​Database-First with EDMX

Build docs developers (and LLMs) love

Database Overview

Production.Product

SQL Schema

C# Entity Class

Key Field Reference

Production.ProductCategory

SQL Schema

C# Entity Class

Production.ProductSubcategory

SQL Schema

C# Entity Class

Entity Relationships

CYCLE_STOREEntities DbContext

Database-First with EDMX