This review will focus more on the API that results from the design. The over-all approach is one which uses some fluent interface elements in conjunction with composition in order to create a very flexible means of creating an domain model for sql queries. Warning: yes, it's verbose, but in this case I believe verbose is desirable because it make the intention of the code crystal clear.
There currently isn't a plan to add a lot of validation to this model. The assumption is that the consumer understands SQL and will be responsible for the query they construct.
Select Example:
1: Select.Query
2: (
3: Table.From("Table1").As("T1") 4: .Join
5: (
6: Table.Join("Table2").As("T2").On(Criterion.Column("Column1", "T1").EqualTo((Reference)"T2.Column2"), 7: Table.Join("Table3").As("T3".On(Criterion.Column("Column3", "T1").Between((Reference)"T3.Column2").And((Reference)"T3.Column3") 8: ),
9: Select.Column("Column1").OwnedBy("T1").As("Alias1") + 10: Select.Column("Column2").OwnedBy("T1").As("Alias2") + 11: Select.Column("Column3").OwnedBy("T2").As("Alias3") + 12: Select.Column("Column3").OwnedBy("T3").As("Alias4), 13: Where.Criteria(Criterion.Column("Column2", "T1").Like((Literal)"Patter%"), 14: Page.PageSize(10).PageIndex(1),
15: Order.By().Ascending("T1.Column1") 16: );
Yeah, I know, very un-imaginative column and table names, right? But hopefully the intent of this code is very clear. A couple things should be pointed out that I'm not particularly happy about. The operator overload that concatenates columns. Yes, it's not intuitive, but I wanted a simple way to chain elements together and there it is.
The use of explicit type casts to Reference (lines 6 & 7) and Literal (line 13) are important because this is going to get parameterized and anything cast as a Literal type will be replaced with a parameter while a Reference value stays in the resulting SQL as is.
Derived queries, grouping constructs, functions, and table variables are also supported. Insert, update and delete operations are also a part of this stack and have very similar syntax and usages. Keep in mind that the primary use case for this is for other parts of Nvigorate to generate queries for the ORM. Supporting ad-hoc query capability for the end user is a bonus. It's to address the problem space that LINQ to SQL was trying to address (granted, more concisely) but with fewer limitations.
LINQ to SQL, in my opinion, is a neat idea that encourages really bad design. First off, it promotes 2 tier solutions which is great for hobby sites but generally bad for professional solutions. Secondly, Expression trees are a bad medium for expressing something like a T-SQL query which means that often-times, complex queries in LINQ-to-SQL result in horrendous results which no sane developer would ever intentionally implement. Thirdly, expression trees are immutable which makes them difficult to work with once they're created and pretty much impossible to serialize across a boundary. With the query stack above, all these concerns are addressed. You can still have a multi-tiered application that can also support ad-hoc queries in the client tier which will generally be more expressive representations of what will actually be executed against the database.
Let me provide you with a more involved example which demonstrates more of the domain model. This will demonstrate the table variable, function and derived query syntax. Also note that multiple query types chain together with an operator overload to create a batch.
1: var batch =
2: new TableVariable
3: (
4: Table.Variable("Employees"), 5: TableVariable.Column("EmployId").OwnedBy("Employees").Type(typeof(string)) + 6: TableVariable.Column("ManagerId").OwnedBy("Employees").Type(typeof(string)) + 7: TableVariable.Column("CustomerId").OwnedBy("Employees").Type(typeof(string)) + 8: TableVariable.Column("RoleId").OwnedBy("Employees").Type(typeof(int)) + 9: TableVariable.Column("DirectReports").OwnedBy("Employees").Type(typeof(int)) + 10: TableVariable.Column("CompletedEnrollment").OwnedBy("Employees").Type(typeof(bool)) 11: ) +
12: Insert.Query
13: (
14: new Table("@Employees"), 15: Insert.Column("EmployId").OwnedBy("Employees") + 16: Insert.Column("ManagerId").OwnedBy("Employees") + 17: Insert.Column("CustomerId").OwnedBy("Employees") + 18: Insert.Column("RoleId").OwnedBy("Employees") + 19: Insert.Column("DirectReports").OwnedBy("Employees) + 20: Insert.Column("CompletedEnrollment").OwnedBy("Employees"), 21: Select.Query
22: (
23: Table.From("Employees"), 24: Select.Column("EmployId").OwnedBy("Employees") + 25: Select.Column("ManagerId").OwnedBy("Employees") + 26: Select.Column("CustomerId").OwnedBy("Employees") + 27: Select.Column("RoleId").OwnedBy("Employees") + 28: Select.Column("CompletedEnrollment").OwnedBy("Employees") + 29: Function.UDF("udf_GetReportsForEmployeeId", (Reference) "Employees.EmployId") 30: Where.Criteria(
31: Criterion.Column("ManagerId", "Employees").EqualTo((Literal) DBNull.Value) & 32: Criterion.Column("RoleId", "Employees").EqualTo((Literal) 3)) 33: )
34: ) +
35: Select.Query
36: (
37: Table.From("@Employees"), 38: Select.Column("*") 39: );
I'd be interested in finding out what people think about this syntax in general. Soon I'll cover the visitor patterns which transform and create the parameterized sql from this object model.
Tags: