CRUD Operations

In the Storable<T> class, CRUD (Create, Read, Update, Delete) operations are structured to simplify managing data. Each CRUD method includes two versions:

Standard Method: Performs the operation without additional error handling (e.g., user.Create()).
Error-Aware Method: Returns error details if the operation fails, enhancing traceability and debugging (e.g., user.CreateWithError()).

Simple operations

Creating Records

The Create operation is responsible for adding new records to the database, either as single entries or in batches. In Storable<T>, the Create method has both instance-based and static-based versions, along with error-aware variants.

Instance-Based Creation

To create a new record using an instance, you call the Create method on the specific instance. The standard method (Create) will attempt to create the record without returning error details, while CreateWithError will provide any encountered errors.

Standard Method:

var user = new User { Username = "john" };
bool success = user.Create();  // Returns true if creation succeeds.

If the operation is successful, it returns true. If it fails, it returns false without error details.

Error-Aware Method:
```
var user = new User { Username = "john" };
List<GenericError> errors = user.CreateWithError();
```
- If errors occur during creation, CreateWithError returns a list of GenericError objects detailing the issues. This allows you to handle these errors programmatically, making it easy to display user feedback or log issues.

Static-Based Creation

Using static-based creation methods, you can create one or multiple records directly without initializing individual instances. This approach is helpful when bulk-creating items. Both Create and CreateWithError versions are available:

Standard Method:
```
var users = new List<User> { new User { Username = "alice" }, new User { Username = "bob" } };
List<User> createdUsers = User.Create(users);
```
- The Create method returns a list of successfully created users. If the creation fails, it returns an empty list without additional error information.
Error-Aware Method:
```
var users = new List<User> { new User { Username = "alice" }, new User { Username = "bob" } };
ResultWithError<List<User>> result = User.CreateWithError(users);
```
- The CreateWithError method provides a ResultWithError object that contains both the list of created users (if successful) and a list of errors, if any occurred. This is especially useful when you need to identify specific items that failed during the batch creation process.

Updating Records

The Update operation modifies existing records in the database. Like creation, update operations in Storable<T> support instance-based and static-based methods, each with an error-aware variant.

Instance-Based Update

To update a specific record, you can use the Update method on an instance of the model.

Standard Method:

var user = User.GetById(1);
if (user != null)
{
    user.Username = "newUsername";
    bool success = user.Update();  // Returns true if the update succeeds.
}

If the update is successful, Update returns true; otherwise, it returns false with no error information.

Error-Aware Method:

var user = User.GetById(1);
if (user != null)
{
    user.Username = "newUsername";
    List<GenericError> errors = user.UpdateWithError();
}

The UpdateWithError method returns a list of GenericError objects if any issues occur, allowing you to handle these errors immediately.

Static-Based Update

You can update multiple records in a single operation using the static-based Update methods.

Standard Method:

var users = User.Where(u => u.IsActive);
List<User> updatedUsers = User.Update(users);  // Updates all active users.

Update will return the list of successfully updated records. If updates fail, an empty list is returned.

Error-Aware Method:
```
var users = User.Where(u => u.IsActive);
ResultWithError<List<User>> result = User.UpdateWithError(users);
```
- The UpdateWithError method returns a ResultWithError object, which includes a list of successfully updated records and any errors encountered, helping pinpoint the problematic items.

Deleting Records

The Delete operation removes records from the database. It is available both as an instance-based and a static-based method, with error-aware options.

Instance-Based Deletion

Standard Method:

var user = User.GetById(1);
if (user != null)
{
    bool success = user.Delete();  // Deletes the user if found, returns true on success.
}

If deletion is successful, Delete returns true; otherwise, it returns false without error details.

Error-Aware Method:
```
var user = User.GetById(1);
if (user != null)
{
    List<GenericError> errors = user.DeleteWithError();
}
```
- DeleteWithError provides a list of GenericError objects if the deletion fails, allowing error tracking.

