SQL syntax for OneToMany mapping

[ThisNoName]

The documentation says something like this:

SELECT * FROM Beer
SELECT b.BeerID, g.* FROM Beer JOIN Glasses

Is this the only acceptable query format? I think most our SQL joins are written like this:

SELECT b.*, g.* FROM Beer, Glasses

I can see the former can be more efficient in some situations, but we often can't change DB procs unless it's a green project. Any suggestions? Thanks.

[jonwagner]

The query itself doesn't matter. Insight just parses the result set, looking at the columns.

The most important part is to make sure that columns from a table are consecutive. Otherwise, you'll have to do some manual mapping.

[ThisNoName]

Thanks for the quick follow up. Not sure if I missed anything important. I have these records and classes

SELECT a.*, b.* FROM beer a LEFT JOIN glass b ON A.id=b.beerid
id  NAME    beerid  id  size
1   IPA 1   1   small
1   IPA 1   2   medium
2   Lager   2   3   large
2   Lager   2   4   larger

public  class Beer {
    [RecordId] public int Id { get; set; }
    public string Name { get; set; }
    [ChildRecords] public IList<Glass> Glasses { get; set; } }

public class Glass {
    [ParentRecordId] public  int BeerId { get; set; }
    [RecordId] public int Id { get; set; }
    public string Size { get; set; } }

In the following test, 1&3 use query from the wiki, 2&4 use typical SQL select/join

• result1 is normal 2 beers x 2 glasses each;
• result2 returns 4 records and only map beer property
• result3 returns 2 beers, but no glasses. I thought the [ChildRecords] [ParentRecordId] would be equivalent to those Some ... Then Children?
• result4 returns 4 flat list

Seems the only way to do one to many mapping is if there're two separate queries, one return Beer and another return Beer.Id+Glass? I tried several other combos and the auto interface hint, etc., but only the exact SQL from the wiki works.

var results1 = conn.QuerySql("select * from beer; select a.id, b.* from beer a left join glass b on a.id=b.beerid", 
    Parameters.Empty, Query.Returns(Some<Beer>.Records).ThenChildren(Some<Glass>.Records));

var result2 = conn.QuerySql("select a.*, b.* from beer a left join glass b on a.id=b.beerid", Parameters.Empty,
    Query.Returns(Some<Beer>.Records).ThenChildren(Some<Glass>.Records));

var result3 = conn.QuerySql<Beer>("select * from beer; select a.id, b.* from beer a left join glass b on a.id=b.beerid");

var result4 = conn.QuerySql<Beer>("select a.*, b.* from beer a left join glass b on a.id=b.beerid");

[jonwagner]

It looks like you're trying to do a One-to-One mapping.

See: https://github.com/jonwagner/Insight.Database/wiki/Specifying-Result-Structures#one-to-one-relationships

[ThisNoName]

I'm trying the one-to-many section from that very same documentation as is. But it seems only work if there are two separate queries in the proc.

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

Not when I have other common type of SQL query like

 SELECT a.*, b.* FROM Beer a LEFT JOIN GLasses b ...

Also, the syntax used in the auto interface variant [Recordset(1, typeof(Glass) ...] seems to imply one-to-many is expecting two separate queries from the proc.

public interface IHaveStructure
{
    [Recordset(1, typeof(Glass), ID="ID", GroupBy="BeerID", Into="Glasses")]
    IList<Beer>GetBeerAndChildren();
}

Is that the only way to construct query for Insight one-to-many mapping?

[Jaxelr]

The API of one-to-many mapping does require that you use 2 result sets to convert into the Parent/Child structure. But Insight is flexible enough to allow you to implement your own that will comfort into your result set. Please check this part of the Wiki, which might be useful: https://github.com/jonwagner/Insight.Database/wiki/Record-Readers