New Feature: Include()

[LorenDorez]

I come from a web background where i have bee using EntityFramework alot. I think a nice feature would be to have an .include() function.

Essentially. what it would do it load another Query/Table into a variable into the first query. This helps to create Nested objects where a table join just doesnt do the same things.

Example of what im using right now:

read: function () {
                if (global.isOnline()) {
                    return global.oversii.api.fetch.post("property")
                        .then((jsonData) => jsonData);
                }
                else {
                    return new Promise((resolve, reject) => {
                        db.context.all("SELECT * FROM Property")
                            .then((properties) => {
                                for (let i = 0; i <= properties.length - 1; i++) {
                                    const property = properties[i];
                                    db.tables.violationWorkflowPeriod.getById(property.ViolationWorkflowPeriodId)
                                        .then((vwp) => {
                                            property.ViolationWorkflowPeriod = vwp;
                                            if (i === properties.length - 1) {
                                                resolve(properties);
                                            }
                                        })
                                        .catch((error) => db.error(error));
                                }
                            })
                            .catch((error) => db.error(error));
                    });
                }
            },

[NathanaelA]

I must be dense today; I'm still not following how/what a include would do. Can you point me to some documentation about the feature in an entity framework; or show me what it would cut out.

[LorenDorez]

It basically does loading of nested objects. Example if i had a Table for Person and another Table for Vehicle. Lets say Vehicle has a FK column PersonId that links to the PK on Person.

Currently if i so a SELECT * FROM Person P Inner Join Vehicle V on V.PersonId = P.PersonId i will get back a record that has Person and Vehicle on the same DBResult object. What the include would do would be a nested tree so you would have something like this

{
 PersonId: 1,
Name : "John Smith",
Vehicle : {
   VehicleId: 1,
   PersonId:1,
   Color: "Red"
}

I hope that make more sense. Here a link to https://msdn.microsoft.com/en-us/library/jj574232%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

[NathanaelA]

Ah, yeah -- That makes a lot more sense! Thanks.

However, since I don't do any SQL rewriting in NS-Sqlite; I'm not sure it would ever work. However, maybe I need to add a Entity framework wrapper that can be used to do this type of stuff. That is something that could easily generate SQL needed so you are doing something like Select("blah").From("table").include("anotherTable")....

[LorenDorez]

I was thinking of writing an extension method that would take essentially do this. You have to pass in an options object to tell the extension method how the queries are related( ie what columns) and then where to put them on the base object.

I think it will be good for joins. Also anyone coming from APIs which return nested Is on. This is where the issue came from as I have an offline option in my app to store the api call results