Static-Based Deletion

Deleting multiple records can be done through the static-based Delete methods, which take a list of records or IDs.

Standard Method:

var usersToDelete = new List<User> { user1, user2 };
List<User> deletedUsers = User.Delete(usersToDelete);  // Deletes specified users.

Returns a list of successfully deleted records. If the deletion fails, it returns an empty list.

Error-Aware Method:
```
var usersToDelete = new List<User> { user1, user2 };
ResultWithError<List<User>> result = User.DeleteWithError(usersToDelete);
```
- Returns a ResultWithError object, including successfully deleted records and any errors, offering insight into potential issues.

Reading Data

Retrieving data is essential for querying records. The Storable<T> class provides several methods for this, including:

GetAll: Retrieves all records.
GetById: Fetches a record by its unique ID.
Where: Retrieves records based on conditions.
Exists: Checks if any records match a condition.

Retrieving All Records

Standard Method:
```
List<User> users = User.GetAll();
```
- GetAll fetches all records. If successful, it returns a list of records; if no records exist, it returns an empty list.
Error-Aware Method:
```
ResultWithError<List<User>> result = User.GetAllWithError();
```
- Returns a ResultWithError object that includes the list of all records, along with any error details if retrieval fails.

Retrieving a Record by ID

Standard Method:
```
User? user = User.GetById(1);
```
- Retrieves a record by ID, returning null if the record doesn’t exist.
Error-Aware Method:
```
ResultWithError<User> result = User.GetByIdWithError(1);
```
- Returns a ResultWithError object containing the record (if found) and any errors encountered during retrieval.

Retrieving Records with Conditions

The Where and Exists methods leverage LINQ, allowing expressive queries.

Where:
```
List<User> activeUsers = User.Where(u => u.IsActive);
```
- Filters records based on a lambda condition. If no matching records are found, an empty list is returned.
Error-Aware Where:
```
ResultWithError<List<User>> result = User.WhereWithError(u => u.IsActive);
```
- Returns a ResultWithError object, containing both the filtered records and any errors.
Exists:
```
bool exists = User.Exists(u => u.Username == "john");
```
- Checks if any records match a condition. Returns true if matches are found, otherwise false.
Error-Aware Exists:
```
ResultWithError<bool> result = User.ExistsWithError(u => u.Username == "john");
```
- Returns a ResultWithError object, allowing you to access errors if any occur.

Complex operations

In some cases, you may need to perform more complex operations. To facilitate this, you can use the following methods that allow you to create query builders:

Query Builder

The query builder allow you to create a SELECT query.

Run

The Run method executes the query and returns the results as a list of objects of type T.

Example:

var usersQuery = User.StartQuery().Where(u => u.IsActive);
List<User> activeUsers = usersQuery.Run();

This will retrieve all active users (where IsActive is true). The Run method returns a direct list of results.

RunWithError

The RunWithError method executes the query and returns the results with error handling, in the form of a ResultWithError object.

Example:

var usersQuery = User.StartQuery().Where(u => u.IsActive);
ResultWithError<List<User>> result = usersQuery.RunWithError();

If an error occurs during query execution (e.g., a database error), the ResultWithError object will contain information about the error.

Single

The Single method executes the query and returns a single result. If no result is found, it returns null.

Example:

var userQuery = User.StartQuery().Where(u => u.Username == "john");
User? user = userQuery.Single();

This returns a single user with the username "john", or null if no user is found.

SingleWithError

The SingleWithError method executes the query and returns a single result with error handling. If an error occurs, it will be included in the ResultWithError object.

Example:

var userQuery = User.StartQuery().Where(u => u.Username == "john");
ResultWithError<User> result = userQuery.SingleWithError();

This returns a single user or an error object containing details about the failure.

Where

The Where method adds a filter condition to the query.

Example:

var usersQuery = User.StartQuery().Where(u => u.IsActive && u.Age >= 18);
List<User> activeAdults = usersQuery.Run();

