Composure

Composure is a simple SQL-authoring library for .NET Core. Composure allows for statically-typed SQL-like query syntax directly in C#, with minimal messy string manipulation. Composure is designed for readability, clarity, and testability, and was developed for the Leaf Clinical Data Explorer app at the University of Washington by @ndobb and @cspital.

Note: If you are able to create and use Stored Procedures, Views, and other SQL objects, please read no further and do so!

In addition to certain performance gains and so on, precompiled SQL drastically reduces the risk of SQL injection and other security concerns related to dynamically creating queries. It also allows separation of database and app code, often making for cleaner, more maintainable code bases.

Also note that Composure is not an object-relational-mapper, and doesn't handle SQL connections or execute queries itself. Libraries such as Dapper do a great job of that already.

Rather, Composure allows you to reason about complex SQL queries in an object-oriented, visually intuition fashion using a simple domain-specific-language and operators. Let's get started!

Installation

Load from Nuget via the dotnet CLI:

$ dotnet add package Composure --version 1.1.0

or build locally:

$ git clone https://github.com/uwrit/composure.git
$ cd composure/src/Composure
$ dotnet build -c Release
# Outputs dll to /bin/Release/netcoreapp2.2/

• Basic SELECT from a table/view
• Nested WHERE conditions
• Basic JOIN
• JOIN and GROUP BY
• UNION and wrap as subquery
• CASE WHEN statements
• Using inheritance for predefined sets and intellisense
• Syntax cheat-sheet
• Caveats

Basic SELECT from a table/view

// Columnsvarname=newColumn("Name");varcategory=newColumn("Category");vardeliciousness=newColumn("Deliciousness");// Get queryvarquery=newNamedSet{Select=new[]{name,category,deliciousness},From="dbo.Food",Where=new[]{deliciousness>3,category=="fruit"}};query.ToString();

Returns:

SELECT Name
, Category
, Deliciousness FROMdbo.FoodWHERE Deliciousness >3AND Category ='fruit'

And that's it! Note that these examples show formatted SQL only for readability. Composure itself does not beautify SQL.

Let's try a more interesting example.

Nested WHERE conditions

// WHERE clause conditionsvarisDelicious=deliciousness>3;varisFruit=category=="fruit";// Get queryvarquery=newNamedSet{Select=new[]{name,category,deliciousness},From="dbo.Food",Where=new[]{(isDelicious&!isFruit)|isFruit}};query.ToString();

Returns:

SELECT Name
, Category
, Deliciousness
FROMdbo.FoodWHERE (
(Deliciousness >3AND NOT (Category ='fruit')) OR
Category ='fruit'
)

At this point if you are thinking the above is readable and clear, great! If however the above syntax looks like voodoo, that's okay too! Composure is strongly typed, and leverages operator overloading to allow for concise, simple code, that compiles to plain ol' SQL.

Note that we could have just as easily written the above as:

varisDelicious=newColumnEval(deliciousness,EvaluationType.GreaterThan,newExpression(3));varisFruit=newColumnEval(category,EvaluationType.Equal,newQuotedExpression("fruit"));// Get queryvarquery=newNamedSet{Select=newISelectable[]{name,category,deliciousness},From=newRawSet("dbo.Food"),Where=newIEvaluatable[]{newOrEval(newAndEval(isDelicious,newNotEval(isFruit)),isFruit)}};

...and the resulting SQL would have been identical. Composure supports both the shorthand and longhand query syntax, so choose the style that works best for you.

Skip to the Syntax cheat-sheet below for a quick reference.

Basic JOIN

// Setsvarset1=newRawSet("dbo.Food");varset2=newRawSet("dbo.Category");// Joinsvarj1=newJoin{Set=set1,Alias="F"};varj2=newJoin{Set=set2,Alias="C",Type=JoinType.Inner,On=new[]{newColumn("CategoryId",j1)==newColumn("CategoryId")}};// Columns with Sets specifiedvarname=newColumn("Name",j1);vardeliciousness=newColumn("Deliciousness",j1);varcategoryName=newColumn("CategoryName",j2);// Get queryvarquery=newJoinedSet{Select=new[]{name,deliciousness,categoryName},From=new[]{j1,j2},OrderBy=new[]{categoryName,name}};query.ToString();

Returns:

SELECTF.Name
, F.Deliciousness
, C.CategoryNameFROMdbo.FoodAS F INNER JOINdbo.CategoryAS C ONF.CategoryId=C.CategoryIdORDER BYC.CategoryName
, F.Name

JOIN and GROUP BY

// Setsvarset1=newRawSet("dbo.Food");varset2=newRawSet("dbo.Category");// Joinsvarj1=newJoin{Set=set1,Alias="F"};varj2=newJoin{Set=set2,Alias="C",Type=JoinType.Inner,On=new[]{newColumn("CategoryId",j1)==newColumn("CategoryId")}};// Columns with Sets specifiedvarcategoryId=newColumn("CategoryId",j2);varcategoryName=newColumn("CategoryName",j2);vardeliciousness=newColumn("Deliciousness",j1);// Aggregation expressionsvarcalcMaxDeliciousness=newExpression($"MAX({deliciousness})");varcalcTotalCount=newExpression("COUNT(*)");// Aggregate columnsvartotalCount=newExpressedColumn("TotalCount",calcTotalCount);varmaxDeliciousness=newExpressedColumn("MaxDeliciousness",calcMaxDeliciousness);// Get queryvarquery=newJoinedSet{Select=new[]{categoryId,categoryName,totalCount,maxDeliciousness},From=new[]{j1,j2},Where=new[]{deliciousness>3},GroupBy=new[]{categoryId,categoryName},Having=new[]{calcMaxDeliciousness>=5},OrderBy=new[]{categoryName}};query.ToString();

