SelectTransformBuilder doesn't handle subqueries. Cannot make any kind of subquery projection

[rwasef1830]

Given this class:

class User 
{
    public Guid Id { get; set; }
    public List<Guid> RoleIds { get; set; }
}

var session = store.QuerySession();
session.Query<User>(x => new { x.Id, FirstRoleId = x.RoleIds.FirstOrDefault() });

This query does not work and throws an exception, it appears to be only expecting sub-member expressions from the RoleIds collection which doesn't work because it's a collection of scalar values.

PR with failing unit test coming.

[rwasef1830]

Ok, so I have this entirely crazy idea of synthesizing a subquery with primary key join and running the entire expression tree processing for the subquery. It might work but perhaps not in all cases... hmmm...

aka, generating a query like this:

SELECT
       d.data->>'Id' AS Id,
       (SELECT id.data->>'RoleIds' FROM mt_doc_user AS id WHERE id.id = d.id LIMIT 1) AS RoleId
FROM mt_doc_user AS d

Perhaps in a future release, some subquery collection operations could be translated to direct jsonb functions and eliminate the self-table join, but for now getting it to work in the first place is going to go a long way.

[rwasef1830]

This is closer to what Marten generates:

SELECT
       jsonb_build_object(
            'Id', d.data->>'Id',
            'RoleId', (SELECT id.data->'RoleIds'->>0 FROM mt_doc_user AS id WHERE id.id = d.id LIMIT 1)
       )
FROM mt_doc_user AS d

[rwasef1830]

This needs a refactor to LinqHandlerBuilder to extract out a class that builds Sql and takes a processed model directly.
Then another refactor is needed to SelectTransformBuilder.TargetObject so that it can accept correlated subquery sql generating type.

[rwasef1830]

This will be a tiny bit easier to do after #1927 is merged.

[jeremydmiller]

It's not a bug per se, just something that's never been implemented.

[jeremydmiller]

Beating this for now w/ the ability to use array indexers and calling that good enough.

[jeremydmiller]

I'm closing this one. We're not going any farther for now.