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: github.com/ServiceStack/ServiceStack.OrmLite — A T
– A T, Commented May 15, 2012 at 5:36

Community · Accepted Answer · 2017-05-23 12:20:17Z

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, 2017 at 12:20

CommunityBot

11 silver badge

answered May 15, 2012 at 2:49

mythz

144k31 gold badges253 silver badges400 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

A T Over a year ago

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?

mythz Over a year ago

What would you like the API to look like?

A T Over a year ago

Thanks for asking. I recommend: this sort of syntax.

mythz Over a year ago

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.

A T Over a year ago

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?

|

Collectives™ on Stack Overflow

OrmLite with nested select queries

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related