- Build all your Select/Update/Insert/Delete T-SQL queries arround your POCOs!
- Fluent interface
- Try-Build pattern (basic validation)
- Evaluate once: use parameters ("@param") within the builder to parametrize your queries!
- Build your complex "WHERE" clauses as sub-blocks you can assemble
- Use complex selectors like aggregates and SQL functions by inheriting the SelectBuilder class.
- Supports table name alias (when you join the same table multiple times)
- All depedencies are abstracted and injected, can easily be integrated with any IoC library.
Here is a basic factory method to get a builder. This factory method can be registered into any container library.
private IQueryBuilderFactory GetBuilder()
{
ISqlTranslator translatorFactory() => new SqlTranslator();
ICompare compareFactory() => new Comparator();
IWhereBuilderFactory whereBuilderFactory() => new WhereBuilderFactory(compareFactory);
return new SqlQueryBuilderFactory(translatorFactory, whereBuilderFactory, compareFactory);
}Simply write your query with terms you are familiar with.
bool isValid = GetBuilder().GetSelect().From<Car>()
.SelectAll<Car>()
.Where(comparator => comparator.Compare<Car>(car => car.ModelYear).With(Operators.GT, "@year"))
.TryBuild(out string query);
//@year can later be replaced with your favorite libraryResulting SQL:
SELECT [Car].*
FROM [Car]
WHERE [CarMaker].[ModelYear] > @yearAn update query is just as simple.
bool isValid = GetBuilder().GetUpdate().From<Car>("CarTableAlias")
.Set(car => car.Mileage, "@mileage")
.Where(c => c.Compare<Car>(car => car.Mileage).With(Operators.LT, "0"))
.TryBuild(out string query); Resulting SQL:
UPDATE [CarTableAlias]
SET [CarTableAlias].[Mileage] = @mileage
FROM [Car] [CarTableAlias]
WHERE [CarTableAlias].[Mileage] < 0Note the use of an alias for the "Car" table.
An insert query is also straightforward.
bool isValid = GetBuilder().GetInsert()
.InsertInto<Car>(car => new {
car.Id,
car.ModelYear,
car.Mileage,
car.Price,
car.CarMakerId
}).Values("@id", "@modelYear", "@mileage", "@price", "@carMakerId")
.TryBuild(out string query); Resulting SQL:
INSERT INTO [CAR]
([Car].[Id], [Car].[ModelYear], [Car].[Mileage], [Car].[Price], [Car].[CarMakerId])
VALUES (@id, @modelYear, @mileage, @price, @carMakerId)By now you should expect the delete query to be just as simple. But here's a taste of more advanced features.
bool isValid = GetBuilder().GetDelete().DeleteFrom<Car>()
.Join<Car, CarMaker>(car => car.CarMakerId, carMaker => carMaker.Id)
.WhereFactory(f => f.Or(
f1 => f1.Compare(c => c.Compare<CarMaker>(m => m.FoundationDate).With(Operators.LT, new DateTime(1950, 01, 01).ToShortDateString())),
f2 => f2.Compare(c => c.Compare<Car>(car => car.Mileage).With(Operators.LTE, 50_000.ToString()))
));Resulting SQL:
DELETE FROM [Car]
JOIN [CarMaker] ON [Car].[CarMakerId] = [CarMaker].[Id]
WHERE ((([CarMaker].[FoundationDate]) < (1950-01-01)) OR (([Car].[Mileage]) <= (50000)))You can use table aliases if you want to join the same table multiple times.
const string TABLE1 = "MAKER1";
const string TABLE2 = "MAKER2";
var isValid = GetBuilder().GetSelect().From<CarMaker>(TABLE1)
.Join<CarMaker, CarMaker>(maker1 => maker1.CountryOfOriginId, maker2 => maker2.CountryOfOriginId, TABLE1, TABLE2)
.SelectAll<CarMaker>(TABLE1)
.Where(comparator => comparator.Compare<CarMaker>(maker1 => maker1.Id, TABLE1).With<CarMaker>(Operators.NEQ, maker2 => maker2.Id, TABLE2))
.TryBuild(out string query);Resulting SQL:
SELECT [MAKER1].*
FROM [CarMaker] AS [MAKER1]
JOIN [CarMaker] AS [MAKER2] ON [Maker1].[CountryOfOriginId] = [Maker2].[CountryOfOriginId]
WHERE [Maker1].[Id] <> [Maker2].IdHere is a more complex query. Note the use of a complex selector (average aggregate).
var isValid = GetBuilder().GetSelect().From<Car>()
.Join<Car, CarMaker>(car => car.CarMakerId, maker => maker.Id)
.Select<CarMaker>(maker => maker.Name)
.Select<Car>(car => car.ModelYear)
.SelectAs(new Aggregate(AggregateFunctions.AVG).Select<Car>(car => car.Price), "AveragePrice")
.GroupBy<Car>(car => car.ModelYear)
.GroupBy<CarMaker>(maker => maker.Name)
.TryBuild(out string query);Resulting SQL:
SELECT AVG([Car].[Price]) AS [AveragePrice], [CarMaker].[Name], [Car].[ModelYear]
FROM [Car]
JOIN [CarMaker] ON [Car].[CarMakerId] = [CarMaker].[Id]
GROUP BY [Car].[ModelYear], [CarMaker].[Name]A complex selector is described as a class. Simply inherit SelectBuilder to create your own easily. The generic type <T> will enforce (or not using object) a specific type on your special selector. For example, here is a "DATEDIFF" implementation that requires the selector to be of a DateTime type.
// definition
public class DateDiff : SelectBuilder<DateTime>
{
private readonly DateDiffType type;
private readonly DateTime compareTo;
public DateDiff(DateDiffType type, DateTime compareTo)
{
this.type = type;
this.compareTo = compareTo;
}
protected override string BuildSelectClause(string column)
{
return $"DATEDIFF({type.ToString()}, '{compareTo.ToString("yyyy-MM-dd")}', {column})";
}
}
// (...)
// usage
new DateDiff(DateDiffType.YEAR, new DateTime(2018,1,1))
.Select<CarMaker>(maker => maker.FoundationDate);Resulting SQL:
DATEDIFF(YEAR, '2018-01-01', [CarMaker].[FoundationDate])People's car tastes can be all over the place, and so can be your "WHERE" clauses! Here are some "WHERE" conditions extracted as functions so we can use them later.
private IWhereBuilder CheapCarCondition(IWhereBuilderFactory factory)
{
return factory.And(
f => f.Compare(c => c.Compare<Car>(car => car.Mileage).With(Compare.LT, "@cheap_mileage")),
f => f.Compare(c => c.Compare<Car>(car => car.Price).With(Compare.LT, "@cheap_price")),
f => f.Compare(c => c.Compare<CarMaker>(maker => => maker.Name).With(Compare.NEQ, "@cheap_name")),
f => f.Compare(c => c.Compare<Country>(country => country.Name).With(Compare.NEQ, "@cheap_country"))
);
}
private IWhereBuilder DreamCarExceptionCondition(IWhereBuilderFactory factory)
{
return factory.And(
f => f.Compare(c => c.Compare<Car>(car => car.Mileage).With(Compare.LT, "@dream_mileage")),
f => f.Compare(c => c.Compare<Car>(car => car.Price).With(Compare.LT, "@dream_price")),
f => f.Compare(c => c.Compare<CarMaker>(maker => maker.Name).With(Compare.EQ, "@dream_maker"))
);
}The conditions above are assembled with a "OR" to create our very specific query! Also, notice the anonymous object used inside the select function.
var isValid = GetBuilder().GetSelect().From<Car>()
.Join<Car, CarMaker>(car => car.CarMakerId, maker => maker.Id)
.Join<CarMaker, Country>(maker => maker.CountryOfOriginId, country => country.Id)
.Select<Car>(car => new { car.Id, car.Price })
.WhereFactory(factory => factory.Or(
CheapCarCondition,
DreamCarExceptionCondition
))
.OrderBy<Car>(car => car.Price, desc: true)
.TryBuild(out string query);Resulting SQL:
SELECT [Car].[Id], [Car].[Price]
FROM [Car]
JOIN [CarMaker] ON [Car].[CarMakerId] = [CarMaker].[Id]
JOIN [Country] ON [CarMaker].[CountryOfOriginId] = [Country].[Id]
WHERE (
((([Car].[Mileage]) < (@cheap_mileage)) AND
(([Car].[Price]) < (@cheap_price)) AND
(([CarMaker].[Name]) <> (@cheap_maker)) AND
(([Country].[Name]) <> (@cheap_country))
) OR (
(([Car].[Mileage]) < (@dream_mileage)) AND
(([Car].[Price]) < (@dream_price)) AND
(([CarMaker].[Name]) = (@dream_maker)))
)
ORDER BY [Car].[Price] DESC