Feb 02, 2014
Menashay, great questions:
1. Extending the Insert statement would not be appropriate. However, you can build a custom string in which several SQL statements are concatenated (separated by a semicolon) and issue that as a separate query; for example, some inserts followed by a select statement. The last select statement will determine what you will get back.
However, it seems to me that with triggers in the middle such a transaction gets a bit hairy. My personal preference would be to issue an insert (or multiple concatenated inserts) and then an extra select query. Indeed, as a general rule you want to minimize the number of trips to the database, but if the code gets too convoluted I would choose to issue an extra statement.
2. The technique you are using is called 'soft deletes'. Of course in each query you could add the WHERE clause 'Active = true', but that gets a bit cumbersome. If you wanted to you can implement 'soft deletes' into Core.cs. This would be in the Domain Layer section inside the CreateSelect, and CreatePaged methods (there could be more, I have not fully researched this). .
3. When issuing M-M selects I would build a dynamic SQL statement and execute it with the Query method.
Hope this helps,