This filters users who are active and at least 18 years old.

WhereWithParameters

The WhereWithParameters method allows you to create filter conditions with external parameters. It is particularly useful for complex queries where you want to store a condition for reuse with different parameters.

Example:

int idUser = 123;
int idPerm = 456;
// query is stored inside the class
if(query == null)
    query = PermissionUser.StartQuery().WhereWithParameters(pu => pu.Permission.Id == idPerm && pu.UserId == idUser);

// create a new query
var newQuery = query.New();
// Set the query variables
newQuery.SetVariables((define) => {
  define("idUser", idUser);
  define("idPerm", idPerm);
});

// Execute the query
List<PermissionUser> permissionUsers = newQuery.Run();

This prepares a filter condition for the user and permission, with variables that can be dynamically adjusted.

New

The New method allows you to create a new query when you use WhereWithParameters. This method lock concurrency action and until a Run/RunWithError/Single/SingleWithError is called.

Example:

int idUser = 123;
int idPerm = 456;
// query is stored inside the class
if(query == null)
    query = PermissionUser.StartQuery().WhereWithParameters(pu => pu.Permission.Id == idPerm && pu.UserId == idUser);

// create a new query to prevent concurrency until the run method
var newQuery = query.New();
// Set the query variables
newQuery.SetVariables((define) => {
  define("idUser", idUser);
  define("idPerm", idPerm);
});

// Execute the query and release the mutex for the next query
List<PermissionUser> permissionUsers = newQuery.Run();

Prepare

The Prepare method allows you to prepare the query with parameters.

Example:

var userQuery = User.StartQuery().WhereWithParameters(u => u.Username == username);
List<User> users = userQuery.New().Prepare("John").Run();

Here, Prepare allows you to add all parameters to the query before executing it.

SetVariables

The SetVariables method lets you define specific variables for the query. These variables can be used in conditions or parameters within the query.

Example:

List<User> users = User
                      .StartQuery()
                      .WhereWithParameters(u => u.Username == username)
                      .New()
                      .SetVariables((define) => {
                        define("username", "John");
                      })
                      .Run();

Field

The Field method allows you to specify one or more fields to include in the query results.

Example:

var usersQuery = User.StartQuery().Field(u => u.Username).Field(u => u.Email);
List<User> users = usersQuery.Run();

This will only retrieve the Username and Email fields of the users, instead of all the fields from the table.

Sort

The Sort method allows you to specify a field to sort the results in a specific order (ascending or descending).

Example:

var usersQuery = User.StartQuery().Sort(u => u.LastLogin, Sort.Descending);
List<User> sortedUsers = usersQuery.Run();

This sorts the users by the date of their last login in descending order.

Include

The Include method allows you to include related objects (e.g., a foreign key relation or another model).

Example:

var query = User.StartQuery().Include(u => u.Role);
List<User> usersWithRole = query.Run();

This includes the role of the users in the results.

Limit

The Limit method allows you to limit the number of results returned by the query.

Example:

var usersQuery = User.StartQuery().Limit(10);
List<User> limitedUsers = usersQuery.Run();

This limits the query to return only 10 results.

Offset

The Offset method allows you to skip a number of results before starting to return results.

Example:

var usersQuery = User.StartQuery().Offset(5).Limit(10);
List<User> paginatedUsers = usersQuery.Run();

This skips the first 5 results and returns the next 10, useful for pagination.

Take

The Take method allows you to specify the number of results to return.

Example:

var usersQuery = User.StartQuery().Take(10);
List<User> first10Users = usersQuery.Run();

This returns the first 10 users.

You can alse use the Take(int length, int offset) method to combine the functionality of Take() and Offset(), allowing you to specify both the number of results to return and how many results to skip before starting to retrieve.

Example:

var usersQuery = User.StartQuery().Take(10, 20);
List<User> paginatedUsers = usersQuery.Run();

This returns 10 users starting from the 21st result, useful for advanced pagination.

