A hybrid ORM library for .NET.
Package: https://www.nuget.org/packages/RepoDb
Documentation: https://repodb.readthedocs.io/en/latest/
Follow @mike_pendon at Twitter.
- RepoDb is the fastest and the most efficient ORM library in .NET as per the result of RawDataAccessBencher. You can see the actual execution result here.
- RepoDb is covered by thousand of major business related Unit Tests and Integration Tests.
| Type of Build | Net (Framework) | Net (Standard) |
|---|---|---|
| Project/Solution | ||
| Unit Test | ||
| Integration Test |
- It is very fast in CRUD operations.
- It is very efficient in memory usage.
- It is highly extensible.
- It is easy to switch between lightweight and method-based operations.
- It is simple, fluent and clean.
- It is easy to write RawSql statements.
- It is easy to cache the data.
- It is well covered by Unit/Integration tests.
- It has Batch operations.
- It has Bulk-Insert operation.
- It has massive ORM operations.
- It has Async operations.
- It has Enumeration supports.
- It has Multi-ResultSet query operations.
- It has Type mapping.
- It has Query Hints.
- It has built-in Repositories.
- It has Dynamic Expressions support.
- It has Linq Expressions support.
- It has Table-Based call operations.
- It has an extensible Tracers.
- It has an extensible Database Helpers.
- It has an extensible DB Operation Providers.
- It has an extensible Statement Builders.
- It has ADO.NET transaction supports.
- It is always free!
- Asynchronous Operations
- Batch Operations
- Bulk Operations
- Caching
- Connection Persistency
- Database Helpers
- Database Operation Providers
- Expression Trees
- Field Mapping
- Inline Hints
- Massive Operations (Generics/Explicits/MethodCalls/TableBased)
- Multi-Resultset Query
- Query Builder
- Repositories
- Statement Builder
- Tracing
- Transaction
- Type Mapping
Let us say you have a customer class named Customer that has an equivalent table in the database named [dbo].[Customer].
public class Customer
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public bool IsActive { get; set; }
public DateTime LastUpdatedUtc { get; set; }
public DateTime CreatedDateUtc { get; set; }
}Via PrimaryKey:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query<Customer>(10045);
}Via Dynamic:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query<Customer>(new { Id = 10045 });
}Via Expression:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query<Customer>(c => c.Id == 10045);
}Via Object:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query<Customer>(new QueryField(nameof(Customer.Id), 10045));
}Via PrimaryKey:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query("Customer", 10045);
}Via Dynamic:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query("Customer", new { Id = 10045 });
}Via Object:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query("Customer", new QueryField(nameof(Customer.Id), 10045));
}Via Object (targetting few fields):
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query("Customer", new QueryField(nameof(Customer.Id), 10045),
Field.From("Id", "FirstName", "LastName"));
}var customer = new Customer
{
FirstName = "John",
LastName = "Doe",
IsActive = true
};
using (var connection = new SqlConnection(ConnectionString))
{
var id = connection.Insert<Customer, int>(customer);
}var customer = new
{
FirstName = "John",
LastName = "Doe",
IsActive = true,
LastUpdatedUtc = DateTime.Utc,
CreatedDateUtc = DateTime.Utc
};
using (var connection = new SqlConnection(ConnectionString))
{
var id = connection.Insert<int>("Customer", customer);
}Via DataEntity:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query<Customer>(10045);
customer.FirstName = "John";
customer.LastUpdatedUtc = DateTime.UtcNow;
var affectedRows = connection.Update<Customer>(customer);
}Via PrimaryKey:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query<Customer>(10045);
customer.FirstName = "John";
customer.LastUpdatedUtc = DateTime.UtcNow;
var affectedRows = connection.Update<Customer>(customer, 10045);
}Via Dynamic:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query<Customer>(10045);
customer.FirstName = "John";
customer.LastUpdatedUtc = DateTime.UtcNow;
var affectedRows = connection.Update<Customer>(customer, new { Id = 10045 });
}Via Object:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query<Customer>(10045);
customer.FirstName = "John";
customer.LastUpdatedUtc = DateTime.UtcNow;
var affectedRows = connection.Update<Customer>(customer, new QueryField(nameof(Customer.Id), 10045));
}Via Dynamic Object:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = new
{
Id = 10045,
FirstName = "John",
LastUpdatedUtc = DateTime.UtcNow
};
var affectedRows = connection.Update("Customer", customer);
}Via PrimaryKey:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = new
{
FirstName = "John",
LastUpdatedUtc = DateTime.UtcNow
};
var affectedRows = connection.Update("Customer", customer, 10045);
}Via Dynamic:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = new
{
FirstName = "John",
LastUpdatedUtc = DateTime.UtcNow
};
var affectedRows = connection.Update("Customer", customer, new { Id = 10045 });
}Via Object:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = new
{
FirstName = "John",
LastUpdatedUtc = DateTime.UtcNow
};
var affectedRows = connection.Update("Customer", customer, new QueryField("Id", 10045));
}Via PrimaryKey:
using (var connection = new SqlConnection(ConnectionString))
{
var deletedCount = connection.Delete<Customer>(10045);
}Via Dynamic:
using (var connection = new SqlConnection(ConnectionString))
{
var deletedCount = connection.Delete<Customer>(new { Id = 10045 });
}Via Expression:
using (var connection = new SqlConnection(ConnectionString))
{
var deletedCount = connection.Delete<Customer>(c => c.Id == 10045);
}Via Object:
using (var connection = new SqlConnection(ConnectionString))
{
var deletedCount = connection.Delete<Customer>(new QueryField(nameof(Customer.Id), 10045));
}Via DataEntity:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.Query<Customer>(new { Id = 10045 });
var deletedCount = connection.Delete<Customer>(customer);
}Via PrimaryKey:
using (var connection = new SqlConnection(ConnectionString))
{
var deletedCount = connection.Delete("Customer", 10045);
}Via Dynamic:
using (var connection = new SqlConnection(ConnectionString))
{
var deletedCount = connection.Delete("Customer", { Id = 10045 });
}Via Object:
using (var connection = new SqlConnection(ConnectionString))
{
var deletedCount = connection.Delete("Customer", new QueryField(nameof(Customer.Id), 10045));
}var customer = new Customer
{
FirstName = "John",
LastName = "Doe",
IsActive = true,
LastUpdatedUtc = DateTime.Utc,
CreatedDateUtc = DateTime.Utc
};
using (var connection = new SqlConnection(ConnectionString))
{
var qualifiers = new []
{
new Field(nameof(Customer.FirstName)),
new Field(nameof(Customer.LastName)),
};
var mergeCount = connection.Merge<Customer>(customer, qualifiers);
}var customer = new Customer
{
FirstName = "John",
LastName = "Doe",
IsActive = true
};
using (var connection = new SqlConnection(ConnectionString))
{
var qualifiers = new []
{
new Field(nameof(Customer.FirstName)),
new Field(nameof(Customer.LastName)),
};
var mergeCount = connection.Merge("Customer", customer, qualifiers);
}You can create a class with combined properties of different tables or with stored procedures. It does not need to be 100% identical to the schema, as long the property of the class is part of the result set.
public class ComplexClass
{
public int CustomerId { get; set; }
public int OrderId { get; set; }
public int ProductId { get; set; }
public string CustomerName { get; set; }
public string ProductName { get; set; }
public DateTime ProductDescription { get; set; } // This is not in the CommandText, will be ignored
public DateTime OrderDate { get; set; }
public int Quantity { get; set; }
public double Price { get; set; }
}Then you can create this command text.
var commandText = @"SELECT C.Id AS CustomerId
, O.Id AS OrderId
, P.Id AS ProductId
, CONCAT(C.FirstName, ' ', C.LastName) AS CustomerName
, P.Name AS ProductName
, O.OrderDate
, O.Quantity
, P.Price
, (O.Quatity * P.Price) AS Total /* Note: This is not in the class, but still it is valid */
FROM [dbo].[Customer] C
INNER JOIN [dbo].[Order] O ON O.CustomerId = C.Id
INNER JOIN [dbo].[OrderItem] OI ON OI.OrderId = O.Id
INNER JOIN [dbo].[Product] P ON P.Id = OI.ProductId
WHERE (C.Id = @CustomerId)
AND (O.OrderDate BETWEEN @OrderDate AND DATEADD(DAY, 1, @OrderDate));";
Via Dynamic:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.ExecuteQuery<ComplexClass>(commandText, new { CustomerId = 10045, OrderDate = DateTime.UtcNow.Date });
}Via Object:
using (var connection = new SqlConnection(ConnectionString))
{
var queryGroup = new QueryGroup(new []
{
new QueryField("CustomerId", 10045),
new QueryField("OrderDate", DateTime.UtcNow.Date)
});
var customer = connection.ExecuteQuery<Customer>(commandText, queryGroup);
}The ExecuteQuery method is purposely not being supported by Expression based query as we are avoiding the user to bind the complex-class to its target query text.
Note: The most optimal when it comes to performance is to used the Object-Based.
Using the complex type above. If you have a stored procedure like below.
DROP PROCEDURE IF EXISTS [dbo].[sp_get_customer_orders_by_date];
GO
CREATE PROCEDURE [dbo].[sp_get_customer_orders_by_date]
(
@CustomerId INT
, @OrderDate DATETIME2(7)
)
AS
BEGIN
SELECT C.Id AS CustomerId
, O.Id AS OrderId
, P.Id AS ProductId
, CONCAT(C.FirstName, ' ', C.LastName) AS CustomerName
, P.Name AS ProductName
, O.OrderDate
, O.Quantity
, P.Price
, (O.Quatity * P.Price) AS Total /* Note: This is not in the class, but still it is valid */
FROM [dbo].[Customer] C
INNER JOIN [dbo].[Order] O ON O.CustomerId = C.Id
INNER JOIN [dbo].[OrderItem] OI ON OI.OrderId = O.Id
INNER JOIN [dbo].[Product] P ON P.Id = OI.ProductId
WHERE (C.Id = @CustomerId)
AND (O.OrderDate BETWEEN @OrderDate AND DATEADD(DAY, 1, @OrderDate));
END
Then it can be called as below.
Via Dynamic:
using (var connection = new SqlConnection(ConnectionString))
{
var customer = connection.ExecuteQuery<ComplexClass>("[dbo].[sp_get_customer_orders_by_date]",
param: new { CustomerId = 10045, OrderDate = DateTime.UtcNow.Date },
commandType: CommandType.StoredProcedure);
}Via Object:
using (var connection = new SqlConnection(ConnectionString))
{
var queryGroup = new QueryGroup(new []
{
new QueryField("CustomerId", 10045),
new QueryField("OrderDate", DateTime.UtcNow.Date)
});
var customer = connection.ExecuteQuery<Customer>(commandText, queryGroup,
commandType: CommandType.StoredProcedure);
}Please visit our documentation for further details about the codes.