How to do subselect an entity say for example I am having a combobox that is bound to an table that has say Country { countryid int, continentcd char(1), countryname varchar(50), countrydescription varchar(255), populationcharacteristicsnote text, culturecharacteristicsnote text, countrymappicture binary }. I don't want to use all of the columns in the select list besides my predicate being continentcd = 'S' I want the generated select to be selct countryid, countryname from Country where continentcd = 'S'

However the same Country table I want in the code maintenance window to be able to see countryid , countryname , continentcd in the datagridview.

I want to do "lazy get" of note & description columns on detail or freeform mode of a particular row where I edit only those column with id there.

I want another scenario of having to select all of them.

I now have 4 different select statements.

The scenarios why people want to do sub select on a table is to have security, performance & I don't want to have all the columns when I don't need all of them. When the query generator can do partial update, partial delete, partial insert. That is columns only that are dirty are impacted in the DML. Is there a way to do the same for SELECT as well?

One of the approach I could see to address is this if I have 3 views on the table with the subselected columns & the view is updateable & the ORM has update capability on view.

Another approach could be is I can have a stored procedure or a view. And a mechanism to instance it to the Entity for the table. Provided the view also has the primary key, make it updateable instead of a newrow. (Old, dirty trick)

Or is there a better method?

One could argue with having to create a new extender table. But then the records are 1:1 relation. And having it to split into multiple tables, joining when I need all of the columns & having them to join with other tables when required in a collection. I just see big time performance issues.

thanks for the help.

Take a look at excludedfields:

For example this code:

ExcludeIncludeFieldsList excludedFields = new ExcludeIncludeFieldsList();
excludedFields.Add(CountryFields.Countrymappicture);

That would exclude the countrymappicture data from the entity ...

So you can use one function to retrieve the data, have an excludedfieldslist as parameter to the function and use the same function to fill the entities of country with exactly only the data that you need.

And yes, this also functions for related entities (subqueries etc.)

For dropdownlists and other read only selects I use a view in the database, that is simple to use and easy to maintain and performs better since it's just a list and not complete entities in a collection.

But that's just a design question how you will do that.

Beside the ExecludeFields approach which is introduced in v.2.5 You can also use a dynamicList to populate the combobox with Id and Name fields.