SelectOnly<T> extension method

ServiceStack.OrmLite
1

Would it be possible to have a SelectOnly extension in which one could explicitly provide the columns (string list, or properties) to retrieve rows?

For example, we would like to use a POCO for an Order table but only retrieve the ID, the Name and the CreationDate whereas the POCO itself has about 50 properties which are in fact fields in the DB.
We would like to reuse this DB object for lists (customer can choose which columns to include) without having to create a specific POCO for every possible combination.

2

There are a number of different ways to select partial columns, have a look at the Dynamic Result Sets examples.

Also you can specify to select into a partial typed POCO with:

var results = db.Select<Partial>(db.From<Table>());
3

The partial POCOs is the way we specifically do not want to go. This would imply having hundreds (if not thousands) of specific POCOs.

Also, maybe I am missing something, but I do not see anything on the link you provided regarding dynamic querying with OrmLite. The link points to custom rules for routing. We already passed that point and would just like to make the resulting columns (semi) dynamic. Did you mean this link? GitHub - ServiceStack/ServiceStack.OrmLite: Fast, Simple, Typed ORM for .NET

In fact we are looking for duck-typing, like described in this article:
http://designpattern.ninja/catalog/design-patterns/dynamic-tuple-dto.html

I would think this would be pretty doable in a similar way the UpdateOnly works for partial updates. In this case, we just want partial select.

4

The dynamic result sets link shows how you can select a partial result set using different collections or c# tuples, the other option is using a typed poco. You can also use the .Select() expression to select a partial list back into the same POCO if you want, that’s all the available options.

Pasted wrong link earlier, the right link is:

5

Right. The available options are OK for some, but unfortunately not for us :smile:
So, again: any chance for adding a SelectOnly with the same kind for syntax for select like the UpdateOnly has?

6

UpdateOnly doesn’t populate results and the existing options should be satisfactory, otherwise submit a feature request on UserVoice with a specific example which can’t be done with the existing solutions.

1 Like
7

I took the ORMLite code and implemented the feature. Far easier than I expected to be honest. Well written code allows for easy extension.

I added a simple test to show that the select only returns the field I wanted:

    [Test]
public void Can_SelectOnly_Into_ModelWithIdAndName_from_ModelWithFieldsOfDifferentTypes_table()
{
  using (var db = OpenDbConnection())
  {
    db.DropAndCreateTable<ModelWithFieldsOfDifferentTypes>();

    var rowIds = new List<int>(new[] { 1, 2, 3 });

    for (var i = 0; i < rowIds.Count; i++)
      rowIds[i] = (int)db.Insert(ModelWithFieldsOfDifferentTypes.Create(rowIds[i]), selectIdentity: true);

    var rows = db.SelectOnly<ModelWithIdAndName>(typeof(ModelWithFieldsOfDifferentTypes), x => new { x.Id });
    var dbRowIds = rows.ConvertAll(x => x.Id);

    Assert.That(dbRowIds, Is.EquivalentTo(rowIds));
  }
}

The x=> new { x.Id } should also be able to accept a list of column names like new List{ “Id”}. I can implement that too, but have not done this yet. Just let me know if I should do that or if you want to do this yourself.

In the above test, I specifically mentioned to return only the Id field of the test model. I will fork the code and provide a pull request

8

I’m currently on a plane so can’t check anything but this looks like it’s doing the same thing as:

var rows = db.Select<ModelWithIdAndName>(db.From<Table>().Select(x => new { x.Id });

The .Select() API also accepts a string list of fields which you can use instead if you prefer.

But in this case if you’re only selecting a single column you can use db.Column() which will convert it directly in a list, if you’re selecting 2 columns you can use db.Dictionary() and for any more columns I’d use the c# 7 tuple support in the dynamic result set example.

9

Did some homework, signed the Contributers Agreement and added a Pull Request.

10

No problem. You post just crossed the PR I made. The thing is that the number of columns required to return is dynamic. I will check what the exact statement is when I select the rows like you mention. If the .Select accepts column names / field names, then that would be just fine indeed! Keep you posted.

11

Geez… this just works! Forget my PR… Thx!

1 Like