PaletteAPI is a legacy ASP.NET Web API 2 backend for a palette-oriented social application. It exposes a SQL Server data model through conventional CRUD controllers and a database-first Entity Framework 6 data-access project.
Project status: This is a legacy .NET Framework project preserved as a backend engineering and learning project. Its current authentication and data-exposure design is not suitable for production use.
The solution contains two projects:
PaletteAPI, an IIS-hosted ASP.NET Web API applicationPalette.DAL, an Entity Framework 6 database-first model and small data-access layer
Most controllers work directly with PaletteEntities. PaletteExampleController separately demonstrates the UsersDAL abstraction. The HTTP pipeline configures JSON-only responses, camel-case property names, a global exception filter, and an API-key message handler.
- conventional CRUD endpoints for ten Palette domain resources
- SQL Server persistence through an Entity Framework EDMX model
- JSON responses with camel-case property names
- reference-loop suppression for entity navigation properties
- an API-key lookup message handler and a custom authorization attribute prototype
- global exception-to-HTTP-response handling
- a separate DAL example for user operations
| Area | Technology |
|---|---|
| Language | C# |
| Runtime | .NET Framework 4.8 |
| Web framework | ASP.NET Web API 2.2 |
| Hosting model | System.Web application hosted by IIS or IIS Express |
| Data access | Entity Framework 6 database-first |
| Database | Microsoft SQL Server |
| Serialization | Newtonsoft.Json 12.0.2 |
| Routing | Conventional api/{controller}/{id} routing |
| Architecture | Web API controllers plus a separate DAL assembly |
The web project references Entity Framework 6.1.3, while the DAL project references Entity Framework 6.2.0.
The default resource controllers instantiate PaletteEntities and perform synchronous EF operations directly. PaletteExampleController calls UsersDAL, whose base class owns a separate PaletteEntities context. The solution therefore contains a partial DAL abstraction, not a uniform repository or service architecture.
flowchart LR
Client[HTTP client] --> IIS[IIS or IIS Express]
IIS --> Pipeline[Web API pipeline]
Pipeline --> Handler[APIKeyHandler]
Handler --> Controllers[Resource controllers]
Controllers --> Context[PaletteEntities]
Example[PaletteExampleController] --> UsersDAL
UsersDAL --> Context
Context --> EF[Entity Framework 6]
EF --> SQL[(SQL Server Palette database)]
Config[WebApiConfig] -. configures .-> Pipeline
sequenceDiagram
participant Client
participant APIKeyHandler
participant PostsController
participant PaletteEntities
participant SQLServer
Client->>APIKeyHandler: GET /api/Posts/{id}
APIKeyHandler->>PaletteEntities: Look up optional apiKey query value
PaletteEntities->>SQLServer: Query APIKeys
SQLServer-->>PaletteEntities: Matching key or no result
APIKeyHandler->>PostsController: Continue request
PostsController->>PaletteEntities: Posts.Find(id)
PaletteEntities->>SQLServer: Query post by primary key
SQLServer-->>PaletteEntities: Post row or no result
alt post exists
PostsController-->>Client: 200 OK with JSON
else post does not exist
PostsController-->>Client: 404 Not Found
end
The handler always continues the request. It sets the current principal only when a key matches; it does not reject missing or invalid keys.
The solution uses the conventional route api/{controller}/{id}. Each verified resource controller exposes the following operations:
| Method | Route | Purpose |
|---|---|---|
GET |
/api/{resource} |
Return all records as an IQueryable |
GET |
/api/{resource}/{id} |
Return one record by primary key |
POST |
/api/{resource} |
Create a record from the JSON entity payload |
PUT |
/api/{resource}/{id} |
Update the record when the route ID matches the payload ID |
DELETE |
/api/{resource}/{id} |
Delete and return the matching record |
Verified resource routes:
| Base route | Controller | Entity |
|---|---|---|
/api/APIKeys |
APIKeysController |
APIKeys |
/api/Comments |
CommentsController |
Comments |
/api/Favourites |
FavouritesController |
Favourites |
/api/Followings |
FollowingsController |
Followings |
/api/PostColors |
PostColorsController |
PostColors |
/api/PostTags |
PostTagsController |
PostTags |
/api/Posts |
PostsController |
Posts |
/api/Tags |
TagsController |
Tags |
/api/UserProfiles |
UserProfilesController |
UserProfile |
/api/Users |
UsersController |
Users |
PaletteExampleController exposes the same HTTP method pattern at /api/PaletteExample and /api/PaletteExample/{id}, but performs user operations through UsersDAL.
The controllers accept and return EF entities directly; separate request and response DTOs are not present.
| Model | Main fields |
|---|---|
Users |
UserID, Username, Password, RegisterDate |
UserProfile |
UserProfileID, UserID, names, email, about text, profile photo |
Posts |
PostID, UserID, Title, Contents, PublishedDate |
PostColors |
PostColorID, PostID, three colour values |
Tags |
TagID, Tag |
PostTags |
PostTagID, PostID, TagID |
Comments |
CommentID, CommenterID, PostID, text and date |
Favourites |
FavouriteID, PostID, UserID, date |
Followings |
FollowingsID, UserID, FollowingID, date |
APIKeys |
APIKeyID, UserID, APIRole, UserKey, creation date |
curl --request GET https://localhost:44391/api/PostsThe port is taken from the committed IIS Express project settings and may be reassigned by the local Visual Studio environment.
WebApiConfig registers APIKeyHandler globally. The handler:
- reads
apiKeyfrom the request query string - finds a matching
APIKeys.UserKey - creates a principal for the related user when a match exists
- continues the request in every case
APIAuthorizeAttribute can compare a configured role with the stored API role, but its only use in the repository is commented out in PaletteExampleController. Consequently, the current endpoints are not protected. Query-string credentials can also be recorded by proxies and server logs and should not be used for a production authentication design.
PaletteDataModel.edmx defines the SQL Server schema and generated PaletteEntities context. No database creation script or seed data is committed.
erDiagram
USERS ||--o{ APIKEYS : owns
USERS ||--o{ POSTS : publishes
USERS ||--o{ USER_PROFILE : has
USERS ||--o{ COMMENTS : writes
POSTS ||--o{ COMMENTS : receives
USERS ||--o{ FAVOURITES : creates
POSTS ||--o{ FAVOURITES : receives
USERS ||--o{ FOLLOWINGS : follows
USERS ||--o{ FOLLOWINGS : is_followed
POSTS ||--o{ POST_COLORS : contains
POSTS ||--o{ POST_TAGS : categorised_by
TAGS ||--o{ POST_TAGS : assigned_through
USERS {
int UserID PK
string Username
string Password
date RegisterDate
}
APIKEYS {
int APIKeyID PK
int UserID FK
guid UserKey
string APIRole
date KeyCreateDate
}
POSTS {
int PostID PK
int UserID FK
string Title
string Contents
date PublishedDate
}
USER_PROFILE {
int UserProfileID PK
int UserID FK
string Email
binary ProfilePhoto
}
COMMENTS {
int CommentID PK
int CommenterID FK
int PostID FK
string CommentText
date CommentedDate
}
FAVOURITES {
int FavouriteID PK
int PostID FK
int UserID FK
date FavouriteDate
}
FOLLOWINGS {
int FollowingsID PK
int UserID FK
int FollowingID FK
date FollowedDate
}
POST_COLORS {
int PostColorID PK
int PostID FK
int ColorHEX1
int ColorHEX2
int ColorHEX3
}
POST_TAGS {
int PostTagID PK
int PostID FK
int TagID FK
}
TAGS {
int TagID PK
string Tag
}
The code confirms that both repositories map the same Palette entities and relationships. PaletteCoreAPI was created later and expresses that model with ASP.NET Core and EF Core. Neither repository contains release or migration documentation proving how the two were deployed, so this comparison does not claim a production replacement.
| Area | PaletteAPI | PaletteCoreAPI |
|---|---|---|
| Initial repository code | September 2020 | May 2021 |
| Runtime | .NET Framework 4.8 | .NET Core 3.1 |
| Web stack | ASP.NET Web API 2 / System.Web | ASP.NET Core Web API |
| Hosting | IIS or IIS Express | ASP.NET Core generic host |
| Data access | EF6 database-first EDMX | EF Core fluent mapping |
| Controller operations | Synchronous | Asynchronous |
| Database context | PaletteEntities |
PaletteContext |
| OpenAPI UI | Not present | NSwag present |
| Authentication enforcement | Prototype exists but is not applied | Not implemented |
PaletteAPI/
├── PaletteAPI.sln
├── PaletteAPI/
│ ├── App_Start/ Routing, filters, handler, and JSON configuration
│ ├── Attributes/ Global exception filter
│ ├── Controllers/ Web API resource controllers
│ ├── Security/ API-key handler and authorization prototype
│ ├── Global.asax.cs System.Web application startup
│ └── Web.config Runtime and database configuration
└── Palette.DAL/
├── DAL/ BaseDAL, UsersDAL, and APIKeysDAL
├── PaletteDataModel.edmx
├── PaletteDataModel.Context.cs
└── generated entities
- Windows with .NET Framework 4.8
- Visual Studio 2019 or later with the ASP.NET and web development workload
- IIS Express or IIS
- NuGet package restore support
- SQL Server or SQL Server Express
- a Palette database matching
PaletteDataModel.edmx
Mono can compile the solution, but IIS-hosted runtime behaviour should be verified on Windows.
The named Entity Framework connection string is PaletteEntities. It appears in both:
PaletteAPI/Web.configPalette.DAL/App.Config
Replace the local SQL Server Express metadata connection with a connection for the development database. The repository uses integrated security and does not contain a SQL password, but environment-specific connection details remain committed. Do not add credentials to these tracked files.
From a Visual Studio Developer Command Prompt:
nuget restore PaletteAPI.sln -NonInteractive
msbuild PaletteAPI.sln /p:Configuration=DebugOpen PaletteAPI.sln in Visual Studio and run the PaletteAPI web project with IIS Express. A working SQL Server schema is required for data-backed requests.
The complete solution was restored and compiled successfully during the documentation audit with Mono 6.12 and MSBuild 16.10. The build produced one warning because Mono could not resolve the unused System.Web.Entity reference declared by the web project. IIS hosting and database operations were not run because the audit environment does not contain the Palette SQL Server database.
- the API-key handler does not reject missing or invalid keys
- the custom authorization attribute is not active on any endpoint
- API keys are accepted through a query parameter and may appear in logs
- the public CRUD surface includes API-key and user records
- user passwords are represented as normal entity strings and exposed through direct entity serialization
- the exception filter returns exception messages in the HTTP reason phrase
- synchronous controller operations and direct EF entity binding provide limited validation boundaries
- database connection details are stored in tracked configuration
- no automated tests, OpenAPI document, migrations, CI configuration, or deployment automation are present
- several package versions and the System.Web application model are legacy
Possible modernization steps include moving the API to a supported ASP.NET Core release, replacing the EDMX and direct entity exposure with explicit persistence and DTO boundaries, implementing standards-based authentication and authorization, hashing passwords through a proven identity system, moving secrets and connection details to secure configuration, adding OpenAPI documentation and automated tests, and introducing repeatable database migrations.
Serkan Seker
No open-source license has currently been specified.