Hyperboliq is a .NET based ORM with the following main goals
- Predictability
It should be possible to intuit what SQL statements is going to be generated by your code. No magic allowed. - Control
Hyperboliq will by design not contain any magic or attempt to second-guess you by performing clever query rewrites behind your back. What you see is what you get. No magic allowed. - Expressability
Hyperboliq will aim to support as much of the SQL standard as possible (within reason).
Hyperboliq targets the .NET framework version 4.5.1 and F# 4.3.1. Currently there are no plans to support older versions, though this may change. Apart from this, Hyperboliq does not require anything special to build. Simply restore nuget packages and build the solution with Visual Studio.
Hyperboliq is distributed under the terms of the Apache License v.2.
Hyperboliq is still considered to be in pre-alpha and can still change drastically from one day to another. The API is to be considered unstable and you are not recommended to use this in production yet. Suffice to say you should probably wait for the first official release if you want to use this library.
For now, if you wish to use Hyperboliq you will need to download and build it manually. When Hyperboliq has reached a state that is stable there will be nuget packages. Do be aware of the fact that Hyperboliq is currently in a pre-alpha state and as thus may still have breaking API changes etc.
Hyperboliq aims to use POCOs for accessing data in order to be able to provide as much intellisense/type safety as possible. Hyperboliq will always generate table aliases for all tables in your query. You can provide your own aliases if necessary, though as long as you are not referencing the same table more than once in your query you should not have to do this.
Select<Person>(p => p.Name).From<Person>().Where<Person>(p => p.Age > 42)
SELECT PersonRef.Name
FROM Person PersonRef
WHERE PersonRef.Age > 42
Select.Star<Person>().Star<Car>().From<Person>().InnerJoin<Person, Car>((p, c) => c.DriverId == p.Id)
SELECT PersonRef.*, CarRef.*
FROM Person PersonRef
INNER JOIN Car CarRef ON CarRef.DriverId = PersonRef.Id
var child = Table<Person>.WithReferenceName("child");
var parent = Table<Person>.WithReferenceName("parent");
Select.Column(child, p => p.Name).Column(parent, p => p.Name).From(child).InnerJoin(child, parent, (c, p) => c.ParentId == p.Id)
SELECT child.Name, parent.Name
FROM Person child
INNER JOIN Person parent ON child.ParentId = parent.Id
Select.Column<Person>(p => p.Name)
.Column<Person>(
p => Sql.Sum(p.Age),
Over.PartitionBy<Person>(p => p.Name).OrderBy<Person>(p => p.Age, Direction.Ascending))
.From<Person>();
SELECT PersonRef.Name, SUM(PersonRef.Age) OVER (PARTITION BY PersonRef.Name ORDER BY PersonRef.Age ASC)
FROM Person PersonRef
Hyperboliq allows you to create queries that contain subqueries. However due to the nature of C#'s type system these kind of queries are not as type safe. This may let you write queries that translate into SQL that your database will not accept (and thus throw a SQLException during runtime). If you use subqueries, be extra careful to match the correct types, number of expected results and number of columns to avoid these exceptions during runtime.
// In this case we need to use the utility method Sql.SubExpr<T> in order to help the C# Type system.
Select.Star<Person>()
.From<Person>()
.Where<Person>(p => p.Age > Sql.SubExpr<int>(Select.Column<Car>(c => c.Age)
.From<Car>()
.Where<Car>(c => c.Id == 42)));
SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > (SELECT CarRef.Age FROM Car CarRef WHERE CarRef.Id = 42)
// In the case of subqueries with IN we do not need to use the Sql.SubExpr<T> utility method and can just write the subquery inline.
Select.Star<Person>()
.From<Person>()
.Where<Person>(p => Sql.In(p.Id, Select.Column<Car>(c => c.DriverId).From<Car>()));
SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Id IN (SELECT CarRef.DriverId FROM Car CarRef)
Hyperboliq lets you use common table expressions in your select queries. These do however require an extra class in order to specify how the result set for this CTE looks.
// Class for specifying how the result set of our CTE looks
public class PersonLite
{
public string Name { get; set; }
public int Age { get; set; }
}
With.Table<PersonLite>(
Select.Column<Person>(p => new { p.Name, p.Age })
.From<Person>()
.Where<Person>(p => p.Age > 15))
.Query(
Select.Column<PersonLite>(p => p.Name)
.From<PersonLite>()
.Where<PersonLite>(p => p.Age == 42));
WITH PersonLite AS (SELECT PersonRef.Name, PersonRef.Age FROM Person PersonRef WHERE PersonRef.Age > 15)
SELECT PersonLiteRef.Name FROM PersonLite PersonLiteRef WHERE PersonLiteRef.Age = 42
// This example uses the same PersonLite class as the example above
// Define two aliases for the same result set in order to be able to refer to them separately
var oldies = Table<PersonLite>.WithTableAlias("Oldies");
var younglings = Table<PersonLite>.WithTableAlias("YoungOnes");
With.Table(
oldies,
Select.Column<Person>(p => new { p.Name, p.Age, })
.From<Person>()
.Where<Person>(p => p.Age > 40))
.Table(
younglings,
Select.Column<Person>(p => new { p.Name, p.Age, })
.From<Person>()
.Where<Person>(p => p.Age <= 15))
.Query(
Select.Column(oldies, p => p.Name)
.Column(younglings, p => p.Name)
.From(oldies)
.InnerJoin(oldies, younglings, (old, young) => old.Age - 30 == young.Age));
WITH Oldies AS (SELECT PersonRef.Name, PersonRef.Age FROM Person PersonRef WHERE PersonRef.Age > 40),
YoungOnes AS (SELECT PersonRef.Name, PersonRef.Age FROM Person PersonRef WHERE PersonRef.Age <= 15)
SELECT OldiesRef.Name, YoungOnesRef.Name FROM Oldies OldiesRef INNER JOIN YoungOnes YoungOnesRef ON OldiesRef.Age - 30 = YoungOnesRef.Age
Delete.From<Person>().Where<Person>(p => p.Age > 42)
DELETE FROM Person PersonRef WHERE PersonRef.Age > 42
Hyperboliq lets you perform basic inserts both specifying the columns to use, and just specifying a generic "all columns" selector as seen in these examples
var val = new Person { Id = 2, Name = "Kalle", Age = 42, LivesAtHouseId = 5, ParentId = 0 };
Insert.Into<Person>().Columns(p => new { p.Name, p.Age }).Value(val);
INSERT INTO Person (Name, Age) VALUES ('Kalle', 42)
var val = new Person { Id = 2, Name = "Kalle", Age = 42, LivesAtHouseId = 5, ParentId = 0 };
Insert.Into<Person>().AllColumns.Value(val);
// If you use the AllColumns selector all columns will be used and will be added in alphabetical order
INSERT INTO Person (Age, Id, LivesAtHouseId, Name, ParentId) VALUES (42, 2, 5, 'Kalle', 0)
// It is also possible to perform multi-value inserts using Hyperboliq
var val1 = new Person { Id = 2, Name = "Kalle", Age = 42, LivesAtHouseId = 5, ParentId = 0 };
var val2 = new Person { Id = 3, Name = "Pelle", Age = 12, LivesAtHouseId = 3, ParentId = 2 };
Insert.Into<Person>()
.AllColumns
.Values(val1, val2);
INSERT INTO Person (Age, Id, LivesAtHouseId, Name, ParentId) VALUES (42, 2, 5, 'Kalle', 0), (12, 3, 3, 'Pelle', 2)
Hyperboliq aims to allow you to create UPDATE statements that lets you update data without first selecting it (as is necessary in many other ORMs).
Update<Person>.Set(p => new { p.Name, p.Age },
new { Name = "Kalle", Age = 42})
.Where(p => p.Id == 5);
UPDATE Person SET Age = 42, Name = 'Kalle' WHERE Id = 5
Update<Person>.Set(p => p.Age, p => p.Age + 1).Where(p => p.Name == "Kalle");
UPDATE Person SET Age = Age + 1 WHERE Name = 'Kalle'
Update<Person>.Set(p => p.Age, Select.Column<Car>(c => Sql.Max(c.Age)).From<Car>()).Where(p => p.Name == "Kalle");
UPDATE Person SET Age = (SELECT MAX(CarRef.Age) FROM Car CarRef) WHERE Name = 'Kalle'
Further documentation will be available on the github wiki pages as the API stabilizes.
Hyperboliq also maintains a large battery of unit tests testing query generation in different ways. Until the API stabilizes enough to write decent docs, you are encouraged to look at the unit tests to see different ways to use Hyperboliq.
Hyperboliq aims to use semantic versioning.
- Support for Linq queries.