Insight.Database One to Many Mapping from a JOIN using Auto Interface

Question

Insight.Database supports mapping One to Many results if the SQL has multiple SELECT statements, one for Parent and the for the children. See: https://github.com/jonwagner/Insight.Database/wiki/Specifying-Result-Structures

CREATE PROC GetBeerAndPossibleGlasses AS
SELECT * FROM Beer
SELECT b.BeerID, g.* FROM Beer JOIN Glasses ON (...)

class Beer
{
    public int ID;
    public IList<Glass> Glasses;
}

var results = connection.Query("GetBeerAndPossibleGlasses", Parameters.Empty,
    Query.Returns(Some<Beer>.Records)
        .ThenChildren(Some<Glasses>.Records);

Can the one-to-many mapping be done using a single join query? I believe this is supported in Petapoco. (I am using Auto Interface implementation but I would like to know if there is any way at all to accomplish it.) e.g.

CREATE PROC GetBeerAndPossibleGlasses AS    
SELECT b.*, g.* FROM Beer JOIN Glasses ON (...)

I have relatively complex query where I need to return parent and children as above. It works if I create multiple SELECT statments but my select statement look like below:

SELECT * FROM Beer WHERE <Complex WHERE clause>
SELECT b.BeerID, g.* FROM Beer JOIN Glasses ON (...) <Complex WHERE clause>

I am worried that the query is as efficient as a single join.

Answer 1

It looks like you want to return a single recordset like this:

Beer=1, Glass=A
Beer=1, Glass=B

And convert it to these objects:

Beer {
    ID=1
    Glasses = {
         Glass { ID=A }
         Glass { ID=B }
    }
}

Insight would need to de-duplicate the beers and wrap up the glasses into the parents.

That scenario isn't currently supported, but it wouldn't be too difficult to add it as a new type of recordreader (and then bind it to an interface implementation).

Returning the data in two recordsets may or may not be better/faster, depending on your use case. Two recordsets may scan the indexes again, but they are likely in memory, and could be faster than duplicating the data in each child record. The only way to find out is to profile your data.

If you find that you still need to return the data in a single recordset, please open a feature request over at the github site, and we'll look into implementing it.