The dotnet-orm-cookbook from tortugaresearch

Use Case: List tables/columns in database

This would have four operations. One to list the tables in the database, the second to list the columns in a given table. The other two would do the same for views.

HR.EmployeeClassification.IsEmployee isn't nullable in the DDL SQL, yet is treated as such in the code

It has a NOT NULL clause in the ddl sql yet the code in e.g. EF Core's code treats it like a nullable field.

Switch from MSTest - was: Tests don't show up

I hate MS Test with all cells in my body and again I have no idea why people keep using this stuff. I've added ModelWithLookup repositories, which derive from the test base classes properly. Other tests in the project show up fine, but these tests don't show up in the runner.

Any idea what this might be? Everything builds fine. E.g. : https://github.com/FransBouma/DotNet-ORM-Cookbook/blob/master/ORM%20Cookbook/Recipes.LLBLGenPro/Recipes/ModelWithLookup/ModelWithLookupComplexTests.cs

Entity Framework

Entity Framework Core has some serious limitations compared to Entity Framework 6.x, so people have been asking that both are included.

We'll have to do some research to determine the actual differences so that appropriate use cases can be created.

Use Case: Connection Sharing

This scenario is to let an ORM reused a connection object for multiple calls.
Each call should open and close the connection.

In some cases, an exception is thrown if the ORM does not handle it.

Add LLBLGen to ORM Cookbook

We're restarting the ORM Cookbook project with an emphasis on .NET Core. If you are interested in participating we would love to have LLBLGen in the new cookbook.

If you're not, feel free to close this ticket.

Implement the Immutable CRUD examples for ServiceStack

See #15 for other implementations.

Use case: Return a single value

This is equivalent to calling a DbCommand.ExecuteScalar() method.

RepoDB: Model with FK to lookup table use case

See #14

Use case: Row Ownership Security Check

Depends on #25

In this use case, the update operation must first verify the user was the one who created the row in the first place. If he didn't, a security exception should be thrown.

The specific exception thrown is not specified as part of the test. (We should offer a default case though.)

Use Case: Model with FK to lookup table

Two versions of this:

Model with FK as an int
Model with FK as a child object

ORMs may choose to implement either (or both) patterns

ServiceStack: Model with FK to lookup table use case

See #14

Use Case: Transaction Commit/Rollback

An ORM should show how to to transaction in a committed state.

Use Case: Populate a DataTable

Sounds kinda old fashioned, but it's still used when you want to dump a report into a grid.

Interfaces shouldn't be in Recipes.Core

