We would like you and the community of .NET to understand the limitations of the library before you decide using it. RepoDB is a micro-ORM that has some advance features built to address the advance use-cases. But, it also has its own limitations that may not work in all use-cases.
Disclaimer: This page may not contain all the limitations as the other use-cases is not yet discovered. We will further update this page for any discoveries pertaining to the use-cases that cannot be supported (or impossible to support).
- Composite Keys
- Auto-Generated Primary Column
- Computed Columns
- JOIN Query (Support)
- Cache Invalidation
- Advance Query Tree Expression
- Multiple Identity Columns
The default support to this will never be implemented as RepoDB tend to sided the other scenario that is forcefully eliminating this use-case. When you do the push operations in RepoDB (i.e.: Insert, Delete, Update, Merge and etc), it uses the PK as the qualifiers.
using (var connection = new SqlConnection(ConnectionString))
{
var id = connection.Insert<Person>(new Person { Name = "John Doe" });
}Though the call above may insert the data in your database, but the return value will not be the value of the Composite Keys, instead, it will return the ID of that row, whether the ID column is an identity (or not). The value of the Composite Keys cannot be returned on this situation as we only expect a scalar value. This is also true for the other operations, specially for the Bulk Operations.
Another example is for update operation, we tend to defaultly use the PK as the qualifier. See the code below
using (var connection = new SqlConnection(ConnectionString))
{
var affectedRows = connection.Update<Person>(new Person { Name = "John Doe" }, 10045);
}In which, the 10045 is pointed to a single column in the DB, which is the PK.
If you use the code below, it will behave unexpectedly if you have the Composite Keys in the Name and DateOfBirth columns.
using (var connection = new SqlConnection(ConnectionString))
{
var affectedRows = connection.Update<Person>(new Person { Id = 10045, Name = "John Doe", DateOfBirth = DateTime.Parse("1970/01/01"), Address = "New York" });
}Alternative Solution
RepoDB will instead ask you to do it this way, targeting the Composite Keys as the qualifiers.
using (var connection = new SqlConnection(ConnectionString))
{
var person = new Person { Name = "John Doe", DateOfBirth = DateTime.Parse("1970/01/01"), Address = "New York" };
var affectedRows = connection.Update(person, e => e.Name = person.Name && e.DateOfBirth = person.DateOfBirth);
}Same goes to the Update scenario, we use the PK as the default qualifier.
using (var connection = new SqlConnection(ConnectionString))
{
var affectedRows = connection.Delete<Person>(10045);
}It is impossible to map the value of 10045 on the Composite Keys.
Alternative Solution
Simply do the expression-based or dynamic-based approach by targeting the Composite Keys.
using (var connection = new SqlConnection(ConnectionString))
{
var affectedRows = connection.Delete(e => e.Name == "John Doe" && e.DateOfBirth == DateTime.Parse("1970/01/01"));
}Or like below.
using (var connection = new SqlConnection(ConnectionString))
{
var affectedRows = connection.Delete(ClassMappedNameCache.Get<Person>(), new { Name = "John Doe", DateOfBirth = DateTime.Parse("1970/01/01") });
}Note: There may be plenty of undiscovered scenarios that makes RepoDB unusable for the use-cases of having a table with Composite Keys.
In the past, RepoDB always assume that the user will always define the identity column as the primary column. With this, the library is actually failing during the PUSH operations (i.e.: Insert, Merge, Update, etc) in the event the user has separated both the identity and primary into two columns.
Relating to the issue #1027, the major changes to the behavior pertaining to the scenario explained above has been introduced to the library. The updates to the behavior will now allow the library to "only" return and hydrate the value of the identity column back to the model, ignoring the value of the primary column. This is also true to all other columns with default values. The updates will be available to all versions > 1.12.10.
This scenario is considered a limintation due to the library only returns single value and we tend to put more priority on the identity column over the other column types.
Note: The statement above is also true to a case where a user defined a default-value to the primary column (i.e.: UUID in MySQL).
Though the computed column is supported in all fluent-based GET operations (i.e.: Query, QueryAll, etc), but by default, it is not supported for all fluent-based PUSH operations (i.e.: Insert, Merge, Update, etc). For you to understand the computed columns, we recommend that you visit this Microsoft documentation.
It is important to take note that all the non fluent-based methods like Query(TableName) and Insert(TableName) supports the Computed Columns.
RepoDB is dynamic enough on the property projection, but it does not eliminate the computed columns on its projection. Historically, we have the IgnoreAttribute in placed but has been removed in response to the auto-projection capabilities. Of course, up until being pushed by the community to prove the commonality of this use-case, the support to this may not be delivered. To be specific, please see the example below.
Supposed you have a class named Person.
public class Person
{
public long Id { get; set; }
public string Name { get; set; }
public DateTime? DateOfBirth { get; set; }
public int Age { get; set; }
}And you created a table People below.
CREATE TABLE [dbo].[Person](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
[DateOfBirth] [datetime] NULL,
[Age] AS (DATEDIFF(YEAR,[DateOfBirth], GETUTCDATE())),
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY];Notice, the column Age is a computed column.
As mentioned above, RepoDB will work if you are to use any of the GET operations.
using (var connection = new SqlConnection(ConnectionString))
{
var people = connection.QueryAll<Person>();
}But will fail in the push operation.
using (var connection = new SqlConnection(ConnectionString))
{
var id = connection.Insert<Person, long>(new Person { Name = "John Doe", DateOfBirth = DateTime.Parse("1970/01/01") });
}Alternative Solution
We recommend to use the targeted operation when doing a push, see the sample code snippet below.
using (var connection = new SqlConnection(ConnectionString))
{
var id = connection.Insert(ClassMappedNameCache.Get<Person>(), new { Name = "John Doe", DateOfBirth = DateTime.Parse("1970/01/01") });
}Or, like below.
using (var connection = new SqlConnection(ConnectionString))
{
var id = connection.Insert<Person>(person, fields: Field.From("Id", "Name", "DateOfBirth"));
}In which the computed columns must/are eliminated from the fields argument. Please note that you can always extract the list of the fields of the entity model via FieldCache.Get<T> method.
Or, create a dedicated model for GET operations that has a computed column in it and a dedicated model for PUSH operations that has no computed column in it.
public class Person
{
public long Id { get; set; }
public string Name { get; set; }
public DateTime? DateOfBirth { get; set; }
}
[Table("[dbo].[Person]")]
public class CompletePerson
{
public long Id { get; set; }
public string Name { get; set; }
public DateTime? DateOfBirth { get; set; }
public int Age { get; set; }
}And do query like below.
using (var connection = new SqlConnection(ConnectionString))
{
var people = connection.QueryAll<CompletePerson>();
}And do insert like below.
using (var connection = new SqlConnection(ConnectionString))
{
var id = connection.Insert<Person, long>(new Person { Name = "John Doe", DateOfBirth = DateTime.Parse("1970/01/01") });
}We understand the reality that without having a support to JOIN Query will somehow eliminate the concepts of ORM in the library. The correct term maybe is Object-Mapper (OM) library, rather than Object/Relational Mapper (ORM) library. Though we consider RepoDB as ORM due to the fact of its flexible features. We tend to leave to the users on how will they implement the JOIN Query, on their own perusal.
We see that majority of the problems of the RDBMS data providers are managing the relationships. These includes the constraints, delegations, cascading and many more. To maintain the robustness of the library and put the control to the users when doing the things, we purposely did not supported this feature (for now), up until we have a much better solution ahead of other ORM libraries.
Example
You would like to retrieve the related data of the Supplier record.
Given with these classes.
public class Address
{
public int Id { get; set; }
...
}
public class Product
{
public int Id { get; set; }
...
}
public class Warehouse
{
public int Id { get; set; }
...
}
public class Supplier
{
public int Id { get; set; }
public IEnumerable<Address> Addresses { get; set; }
public IEnumerable<Product> Products { get; set; }
public IEnumerable<Warehouse> Warehouses { get; set; }
}You write the code below.
using (var connection = new SqlConnection(ConnectionString))
{
var supplier = connection
.Query<Customer>(e => e.Name == "Amazon")
.Include<Address>()
.Include<Product>()
.Include<Warehouse>();
}And have these scripts executed by ORM.
> SELECT * FROM [Supplier] WHERE Name = 'Amazon';
> SELECT A.* FROM [Address] A INNER JOIN [Supplier] S ON S.Id = A.SupplierId WHERE A.Name = 'Amazon';
> SELECT P.* FROM [Product] P INNER JOIN [Supplier] S ON S.Id = P.SupplierId WHERE P.Name = 'Amazon';
> SELECT W.* FROM [Warehouse] W INNER JOIN [Warehouse] S ON S.Id = W.SupplierId WHERE A.Name = 'Amazon';
We do not want to control the implementation for now, but instead we leave it all to you. We do not know yet whether the solution of multiple execution is acceptable to the community with the use of CTE, LEFT JOIN, OUTER APPLY or whatever techniques.
SplitQuery
Though SplitQuery seems to be working in this case, solving the problem beyond N+1. Let us say you write the code below.
using (var connection = new SqlConnection(ConnectionString))
{
var supplier = connection
.SplitQuery<Customer, Address, Product, Warehouse>(e => e.Name == "Amazon")
.For<Address>(e => e.Id)
.For<Product>(e => e.Id)
.For<Wharehouse>(e => e.Id)
}That may execute this LEFT JOIN query from the database.
> SELECT S.*
> , A.*
> , P.*
> , W.*
> FROM [Supplier] S
> LEFT JOIN [Address] A ON A.SupplierId = S.Id
> LEFT JOIN [Product] P ON P.SupplierId = S.Id
> LEFT JOIN [Warehouse] W ON W.SupplierId = S.Id
> WHERE S.Name = 'Amazon';Is still not the most optimal thing to do as it needed a lot of process on the data afterwards. Like grouping the main object and the other N+X values.
Alternative Solution
We tend to ask the community to use the QueryMultiple operation to solve this.
using (var connection = new SqlConnection(connectionString).EnsureOpen())
{
var result = connection.QueryMultiple<Supplier, Address, Product, Warehouse>(s => s.Id == 10045,
a => a.SupplierId == 10045,
p => p.SupplierId == 10045,
w => w.SupplierId == 10045);
var supplier = result.Item1.FirstOrDefault();
var addresses = result.Item2.AsList();
var products = result.Item3.AsList();
var warehouses = result.Item4.AsList();
// Do the stuffs here
}Or via the ExecuteMultiple operation.
using (var connection = new SqlConnection(connectionString).EnsureOpen())
{
using (var result = connection.ExecuteQueryMultiple(@"SELECT * FROM [dbo].[Supplier] WHERE [Id] = @SupplierId;
SELECT * FROM [dbo].[Address] WHERE SupplierId = @SupplierId;
SELECT * FROM [dbo].[Product] WHERE SupplierId = @SupplierId;
SELECT * FROM [dbo].[Warehouse] WHERE SupplierId = @SupplierId;"))
{
var supplier = result.Extract<Person>().FirstOrDefault();
var addresses = result.Extract<Address>().AsList();
var products = result.Extract<Product>().AsList();
var warehouses = result.Extract<Warehouse>().AsList();
// Do the stuffs here
}
}The good thing to this is controlled by you, and that is a very important case to us.
Though it is quietly important for the cache to get invalidated at some point in time, we in RepoDB decided not to introduce the additional layer that checks the cache invalidity. The reason is to keep the library as light as possible without it allowing to do some extra validations or any background processes that does the hidden jobs.
By default, the length of the cache item expiration is 180 minutes. It uses the value of Constant.DefaultCacheItemExpirationInMinutes property.
Example
Via DbConnection object.
var cache = CacheFactory.Create();
using (var connection = new SqlConnection(connectionString).EnsureOpen())
{
var customers = connection.QueryAll<Customer>(cacheKey: "AllCustomers", cache: cache);
}Via BaseRepository or DbRepository object.
using (var repository = new DbRepository<SqlConnection>(connectionString, cache: CacheFactory.Create()))
{
var customers = repository.QueryAll<Customer>(cacheKey: "AllCustomers");
}The returned value of the QueryAll operation will be cached to the cache argument object for the next 180 minutes. The cache item will not auto validate itself when you and/or somebody had changed the list of customers from the database.
When the cache item has expired, it will again do the wired-up in the database and will retrieve the latest and greatest row information.
Alternative Solution
You have to manually invalidate the cache item by simply removing it from the cache itself.
var cache = CacheFactory.Create(); // Get the instance from the factor
cache.Remove("AllCustomers");By explicitly removing it, any of the fetch operations that does pointed to the cache key AllCustomers will again retrieve the latest information from the database.
RepoDB is only supporting a shallow implementation of the query tree expressions. Also mentioned in the above section, RepoDB does not support JOINs in which many developers usually implements the more deeper logics pertaining to the query tree expressions, specifically when using the Entity Framework.
RepoDB only support first level (deep) when using the query tree expression, anything beyond is not supported by RepoDB.
Let us say you have the class models below.
public class Address
{
public int Id { get; set; }
public string Street { get; set; }
...
}
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public Address Address { get; set; }
...
}And you did this.
using (var customer = new SqlConnection(connectionString))
{
var customers = connection.Query<Customer>(e => e.Address.Country == "New York");
}Any expression that is unbound to the property will not be supported.
using (var customer = new SqlConnection(connectionString))
{
var customers = connection.Query<Customer>(e => DateTime.UtcNow >= DateTime.UtcNow.Date);
}Any expression that compares a field to another field.
using (var customer = new SqlConnection(connectionString))
{
var sales = connection.Query<Sale>(e => e.TotalPrice >= (e.Price * e.Quantity));
}It is also important to take note that the complex first level deep is somewhat not supported. As part of our disclaimer in the query expression trees, we therefore highly recommend to always use the QueryField or QueryGroup objects when composing the complex expression trees.
As recently discovered edge-case scenario, in PosgreSQL, multiple identity columns can be identified in a single table, unlike with other RDBMS. See below.
CREATE TABLE IF NOT EXISTS public."Person"
(
"Id" bigint NOT NULL DEFAULT nextval('person_id_seq'::regclass),
"OtherId" bigint NOT NULL DEFAULT nextval('person_otherid_seq'::regclass),
...
CONSTRAINT person_pkey PRIMARY KEY ("Id")
);The library core SQL Statement builder is only limited to construct a SQL statement with only identity column (at max) available on the table. This is also true to all the other supported RDBMS. Any identity column on top of the default (usual use-case) identity/primary column will be excluded on the parameter passing in all push operations, forcing the library to fail.
It is unfortunate, there is no rectification to this other than by maintaining a single identity column on the table.