QuerySpec

Posts   
 
    
JonE
User
Posts: 164
Joined: 15-Nov-2016
# Posted on: 17-Nov-2016 14:30:20   

When using a dynamicquery instead of linq, is it possible to specify the column names to include in the select using a string array or even as a comma delimited string?

.Select(New String() {"CompanyID", "Name"})

The above doesn't work but maybe there is another way of doing it?

Regards

Jon

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 17-Nov-2016 16:51:29   

Could you please explain the business case/scenario in which you need this? Maybe there is another way of doing it.

JonE
User
Posts: 164
Joined: 15-Nov-2016
# Posted on: 17-Nov-2016 20:56:11   

Sure, I would like to be able to specify which columns I use at runtime rather than hardcoding which columns should be in the select.

With this specific use I have 3 potentially different sets of fields that will be required for any particular entity (List, Email (some extra columns beyond the list) and Admin Email (possibly more columns still if the user is an admin user)).

Further to that I am hoping to unify specific web apis into a small number of controllers with the field sets coming from elsewhere in the code - on a different model.

Company Model List Fields Email Fields AdminEmail Fields DTO Model Reference etc

When the controller is called it will be called with the model and from that it will be able to dynamically add in the various extra info.

This will allow me to make a very DRY system (I hope) and after it is complete maintenance of the API shoul - I hope - be dead easy.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-Nov-2016 16:20:35   

You can specify which fields to fetch, using field objects and .Select().

Dim q = qf.Customer.Select(CustomerFields.CompanyName, CustomerFields.CustomerId)

will fetch just these 2 fields. You of course have to project it then to a type, using a projector func. http://www.llblgen.com/Documentation/5.0/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_projections.htm#example

so projecting that to a CustomerDTO will then become:

Dim q = qf.Customer _
            .Select(CustomerFields.CompanyName, CustomerFields.CustomerId) _
            .WithProjector(Function(r) New CustomerDTO With { _
                .CompanyName = r[0].ToValue(Of String), _
                .CustomerId = r[1].ToValue(Of Int32) })

or something like that. (I might have messed up the VB.NET syntax perhaps!)

With v5.1, which is released next week, you can define this more easily: http://www.llblgen.com/Documentation/5.1/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_projections.htm#dynamicquery.selecttfield-list

Frans Bouma | Lead developer LLBLGen Pro