Yes, I have to apologize (again) for the lack of good documentation or examples. I rarely find or make the time to document this project (or really any of my many projects). typically it's becasue I'm pretty busy using them. I'll see what
I can do to create some documentation for your specific instances, but in the meantime, I can at least give some guidance.
1. For Select with filter conditions you have a few options. If the expected unfiltered result set is small, you can pull everything and then use LINQ to filter (since the CF doesn't support Expressions, we can't do much to help out there). There
is also the FilterCondition array that can be passed in. Play with that to get the ORM to generate SQL for the query for you. It's more limited than what you can get writing full-up SQL, but it works for a lot of things.
2. You can execute direct SQL, but only as an "ExecuteNonQuery" or "ExecuteScalar". There's no way (currently) for the ORM to execute an SQL statement and return the results as typed objects (or any object for that matter - the point of the ORM is
to hide the implementation of the store). Largely the ORM can't convert the result of a SQL statement becasue it has no simple way to map the results to entities. I could do some work on that end, but it would be a rats nest I think. What
if you only select a subset of fields? What if you use Joins?
3. It depends on your JOINs. If it's simple 1:n references, then the Reference fields do that. Take a look at some of the sample/test code covering references. If it's mroe complex, then I'd need a better idea of what you're after.
Generally speaking, if you're using an ORM (any ORM, not just this one), you need to break free of the mindset of "joins" and SQL in general. You're dealing with entities/objects and instances. Not tables and rows.