3 ms·
>The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic access to the SQL AST, so you can ge
by snarfy 12y ago
>The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic access to the SQL AST, so you can generate syntax as opposed to concatenate strings together. Kind of like a DOM API, but for SQL.
What you described is basically EF+LINQ.
var query = context.Users.Include("Users.Group").Where( u => u.UserType
== UserTypes.Basic).Select( u => u.Group);
That's going to spit out a SQL statement that joins Users to Group and selects all groups where the UserType is UserTypes.Basic. You can even do ToTraceString() to see the generated statement.
- taspeotis 12y ago> You can even do ToTraceString() to see the generated statement. And then if you're a control freak, feed that generated query into ScriptDom [1]. [1] http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/microsoft.data.schem...
- virtualwhys 12y agoThe same in Scala + Slick would be: val q = Users.filter(_.userType == UserTypes.Basic).map(_.group) Although I'm not sure what the context.Users.Include bit in the EF example is doing. Both will generate statement at compile time, and I assume EF queries are composable as is the case with Slick. Slick's readability does suffer though with more complex queries -- unfortunate they strayed away from SQL semantics in favor of Scala collections. We'll see where it goes...
- snarfy 12y agoInclude() on an ObjectContext will return the corresponding foreign key objects. It's a poor man's join to related entities. There is also Join(), but that will join by anything.
- virtualwhys 12y agoAh ok, I see. Slick actually does have a nice shorthand for fkey joins: val q = for{ ur <- UserRole; u <- ur.user; r <- ur.role } yield(ur,u,r) which would produce semantic equivalent to: select ur.id, ur.a, ..., u.id, u.a ... from userrole ur join user u on u.id = ur.userId join role r on r. id = ur.roleId grouping and sorting is where Slick becomes less readable SQL-like DSL and more boilerplate DSL full of meaningless tuples O_o