Update Builder

The update builder allow you to create a UPDATE query.

Run

The Run method executes the update operation and returns a list of updated items, or null if no items were updated.

Example:

var user = new User { Id = 1, IsActive = false };
var updateQuery = User.StartUpdate();
List<User>? updatedUsers = updateQuery.Run(user);

This updates the IsActive field of the user with Id = 1 to false. If the user is updated, the method returns a list of updated users (even if it contains only one element here).

RunWithError

The RunWithError method executes the update and returns a ResultWithError object that contains the list of updated items, while handling any errors.

Example:

var user = new User { Id = 1, IsActive = false };
var updateQuery = User.StartUpdate();
ResultWithError<List<User>> result = updateQuery.RunWithError(user);

SingleWithError

The SingleWithError method executes the update and returns a ResultWithError object containing a single updated item, with error handling.

Example:

var user = new User { Id = 1, IsActive = false };
var updateQuery = User.StartUpdate().Field(u => u.IsActive);
ResultWithError<User> result = updateQuery.RunWithErrorSingle(user);

Here, we update the user with Id = 1, and the method returns an object containing the updated user or an error if something goes wrong.

Single

The Single method executes the update and returns a single result. If no result is found, it returns null.

Example:

var updateQuery = User.StartUpdate().Where(u => u.Username == "john");
User? user = updateQuery.Single();

Here, we update the user with Id = 1, and the method returns an object containing the updated user.

Field

The Field method allows you to specify one or more fields to update in the query.

Example:

var user = new User { Id = 1, Username = "newUsername" };
var updateQuery = User.StartUpdate().Field(u => u.Username);
List<User>? updatedUsers = updateQuery.Run(user);

This updates the Username field of the user. You can chain multiple calls to Field to update multiple fields.

Where

The Where method allows you to add a condition to the update query.

Example:

var user = new User { Id = 1, Username = "newUsername" };
var updateQuery = User.StartUpdate().Field(u => u.Username).Where(u => u.Id == 1);
List<User>? updatedUsers = updateQuery.Run(user);

Here, we update the Username field only for the user whose Id is equal to 1.

WhereWithParameters

The WhereWithParameters method allows you to add a condition to the update query using parameters. This is useful for more complex conditions with dynamic variables.

Example:

int userId = 1;
if(updateQuery == null)
    updateQuery = User.StartUpdate().Field(u => u.Username).WhereWithParameters(u => u.Id == userId);
List<User>? updatedUsers = updateQuery.New().SetVariables((define) => define("userId", userId)).Run(new User { Id = userId, Username = "newUsername" });

Here, the update condition is based on a dynamic parameter (userId), which allows you to reuse the same logic for different parameter values.

New

The New method allows you to create a new query when you use WhereWithParameters. This method lock concurrency action and until a Run/RunWithError/Single/SingleWithError is called.

Example:

int userId = 1;
var updateQuerySaved = User.StartUpdate().Field(u => u.Username).WhereWithParameters(u => u.Id == userId);
// create a new query to prevent concurrency until the run method
var updateQuery = updateQuerySaved.New();
updateQuery.Prepare(userId);
List<User>? updatedUsers = updateQuery.Run(new User { Id = userId, Username = "newUsername" });

Prepare

The Prepare method allows you to prepare the query by adding additional objects or parameters to the query before execution.

Example:

int userId = 1;
var updateQuery = User.StartUpdate().Field(u => u.Username).WhereWithParameters(u => u.Id == userId).New();
updateQuery.Prepare(userId);
List<User>? updatedUsers = updateQuery.Run(new User { Id = userId, Username = "newUsername" });

Here, we prepare the query with parameters before executing the update.

SetVariables

The SetVariables method allows you to define specific variables for the update query.

Example:

int userId = 1;
var updateQuery = User.StartUpdate().Field(u => u.Username).WhereWithParameters(u => u.Id == userId).New();
updateQuery.SetVariables((define) => {
  define("userId", userId);
});
List<User>? updatedUsers = updateQuery.Run(new User { Id = userId, Username = "newUsername" });

Delete Builder

The delete builder allow you to create a DELETE query.

Run

The Run method executes the delete operation and returns a list of deleted items, or null if no items were deleted.

Example:

var deleteQuery = User.StartDelete().Where(u => u.Id == 1);
List<User>? deletedUsers = deleteQuery.Run();

This example deletes the user with Id = 1. If the user is found and deleted, the method returns a list of deleted users (even if it contains only one element here).

RunWithError

The RunWithError method executes the deletion and returns a ResultWithError object containing the list of deleted items, while handling any errors.

Example:

var deleteQuery = User.StartDelete().Where(u => u.Id == 1);
ResultWithError<List<User>> result = deleteQuery.RunWithError();

Here, the delete operation is wrapped in a ResultWithError object.

Where

The Where method allows you to add a condition to the delete query using a lambda expression.

Example:

var deleteQuery = User.StartDelete().Where(u => u.Id == 1);
List<User>? deletedUsers = deleteQuery.Run();

This adds a condition to the delete query: only users with an Id equal to 1 will be deleted. If a user with that ID exists, they will be deleted.

WhereWithParameters

The WhereWithParameters method allows you to add a condition to the delete query using dynamic parameters. This enables you to parameterize conditions with external variables.

Example:

int userId = 1;
if(deleteQuery == null)
    deleteQuery = User.StartDelete().WhereWithParameters(u => u.Id == userId);
List<User>? deletedUsers = deleteQuery.New().SetVariables((define) => define("userId", userId)).Run();

In this example, the delete condition is based on a dynamic parameter (userId). This allows you to reuse the same delete logic while changing the parameter value without redefining the condition each time.

New

The New method allows you to create a new query when you use WhereWithParameters. This method lock concurrency action and until a Run/RunWithError/Single/SingleWithError is called.

Example:

int userId = 1;
var deleteQuerySaved = User.StartDelete().WhereWithParameters(u => u.Id == userId);
// create a new query to prevent concurrency until the run method
var deleteQuery = deleteQuerySaved.New();
deleteQuery.Prepare(userId);
List<User>? deletedUsers = deleteQuery.Run();

Prepare

The Prepare method allows you to prepare the query by adding additional objects or parameters to the query before execution.

Example:

int userId = 1;
var deleteQuery = User.StartDelete().WhereWithParameters(u => u.Id == userId).New();
deleteQuery.Prepare(userId);
List<User>? deletedUsers = deleteQuery.Run();

SetVariables

The SetVariables method allows you to define specific variables for the delete query.

Example:

var deleteQuery = User.StartDelete().WhereWithParameters(u => u.Id == 1).New();
deleteQuery.SetVariables((define) => {
  define("userId", 1);
});
List<User>? deletedUsers = deleteQuery.Run();

In this case, the userId variable is defined for the delete query and used in the condition where the user Id must equal 1. You can reuse this same variable for other queries.

Exist Builder

The query builder allow you to create a SELECT COUNT(*) query.

Run

The Run method executes the existence check and returns a boolean indicating whether the item exists or not.

Example:

var existQuery = User.StartExist().Where(u => u.Id == 1);
bool exists = existQuery.Run();

This example checks if a user with Id = 1 exists in the database. If the user is found, the method returns true; otherwise, it returns false.

RunWithError

The RunWithError method executes the existence check with error handling and returns a ResultWithError object containing a boolean indicating whether the item exists, along with any error details if applicable.

Example:

var existQuery = User.StartExist().Where(u => u.Id == 1);
ResultWithError<bool> result = existQuery.RunWithError();

Here, if an error occurs during the execution of the existence check (e.g., a database connection issue), the error will be captured.

Where

The Where method allows you to add a condition to the existence check query using a lambda expression.

Example:

