Select<T>(x => x.ExplicitSql(text)) transform in Select statements

[rwasef1830]

In some cases it maybe desirable to express a projection to a property that Marten's LINQ provider is unable to parse (yet).
In such case it would be so nice if we could have something like

session.Query<User>(x => new 
{ 
    Id = x.Id, 
    FirstRoleId = Projection.For<Guid>("d.data->'RoleIds'->>0") 
});

or maybe

session.Query<User>(x => new 
{ 
    Id = x.Id, 
    FirstRoleId = x.SqlProjection<Guid>("d.data->'RoleIds'->>0") 
});

A certain convention will need to be established and documented perhaps for inner subqueries or related document fetches (where the "d" alias would perhaps differ).

[rwasef1830]

There seems to be a deep assumption in Marten that raw sql can only be used for Where clauses or full queries only. This needs to be changed (surgery).

[davidjamesb]

Something like this would be a very useful addition. I currently need to use 'lag' and 'lead' windowing functions and currently it seems my only option is to pass a SQL string verbatim.

[jeremydmiller]

Working on this one today

Tasks

• Test with Select(x => x.ExplicitSql()) for scalar values
• Test with Select(x => new Persion{Name = x.ExplicitSql()})

[jeremydmiller]

Boom! Working locally.