ServiceStack ORMLite - Select columns

Question

I recently started working with ServiceStack and its ORMLite framework. I have searched on Google and browsed the source code but couldn't find anything relevent.

Is there any way to select specific columns when executing a query with ORMLite ? Something like that : Db.First<Model>(q => q.Id == someId, "Column1, Column2")

Unless I missed this feature, I am surprised nobody asked about this before, since this is one the rule of thumbs to optimize your DB transactions.

Hi cobolstinks, and thanks for your answer. I think you are talking about the Java version which is meant to run on mobile devices. I am in fact refering to the ServiceStack.ORMLite framework, which is a C# port meant to be used in ASP.Net applications — Alexis, Oct 02 '12 at 19:13
yeap my bad i was referring to ormlite and was using it with ADK. I removed my original comment. — cobolstinks, Oct 03 '12 at 01:37

score 7 · Accepted Answer · edited May 23 '17 at 11:47

7

If you want to specify columns other that the table you need to use SQL as seen in this earlier example

So in your case you could do something like:

Db.First<Model>("SELECT Column1, Column2 FROM AnyTableOrView");

You can also create a partial model that looks at your table by decorating it with the [Alias] attribute, like:

[Alias("AnyTableOrView")]
public class Model {
    public int Id { get; set; }
    public string Column1 { get; set; }
    public string Column2 { get; set; }
}

Then you can do something like:

Db.First<Model>(q => q.Id == someId);

And it will only SELECT + populate fields from the partial model.

edited May 23 '17 at 11:47

Community

1
1

answered Oct 02 '12 at 19:38

mythz

141,670
29
246
390

I was using the first way you described, but your second approach is way cleaner. Thanks and keep up the good work. – Alexis Oct 02 '12 at 19:44
Just in case anyone wonders, if you aliased your Id attribute, you also have to alias it in the stripped down model you use for querying. – Alexis Oct 02 '12 at 19:54

score 2 · Answer 2 · answered Jan 03 '13 at 15:07

I did try this :

Created a Database VIEW (table name and columns are already set)
Created a class named "Event" and matching each fields for that table with a property (i used [Alias] for table name and for all columns to have nice names)

Wrote access to DB to select 1 record based on it's ID

    var dbFactory = new OrmLiteConnectionFactory(
        "Data Source=MyDB;User Id=user;Password=pwd",  // Connection String
        OracleDialect.Provider);

    using (var db = dbFactory.OpenDbConnection())
    {
            var event = db.GetByIdOrDefault<Event>( request.Id );
    }

At that point the var 'event' is populated but only the Id field is filled ! all the others fields of the class are not filled (while there are really data in database).

It's the simplest i can do and it does not work. Any ideas ? (PS : i am using OrmLite for Oracle)

Thanks

kilroyFR · Answer 3 · 2013-01-04T09:50:14.400

1

I have found the problem. It was due to an incorrect type matching between field in my class (defined as a string) and the corresponding Oracle Field (that is a DATE).

I replaced the string with datetime and worked like a charm.

So it's working perfectly with a VIEW and that's GREATLY simplify the code.

edited Jan 04 '13 at 09:50

answered Jan 03 '13 at 16:09

kilroyFR

73
4

score 1 · Answer 4 · answered Feb 07 '13 at 17:19

I had a similar problem, however my solution was different.

I had a int property in my POCO. My query (from Oracle) was returning a null for this property. It caused a exception to be raised and prevented further processing of that row. The result was a partial populated POCO.

The solution was to change to type to be nullable.

public int? mypropperty

ServiceStack ORMLite - Select columns

4 Answers4