var existQuery = User.StartExist().Where(u => u.Id == 1);
bool exists = existQuery.Run();

Here, the condition for the existence check is that the Id of the user must equal 1. If a user with this ID exists, the method returns true; otherwise, it returns false.

WhereWithParameters

The WhereWithParameters method allows you to add a condition to the existence check query using dynamic parameters. This enables you to parameterize the conditions with external variables.

Example:

int userId = 1;
if(existQuery == null)
    existQuery = User.StartExist().WhereWithParameters(u => u.Id == userId);
bool exists = existQuery.New().SetVariables((define) => define("userId", userId)).Run();

In this example, the existence check condition is based on a dynamic parameter (userId). This allows you to reuse the same existence check logic for different user IDs without having to redefine the query each time.

New

The New method allows you to create a new query when you use WhereWithParameters. This method lock concurrency action and until a Run/RunWithError/Single/SingleWithError is called.

Example:

int userId = 1;
if(existQuerySaved == null)
    existQuerySaved = User.StartExist().WhereWithParameters(u => u.Id == userId);

// create a new query to prevent concurrency until the run method
var existQuery = existQuerySaved.New();
// Set the query variables
existQuery.SetVariables((define) => {
  define("userId", userId);
});
// Execute the query and release the mutex for the next query
bool exists = existQuery.Run();

Prepare

The Prepare method allows you to prepare the existence check by setting parameters to the query before execution.

Example:

int userId = 1;
var existQuery = User.StartExist().WhereWithParameters(u => u.Id == userId).New();
existQuery.Prepare(userId);
bool exists = existQuery.Run();

SetVariables

The SetVariables method allows you to define a specific variable for query. This enables you to reuse queries.

Example:

int userId = 1;
var existQuery = User.StartExist().WhereWithParameters(u => u.Id == userId).New();
existQuery.SetVariables((define) => {
  define("userId", userId);
});
bool exists = existQuery.Run();

Transaction

A database transaction is a sequence of operations that are treated as a single unit. Transactions ensure that all operations within the block are completed successfully before they are committed to the database. If any operation fails, the transaction is rolled back, and none of the changes are saved. This approach maintains data integrity by ensuring either full completion or full rollback of related operations.

If you need to perform multiple actions within a single transaction in your database, you can use the RunInsideTransaction method provided by the MySQL connector. The transaction will be committed if no errors occur; otherwise, it will be rolled back.

The following example demonstrates how to use RunInsideTransaction in C# to execute multiple functions within a transaction.

Aventus.Storage.RunInsideTransaction(() =>
{
    VoidWithError result = new VoidWithError();
    result.Run(Fct1);
    result.Run(Fct2);
    return result;
});

In this example, RunInsideTransaction runs both Fct1 and Fct2. If both operations succeed, the transaction is committed. If an error occurs, the transaction is automatically rolled back to prevent partial updates.

Loading Dependencies

When working with relationships between models, it’s possible to load only the ID for a linked model. This can result in a scenario where a foreign key ID is loaded, but the full related object is not.

Consider the following User class, where only RoleId (the foreign key) is loaded initially, while the Role object itself is not.

public class User : Storable<User>
{
    [ForeignKey<Role>]
    public int RoleId { get; set; }
    [NotInDB]
    public Role? Role { get; set; }
}

In this setup, only RoleId is loaded from the database, leaving Role as null. To load the full Role object, you can use the static LoadDependencies function.

The LoadDependencies function can load and map related objects, based on the foreign key.

Example:

List<User> users = new List<User>();
User.LoadDependencies<Role>(users, user => user.RoleId, (user, role) => user.Role = role);

In this example:

The LoadDependencies method loads the Role objects corresponding to each User’s RoleId.
user.RoleId specifies the foreign key.
(user, role) => user.Role = role assigns each Role object to its corresponding User.

You can also use the LoadDependancesList if you have a many to many relationship.

This approach allows you to load related objects on demand, improving performance by loading only when necessary.