Skip to content
A hybrid ORM library for .NET.
C# TSQL
Branch: master
Clone or download

README.md

RepoDb

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.

Highlight

  • 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.

Build Results

Type of Build Net (Framework) Net (Standard)
Project/Solution Build status Build status
Unit Test Build status Build status
Integration Test Build status Build status

High-Level Architecture

Why RepoDb?

  • 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!

Features

  • 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

Code Samples

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; }
}

Query

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));
}

Query(TableName)

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"));
}

Insert

var customer = new Customer
{
	FirstName = "John",
	LastName = "Doe",
	IsActive = true
};
using (var connection = new SqlConnection(ConnectionString))
{
	var id = connection.Insert<Customer, int>(customer);
}

Insert(TableName)

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);
}

Update

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));
}

Update(TableName)

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));
}

Delete

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);
}

Delete(TableName)

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));
}

Merge

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);
}

Merge(TableName)

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);
}

ExecuteQuery

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.

StoredProcedure

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.

You can’t perform that action at this time.