Is there a method like SingleOrDefault in Linq?

[magicu]

I don't want to get a list by query. For there is one record at most in the table. I want to get the record or a null value by a method like SingleOrDefault.

[QxOrm admin]

Hi,

A function like SingleOrDefault() from Linq doesn't exist into QxOrm library, but it's quite easy to write your own function with a similar behaviour.

Here is an example of a similar function using QxOrm library :

Code: Select all

template <typename T>
boost::shared_ptr<T> SingleOrDefault(qx::QxSqlQuery query)
{
   QList< boost::shared_ptr<T> > list;
   QSqlError daoError = qx::dao::fetch_by_query(query, list);
   if (daoError.isValid()) { throw qx::dao::sql_error(daoError); }
   else if (list.count() > 1) { QSqlError err("query should result in at most a single result"); throw err; }
   else if (list.count() <= 0) { return NULL; }
   return list.at(0);
}

[Guest]

Hi,

A function like SingleOrDefault() from Linq doesn't exist into QxOrm library, but it's quite easy to write your own function with a similar behaviour.

Here is an example of a similar function using QxOrm library :

Code: Select all

template <typename T>
boost::shared_ptr<T> SingleOrDefault(qx::QxSqlQuery query)
{
   QList< boost::shared_ptr<T> > list;
   QSqlError daoError = qx::dao::fetch_by_query(query, list);
   if (daoError.isValid()) { throw qx::dao::sql_error(daoError); }
   else if (list.count() > 1) { QSqlError err("query should result in at most a single result"); throw err; }
   else if (list.count() <= 0) { return NULL; }
   return list.at(0);
}

Thanks for your code, From efficiency into consideration, SingleOrDefault should add "select top 1 ... " in the query, It's better be converted automatically by QxOrm. I think it's a very useful function, Will I be added in the next release?

[QxOrm admin]

From efficiency into consideration, SingleOrDefault should add "select top 1 ... "

I don't think so : maybe "TOP 2", but not "TOP 1" !
The purpose of this function is to check if there is only 1 row into your table corresponding to your query : and if there is more than 1 row, an exception is thrown.
So if you have more than 1 row, you have a problem somewhere.

Is this really an optimization ?
No, because your query must return only 1 row, so I don't think that this is very useful to add "TOP 2" to the query.

I think it's a very useful function, Will I be added in the next release?

I don't think I will add this function into QxOrm library : there is exceptions thrown and this is not the style of QxOrm library (more Qt style without exception).
You can add the code of the function of my first post into your own project, it will work
And you can add it into a namespace if you want, like this :

Code: Select all

namespace qx {
namespace dao {

template <typename T>
boost::shared_ptr<T> single_or_default(qx::QxSqlQuery query)
{ ... }

}} // namespace qx::dao

[magicu]

From efficiency into consideration, SingleOrDefault should add "select top 1 ... "

I don't think so : maybe "TOP 2", but not "TOP 1" !
The purpose of this function is to check if there is only 1 row into your table corresponding to your query : and if there is more than 1 row, an exception is thrown.
So if you have more than 1 row, you have a problem somewhere.

Is this really an optimization ?
No, because your query must return only 1 row, so I don't think that this is very useful to add "TOP 2" to the query.

I think it's a very useful function, Will I be added in the next release?

I don't think I will add this function into QxOrm library : there is exceptions thrown and this is not the style of QxOrm library (more Qt style without exception).
You can add the code of the function of my first post into your own project, it will work
And you can add it into a namespace if you want, like this :

Code: Select all

namespace qx {
namespace dao {

template <typename T>
boost::shared_ptr<T> single_or_default(qx::QxSqlQuery query)
{ ... }

}} // namespace qx::dao

Got it, thanks.

[Davita]

Sorry guys, I'm very new to QxOrm and maybe it's stupid, but I think FirstOrDefault/SingleOrDefault could easily be implemented by using something like limit(1).

[QxOrm admin]

I think FirstOrDefault/SingleOrDefault could easily be implemented by using something like limit(1)

You are right but magicu said in its first post : "I don't want to get a list by query".
And if you want exactly the same behaviour as C# function (check if there is more than 1 row into the table and throws an exception in this case), you have to write your own function.
Something like this :

Code: Select all

template <typename T>
boost::shared_ptr<T> SingleOrDefault(qx::QxSqlQuery query)
{
   QList< boost::shared_ptr<T> > list;
   QSqlError daoError = qx::dao::fetch_by_query(query, list);
   if (daoError.isValid()) { throw qx::dao::sql_error(daoError); }
   else if (list.count() > 1) { QSqlError err("query should result in at most a single result", "", QSqlError::UnknownError); throw qx::dao::sql_error(err); }
   else if (list.count() <= 0) { return NULL; }
   return list.at(0);
}