I have to reference an interface, Recipes.Immutable.IReadOnlyEmployeeClassification. I have to do that from a NS2.0 project (as it's generated) but I can't, because the interface is in a project with tests which targets netcore3.1.

This isn't really ideal, the interfaces have nothing to do with netcore3.1, they should be separate in a project targeting ns2.0. I now have to hand-write the readonly repo with a handwritten poco while I could have used my generated code and not lift a finger. All this because the readonly repository implements an interface which references an interface in a repo targeting netcore 3.1

Alternatively I could change the target of my ns2.0 repository to netcore3.1, but it still doesn't change the fact the interfaces really should be in a separate project, or the tests should be in a separate project.

(the project is of type 'console application' while it contains interfaces to be referenced by other projects which IMHO is a sign something's off)

What's the purpose of the related EmployeeSpecification in ModelWithLookupComplexRepository.Create?

This method is misleading, and a test, ModelWithLookupComplexTests.FoulLookupTable is wrong (as in: what is it testing? That the signature on the repository is wrong?)

The main issue is that the FoulLookup test creates an entity graph and persists this through the ModelWithLookupComplexRepository.Create(). However, as the method changes the entity, saving the graph thus will also save the related entity, so both the new Employee and the related EmployeeClassification entity are saved (as both are changed, employee is new and its associated EmployeeClassification has been changed (FoulLookup, line 179)

If the related entity EmployeeClassification shouldn't be persisted, then the repository should clearly tell me that. However as microORMs don't do graph persistence this isn't really visible, and the EF Core repository reads a new instance of the passed in EmployeeClassification entity (of course otherwise the test would fail, but this makes no sense at all. No-one in production would use it this way. )

I can of course tell my framework not to persist things recursively and the test will pass. That's not the point: this is a 'recipe' book for people who want to see how things are done. IMHO the whole point of the test and repository is wrong and doesn't teach anyone anything other than how not to do it.

Can I take advantage of change tracking in the entity?

My framework has change tracking in the entities. Update tests which accept an entity can directly persist this entity (as it contains the changes), it doesn't need to fetch the one from the db first, update the fields etc. like EF needs.

I'll go ahead and assume it's allowed :)

Remove IDivision1<TDepartment>.Departments setter

The setter is not to be used, it's only there for EF.

Database set: MySQL

Setup a project that, when run, will rebuild a MySQL database.

You can steal the code from the Tortuga Chain tests.

Use Case: Cache results

Cache result on the first call and return the cached version in future requests.

Used for static lookup tables.

ServiceStack: Populate a DataTable Use Case

See #43

Database Setup: PostgreSQL

Create a project that, when run, will rebuild a PostgreSQL database.

You can steal the code from the Chain tests.

Cancellation Tokens

The async use case should provide support for cancellation tokens. You would not normally expose a potentially long running async method without a way to abort it.

Use case: "Insert large number of records at one time use a bulk insert"

This demonstrates basic bulk insert capabilities.

No echo is needed, the rows are simply inserted and forgotten.

Sample sizes:

100
1,000
10,000
100,000

Databases:

SQL Server
MySQL
PostgreSQL
(others?)

Use Case: Pagination

An ORM should show how to paginate the records.

Use case: Stored procedure with multiple resultsets

SQL and PosgreSQL both offer this capability.

Obviously this would require setting up a PostgreSQL database head, but we wanted that anyways.

Use Case: Views

Read single from a view
Read filtered from a view
Read all from a view
~~4. Write to view (or underlying table if writing to a view isn't supported)~~

Use the Employee table joined to the EmployeeClassification table. Writes would only affect the Employee table.

Connection String Overriding

Allow connection strings to be overridden in user settings. This will allow developers to support other server configurations without messing around with the config file.

Replace the word "Repository" with the word "Scenario" in class names

The term 'repository' makes people cranky because of the varying definitions and expectations around that name.

So instead of SingleModelCrudAsyncRepository we should use a name with fewer connotations such as SingleModelCrudAsyncDAL.

Use case: ORM Populated Audit Columns

In this use case the ORM must provide the following information:

CreatedByUserKey
ModifiedByUserKey
CreatedDateTime
UpdatedDateTime

Database Changes

New columns for TABLE HR.Department

    CreatedDate DATETIME2(7) NULL,
    ModifiedDate DATETIME2(7) NULL,
    CreatedByEmployeeKey INT NULL
        CONSTRAINT FK_Department_CreatedByEmployeeKey
        REFERENCES HR.Employee (EmployeeKey),
    ModifiedByEmployeeKey INT NULL
        CONSTRAINT FK_Department_ModifiedByEmployeeKey
        REFERENCES HR.Employee (EmployeeKey)

Use Case: Validation

Enforce before-insert/update validation via one or more of the following:

Attribute based
INotifyDataErrorInfo
IValidatable

RepoDB: Populate a DataTable Use Case

See #43

Use Case: Use a Non-Integrated Security Connection String (PSI = False)

This is important in a way that opening a connection would fail the ORM in the second next calls due to a default behavior of ADO.NET (removing the password).

Use Case: Simple Stored Procedures

This would be a stored proc that returned a single resultset.

Use case: "Insert large number of records at one time use a batch insert"

This demonstrates basic batch insert capabilities.

No echo is needed, the rows are simply inserted and forgotten.

Sample sizes:

100
1,000
10,000
100,000

Databases:

SQL Server
MySQL
PostgreSQL
SQLite

LocalDB for SQL Server

Investigate the use of LocalDB for SQL Server so we're not tied to a specific server setup.

Use case: Insert/Update/Delete Multiple Rows

Insert and update a small collection of rows.

Insert collection
~~Insert collection async~~
Insert collection, echo primary keys
Insert collection, echo records
Insert and update collection
~~Insert and update collection async~~
Insert and delete collection
~~Insert and delete collection async~~

In all cases the sample size will be 100 rows.

Use Case: Immutable objects

Demonstrate populating an immutable object and collection of objects.

Implement the Try-Crud examples for RepoDB

Wait until #6 is done, then use it as a template.

Use Case: Crud operations with dynamic objects

Object returned and accepted is dynamic.

Use case: Table-Valued Functions

This is going to look like the Views use case, except that you have to supply parameters to the TVF.

I believe that this is a SQL Server/PostgreSQL feature.

Implement the Immutable CRUD examples for RepoDB

See #15 for other implementations.
Depends on the redesign from #6.

NHibernate: Model with FK to lookup table use case

See #14

Change RepoDB to use DbRepository or BaseRepository in the CRUD examples

First, I personally thanked you as always.

Your implementation is more generic and honestly we are also doing the same thing here in the organization (as always). Due to the fact of coding legacy when using Dapper :)

Anyway, there is a much more explicit implementation when targeting a Data Entity when creating a Repository object. I wish to use the DbRepository or BaseRepository instead as I am campaigning it to become more standard.

Here is the wiki.

Please advise and thank you in advance!

Use Case: Tables with defaults

For the setup, we'll have 3 nullable columns:

IsExempt BIT NOT NULL
    CONSTRAINT D_EmployeeClassification_IsExempt
        DEFAULT (0),
IsEmployee BIT NOT NULL
    CONSTRAINT D_EmployeeClassification_IsEmployee
        DEFAULT (1),
IncludeInHolidayParty BIT NULL
    CONSTRAINT D_EmployeeClassification_IncludeInHolidayParty
        DEFAULT (1)

Note that the third column is nullable, but it also has a default value. This may prove tricky for some databases.

Test Cases

Insert(NameOnly)
Insert(EmployeeClassification)
Insert(string)
Insert(string, bool, bool, bool?)

Test pattern:

Insert operation (data-driven for the various combinations)
Read and verify
Update to all true
Read and verify
Update to all false
Read and verify
Update to True-False-Null
Read and verify

Partial Updates

Demonstrate performing a partial update on a row.

Use Case: Model with Child Records

Scenario 1:

Create a ProductLine
Add several Product rows to that ProductLine
Return them on one call

Scenario 2:

Create a ProductLine and its Product rows at the same time
Return them on one call

SELECT GETUTCDATE() AS [DateTime], NEWID() AS [Guid];

Class:

public class WhatEverClass
{
	public string DateTime { get; set; }
	public string Guid { get; set; }
}

Note: The receiving class is of different data type. We can talk and expand this further. Hopefully, you consider this proposal soon.

tortugaresearch / dotnet-orm-cookbook Goto Github PK

dotnet-orm-cookbook's People

Contributors

Stargazers

Watchers

Forkers

dotnet-orm-cookbook's Issues

Database Changes

Recommend Projects

Recommend Topics

Recommend Org