Returns:

SELECTC.CategoryId
, C.CategoryName
, COUNT(*) AS TotalCount
, MAX(F.Deliciousness) AS MaxDeliciousness FROMdbo.FoodAS F INNER JOINdbo.CategoryAS C ONF.CategoryId=C.CategoryIdWHEREF.Deliciousness>3GROUP BYC.CategoryId , C.CategoryNameHAVINGMAX(F.Deliciousness) >=5ORDER BYC.CategoryName

UNION and wrap as subquery

// ColumnsvarallColumns=new[]{"Name","Category","Deliciousness"};// Reusable function to get columns for each setvargetColumns()=>allColumns.Select(c =>newColumn(c)).ToArray();// Setsvarset1=newNamedSet{Select=getColumns(),From="dbo.Food",Alias="F"};varset2=newNamedSet{Select=getColumns(),From="dbo.Beverage",Alias="B"};// Unionvarunion=newUnionedSet{set1,set2};// Get wrapper queryvarwrapper=newVirtualSet{Select=getColumns(),From=union,Alias="W"};wrapper.ToString();

Returns:

SELECTW.Name
, W.Category
, W.DeliciousnessFROM (SELECTF.Name
, F.Category
, F.DeliciousnessFROMdbo.FoodAS F UNIONSELECTB.Name
, B.Category
, B.DeliciousnessFROMdbo.BeverageAS B) AS W

CASE WHEN statements

// Columnsvarname=newColumn("Name");varcategory=newColumn("Category");vardeliciousness=newColumn("Deliciousness");// CasesvarisDelicious=deliciousness>3;varisFruit=category=="fruit";varisVeggie=category=="vegetable";// Case whenvarfoodCases=newCaseWhen{Cases=new[]{isFruit|"It's a fruit",isDelicious&isVeggie|"It's delicious and a vegetable",isVeggie|"It's a vegetable, but not delicious",isDelicious|"It's something else delicious"},Else=newQuotedExpression("It's something else and not delicious!")};// Get queryvarquery=newNamedSet{Select=new[]{name,newExpressedColumn("FoodCases",foodCases)},From="dbo.Food",};query.ToString();

Returns:

SELECT Name
, Category
, CASE WHEN Category ='fruit' THEN 'It''s a fruit' WHEN (Deliciousness >3AND Category ='vegetable') THEN 'It''s delicious and a vegetable' WHEN Category ='vegetable' THEN 'It''s a vegetable, but not delicious' WHEN Deliciousness >3 THEN 'It''s something else delicious' ELSE 'It''s something else and not delicious!' END AS FoodCases FROMdbo.Food

Using inheritance for predefined sets and intellisense

publicclassFoodsAndCategoriesSet:JoinedSet{publicreadonlyColumnFoodName;publicreadonlyColumnCategoryId;publicreadonlyColumnCategoryName;publicreadonlyColumnDeliciousness;// Predefine JOINs on initializationpublicFoodsAndCategoriesSet(){// Setsvarfoods=newRawSet("dbo.Food");varcategories=newRawSet("dbo.Category");// Joinsvarj1=newJoin{Set=foods,Alias="F"};varj2=newJoin{Set=categories,Alias="C",Type=JoinType.Left,On=new[]{newColumn("CategoryId",j1)==newColumn("CategoryId")}};// ColumnsFoodName=newColumn("Name",j1);CategoryId=newColumn("CategoryId",j2);CategoryName=newColumn("CategoryName",j2);Deliciousness=newColumn("Deliciousness",j1);// Final joined SetsFrom=new[]{j1,j2};}}

The joined FoodsAndCategoriesSet is now conveniently predefined and wrapped in class, so its columns can be used as statically-typed properties with full intellisense support.

// Initialize joined setvarq=newFoodsAndCategoriesSet();q.Select=new[]{q.FoodName,q.CategoryName,q.Deliciousness};q.Where=new[]{q.Deliciousness>3,q.CategoryName==new[]{"vegetable","fruit"}};q.OrderBy=new[]{q.CategoryName};q.ToString();

Returns:

SELECTF.Name
, C.CategoryName
, F.DeliciousnessFROMdbo.FoodAS F LEFT JOINdbo.CategoryAS C ONF.CategoryId=C.CategoryIdWHEREF.Deliciousness>3ANDC.CategoryNameIN ('vegetable', 'fruit' ) ORDER BYC.CategoryName

Syntax cheat-sheet

deliciousness>3// Deliciousness > 3
deliciousness ==3&5// Deliciousness BETWEEN 3 AND 5!(deliciousness==2)// NOT (Deliciousness = 2)
name =="apple"&category=="fruit"// (Name = 'apple' AND Category = 'fruit')
name ==new[]{"apple","banana"}// Name IN ('apple', 'banana')
name !=new[]{"hotdog","sauce"}// Name NOT IN ('hotdog', 'sauce') newCaseWhen{Cases=new[]{// CASE deliciousness>5|"Super delicious",// WHEN Deliciousness > 5 THEN 'Super delicious'deliciousness<=4|"So so"// WHEN Deliciousness <= 4 THEN 'So so'},//Else=newQuotedExpression("Not yummy")// ELSE 'Not yummy' }// END