OrmLite: any way to do min/max operators?

[Brannon]

Is there any way to do a MIN or MAX operation with the current expression visitor stuff?

[Demis Bellot]

Doesn't look like it (Note: SqlExpressions were added by @angelcolmenares)

But feel free to add what you need to the base SqlExpressionVisitor class :)

https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/src/ServiceStack.OrmLite/Expressions/SqlExpressionVisitor.cs

ANSI compliant stuff like Min/Max could be generically implemented in the above shared class.

Otherwise any RDBMS's specific versions should go in their respective SqlExpression versions.

On Wed, Mar 7, 2012 at 7:05 PM, Brannon <notac...@gmail.com> wrote:

Is there any way to do a MIN or MAX operation with the current expression visitor stuff?

--
- Demis

http://twitter.com/demisbellot

http://www.servicestack.net/mythz_blog

[aicl]

Max and Min are already implemented:

ev.Select(r=> Sql.As(Sql.Max(r.Birthday), "Birthday"));

ev.Select(r=> Sql.As(Sql.Min(r.Birthday), "Birthday"));

ev.Select(r=>new{r.City,  MaxResult=Sql.As( Sql.Min(r.Birthday), "Birthday") })

.GroupBy(r=>r.City)

.OrderBy(r=>r.City);

take a look at : 

https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/src/AllDialectsTest/Main.cs

also implemented: Sum, Count, Avg

Angel

[Demis Bellot]

Ahh brilliant, why it's always good to quiz the Author :)

[Brannon]

Tell me what I'm doing wrong here as this doesn't seem to return the right value (or any value for that matter):

public DateTime Maximum

{

get

{

using (var cmd = _connection.CreateCommand())

{

var visitor = _provider.ExpressionVisitor<Event>();

visitor.Select(e => Sql.Max(e.Timestamp));

return cmd.Select(visitor).Single().Timestamp;

//return cmd.GetScalar<DateTime>("SELECT MAX(Timestamp) FROM Events"); // this is what we want! (generically, though)

//return cmd.From<Event>().Max(e => e.Timestamp); // if only this "From" were implemented!

}

}

}

[Demis Bellot]

If you used ServiceStack with the MiniProfiler you should be able to see the SQL generated as seen in this example:

https://gist.github.com/1787443

Otherwise you can set a Console Log factory:

LogManager.LogFactory = new ConsoleLogFactory(); 

To output the SQL on a debug build of OrmLite.

Cheers,

[aicl]

hi,

assuming that Event has property "Timestamp",  try :

visitor.Select(e =>Sql.As(  Sql.Max(e.Timestamp), "Timestamp") );

return cmd.Select(visitor).Timestamp;

// to execute SELECT MAX(Timestamp) as Timestamp FROM Events"

//  result  will be in  Event.Timestamp 

 

angel

[Brannon]

The suggested code: 

assuming that Event has property "Timestamp",  try :

visitor.Select(e =>Sql.As(  Sql.Max(e.Timestamp), "Timestamp") );

return cmd.Select(visitor).Timestamp;

I don't think that (above) code will work because the Select command attempts to convert the return value of the query to type T of the visitor. That was the problem with my first attempt at using Sql.Max. The FirstOrDefault method has the same problem. What we need are some Select/FirstOrDefault/GetScalar methods that take two generic types: one for the table and one for the result. My method ended up looking like this:

public DateTime Maximum

{

get

{

using (var cmd = _connection.CreateCommand())

{

var visitor = _provider.ExpressionVisitor<Event>();

visitor.Select(e => Sql.Max(e.Timestamp));

var sql = visitor.ToSelectStatement();

return cmd.GetScalar<DateTime>(sql);

}

}

}

That's four lines of code to accomplish what most LINQ implementations can do in a single statement.

[Demis Bellot]

We just need another overload for Scalar that is all, e.g:

return cmd.GetScalar<DateTime>( e => Sql.Max(e.Timestamp) ); 

I nominate who needs it first, contributes it :)

[aicl]

hi,

return cmd.GetScalar<DateTime>( e => Sql.Max(e.Timestamp) ); will be good !

but I insist that this works:

visitor.Select(e =>Sql.As(  Sql.Max(e.Timestamp), "Timestamp") );  // note  Sql.As(   ) 

