How can I map custom sql queries to CSObjects in cool storage?

Question

I want to use complicated sql queries to get lists of objects while using Vici.CoolStorage. Itself it provides a specific query syntax, but it is not powerful enough for my needs. On the website there is an example of how to map a custom query result to a custom object but what I want is to get a list (CSList) of CSObject descendants, same as I get with CSObject.List () method.

Answer 1

This is possible as CSObjects are mapped to tables. (Being abstract classes and using reflection etc...)

If you want to be able to read related objects, you can define properties that lazy load these records. E.g.:

public class MyCustomQueryResult
{
    public int SomeId;
    public string SomeStringField;
    public int SomeScalar;

    public CSList<MappedObject> MappedObjects
    {
        get { return MappedObject.List("SomeId = @SomeId", "@SomeId", SomeId); }
    }
}

And then you could use it like this:

string sqlQuery = "SELECT a.SomeId, b.SomeString, COUNT(*) AS SomeScalar"
                + "FROM tblA a"
                + "INNER JOIN tblB b ON a.SoneId = b.SomeId"
                + "GROUP BY a.SomeId, b.SomeString"
                + "WHERE b.SomeField = @SomeParameter";

MyCustomQueryResult[] entries = CSDatabase.RunQuery<MyCustomQueryResult>(sqlQuery, new {SomeParameter:"123456"});

foreach (MyCustomQueryResult entry in entries)
{
   foreach (MappedObject mappedObject in entry.MappedObjects)
   {
       DoSomethingUseful(mappedObject);
   }
}