OrmLite with nested select queries

Question

I have generalised my problem in order to cater to the largest number of people with similar issues.

public class Table1 {
    [AutoIncrement]
    public Int32 Id { get; set; }
    [Index(Unique = true)]
    public string FieldA { get; set; }
    public string FieldB { get; set; }
    public string FieldC { get; set; }
}

public Table2 {
    [AutoIncrement]
    public Int32 Id { get; set; }
    [Index(Unique = true)]
    public Table1 FieldA { get; set; }
    public DateTime FieldB { get; set; }
    public int FieldC { get; set; }
}

public Table3 {
    [AutoIncrement]
    public Int32 Id { get; set; }
    [Index(Unique = true)]
    public List<Table2> FieldA { get; set; }
    public List<Table1> FieldB { get; set; }
}

public Table4 {
    [AutoIncrement]
    public Int32 Id { get; set; }
    [Index(Unique = true)]
    public int FieldA { get; set; }
    [References(typeof(Table3))]
    public int Table3_id { get; set; }
    [References(typeof(Table2))]
    public int Table2_id { get; set; }
}

How can I populate a List containing the complete information of Table4? I.e.: including the value of each field of its referenced tables—and the referenced tables of those

I would also be interested in how I could create a single CRUD form—from the JSON serialisation—which can create an entirely new Table4 entry nesting the CRUD forms for Table3, Table2 and Table1.

Thanks for all help

hi Gray, OrmLite is also a C# ORM library: https://github.com/ServiceStack/ServiceStack.OrmLite — A T, May 15 '12 at 05:36

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

To read from multiple tables in 1 query you need to use an SQL JOIN that's mapped to a Merged Poco which matches the returned result-set, see the Shipper Examples in this answer for an example:

https://stackoverflow.com/a/8617860/85785

edited May 23 '17 at 12:20

Community

1
1

answered May 15 '12 at 02:49

mythz

141,670
29
246
390

So neither nested select queries nor joins are supported by OrmLite? - I.e.: I would need to write pure SQL? - Any chance you can add support for either of these methods? – A T May 15 '12 at 03:31
What would you like the API to look like? – mythz May 15 '12 at 04:01
Thanks for asking. I recommend: [this](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join) sort of syntax. – A T May 15 '12 at 04:05
And what do you expect to get back? OrmLite is a .NET 3.5 (i.e. no dynamic) that works on strong-typed POCOs, by their nature JOINs deviate from the shape of the POCOs - so there is no existing shape you can hydrate into. Hence why the current solution looks like it does. – mythz May 15 '12 at 04:34
Actually, I wouldn't need a join. Nested select queries would work fine. Even if I could store a list containing Table4 and everything inside it unrolled, I would then be able to use some fancy javascript on the frontend to CRUD any entry at any layer of the schema. So how can I populate this `List`? – A T May 15 '12 at 05:38
You have to use SQL for nested queries (or any other query requiring more than 1 table). – mythz May 15 '12 at 06:32
Well would it be possible to generalise this to a set without dynamically creating POCOs? - I.e. just a list of lists – A T May 15 '12 at 12:50
Great, send me a pull request for it :) – mythz May 15 '12 at 15:17

OrmLite with nested select queries

1 Answers1