return cmd.Select(visitor)[0].Timestamp; // in first post I forget index[0]

or 

return dbCmd.FirstOrDefault(visitor).Timestamp

there are samples of this in:  https://github.com/ServiceStack/ServiceStack.OrmLite/blob/master/src/AllDialectsTest/Main.cs#L378

angel

[Brannon]

I nominate who needs it first, contributes it:

return cmd.GetScalar<Event, DateTime>( e => Sql.Max(e.Timestamp) ); will be good !

Fine. Go check your Pull Requests.

 

but I insist that this works:

visitor.Select(e =>Sql.As(  Sql.Max(e.Timestamp), "Timestamp") );  // note  Sql.As(   ) 

return dbCmd.FirstOrDefault(visitor).Timestamp; 

That may work my friend, but it is darn ugly. You're relying on the fact that Sql.As returns a string to select the correct overload. And you're relying on "Timestamp" (the second param on Sql.As) to exactly match the name of the property on the type. That's not the strong-typed C# we all know and love. (And internally we're handling the missing columns by eating exceptions -- also quite ugly and very slow. You end up with invalid values for everything other than the one property you returned.)

[Demis Bellot]

What's API are you suggesting instead?

[aicl]

hi,

Demis, can you review https://github.com/ServiceStack/ServiceStack.OrmLite/pulls ( sent by Brannon)

( it seems ,  you do not recieve notifications from OrmLite)

[Brannon]

What's API are you suggesting instead?

The whole purpose of the ORM is so that we don't have to use string types for accessing our data. If we wanted to do that we could just use embedded SQL in the project. Rather, we are using an ORM to create our database from our strong .NET data types. It allows us to easily refactor our table definitions, add columns, etc. without having to search for strings referencing them. I'm suggesting that we avoid any methods that require us to put in a string to match a property name or a table name or a column name, etc.

[Demis Bellot]

 

The whole purpose of the ORM is so that we don't have to use string types for accessing our data.

I don't agree. An ORM should map to strong types as conventionally and idiomatically as possible, but they can just as easily be queried using SQL. 

For common simple queries we can provide a typed API where possible but for joins and the like my preference is to use SQL.

i.e. It's great when we can have a strong-typed API but I get by just as easily when it's not available.

If we wanted to do that we could just use embedded SQL in the project.

Good idea, SQL is a DSL for querying an RDBMS, For complex queries I prefer using SQL than working with a leaky abstraction that I have no idea what SQL gets generated.

Rather, we are using an ORM to create our database from our strong .NET data types. It allows us to easily refactor our table definitions, add columns, etc. without having to search for strings referencing them. 

Right so it still maps to strong types which serve as the master schema for the generated tables. You should only be concerned about your existing queries here since everything else is mapped to your POCO types.

I suggest putting all related queries behind a repository so if you do make a change there's only 1 place to look.

 I'm suggesting that we avoid any methods that require us to put in a string to match a property name or a table name or a column name, etc.

And I'm suggesting we come up and agree on the ideal API. i.e. If you're critiquing the current API, come up with a better one to replace it.

The primary goals of the OrmLite is to map to C# types as conventionally as possible via succinct, intuitive DRY APIs that reduce the boilerplate associated with DataAccess.

Since OrmLite is just extension methods around IDbCommand, we're able to orthagonally add new features and DRY overloads side-by-side without restricting access to the ADO.NET data classes or affecting the complexity of the existing APIs.

And OrmLite is not a finished library, especially in regards to the SQL Expression functionality which is fairly new. 

My suggestion is for those that need the new APIs to add it them themselves, we're always available here if you need any help.

- Demis

On Thu, Mar 8, 2012 at 6:32 PM, Brannon <notac...@gmail.com> wrote:

What's API are you suggesting instead?

The whole purpose of the ORM is so that we don't have to use string types for accessing our data. If we wanted to do that we could just use embedded SQL in the project. Rather, we are using an ORM to create our database from our strong .NET data types. It allows us to easily refactor our table definitions, add columns, etc. without having to search for strings referencing them. I'm suggesting that we avoid any methods that require us to put in a string to match a property name or a table name or a column name, etc.