Ormlite - Db.SingleById behavior

#1

What is the expected behavior when an Id does not exist using SingleById?

var account = Db.SingleById<Account>(username);

I was expecting null but it’s returning an Account even if the Id does not exist…

I’m currently using 6.1.0

In my db, the Account.Id is an int.

But in the above example, I passed a username (a string) by mistake and was surprised to see OrmLite returned a value…

If it can help I tried the following:

var accounts = db.Select<Account>(x => x.Id == username);

var account = Db.SingleById<Account>(username);

accounts is empty but the next statement return an existing account…

If I use an int and an Id that does not exist SingleById returns null for account which is what I would expect.

The db is MySql

#2

A non existing record with that Primary Key should return null, behavior confirmed in:

public class Accounts
{
    public string Id { get; set; }
    public string Name { get; set; }
}

OrmLiteUtils.PrintSql(); // Log SQL to Console
db.DropAndCreateTable<Accounts>();
db.Insert(new Accounts { Id = "johnsmith", Name = "John Smith" });

db.SingleById<Accounts>("johnsmith").Name //= "John Smith"
db.SingleById<Accounts>("") //= null
db.SingleById<Accounts>("johnsmith2") //= null

Which in MySql prints:

SQL: DROP TABLE `Accounts`
SQL: CREATE TABLE `Accounts` 
(
  `Id` VARCHAR(255) PRIMARY KEY, 
  `Name` VARCHAR(255) NULL 
); 

SQL: INSERT INTO `Accounts` (`Id`,`Name`) VALUES (@Id,@Name)
PARAMS: @Id=johnsmith, @Name=John Smith
SQL: SELECT `Id`, `Name` FROM `Accounts` WHERE `Id` = @Id
PARAMS: Id=johnsmith
SQL: SELECT `Id`, `Name` FROM `Accounts` WHERE `Id` = @Id
PARAMS: Id=
SQL: SELECT `Id`, `Name` FROM `Accounts` WHERE `Id` = @Id
PARAMS: Id=johnsmith2

Please include a repro I can run to repro the behavior.

#3

I’ll try creating a repro.

In my example the Id is an int.

#4

username is an int?

#5

In the Account table the Id is an Int.

That’s the only difference with your example.

#6

SingleById is to query by Primary Key, what is username here?

var account = Db.SingleById<Account>(username);
#7

Username is a string

public class Account
{
    [AutoIncrement]
    public int Id { get; set; }

    [Required]
    [Unique]
    public string Username { get; set; }
}

I passed a string instead of an int by mistake and it returned an Account.

#8

The MySql table

create table Account
(
    Id  int auto_increment            primary key,
    Username         varchar(255)         not null,
)
#9

Still can’t repro:

public class AccountIntId
{
    [AutoIncrement]
    public int Id { get; set; }

    [Required]
    [Unique]
    public string Username { get; set; }
}

OrmLiteUtils.PrintSql();
db.DropAndCreateTable<AccountIntId>();
db.Insert(new AccountIntId { Username = "johnsmith" });

db.SingleById<AccountIntId>(1).Username //= johnsmith
db.SingleById<AccountIntId>(2) //= null
db.SingleById<AccountIntId>("") //= null
db.SingleById<AccountIntId>("johnsmith2") //= null

MySql Log:

SQL: DROP TABLE `AccountIntId`
SQL: CREATE TABLE `AccountIntId` 
(
  `Id` INT(11) PRIMARY KEY AUTO_INCREMENT, 
  `Username` VARCHAR(255) NOT NULL UNIQUE 
); 

SQL: INSERT INTO `AccountIntId` (`Username`) VALUES (@Username)
PARAMS: @Username=johnsmith
SQL: SELECT `Id`, `Username` FROM `AccountIntId` WHERE `Id` = @Id
PARAMS: Id=1
SQL: SELECT `Id`, `Username` FROM `AccountIntId` WHERE `Id` = @Id
PARAMS: Id=2
SQL: SELECT `Id`, `Username` FROM `AccountIntId` WHERE `Id` = @Id
PARAMS: Id=
SQL: SELECT `Id`, `Username` FROM `AccountIntId` WHERE `Id` = @Id
PARAMS: Id=johnsmith2

Can you provide a string value used that returned an account and the int Id of the account it returned.

#10

Yes. Give me a minute.

#12

I tried with username:

10-whatever-more-3

It returned the Account with an Id of 10.

#13

Ok that I can repro thx, investigating…

1 Like
#14

Yeah that’s a MySql coercion issue, where this query returns a record where Id=10:

SELECT * FROM AccountIntId WHERE Id = '10-whatever-more-3'

Another case due to MySql’s lax RDBMS constraints, e.g. this throws an invalid cast error PostgreSQL and doesn’t return any matching rows in SQL Server.

1 Like
#15

That’s pretty incredible…

2 Likes
#16

Do you recommend PostgreSQL over MySQL?

I didn’t spend a whole lot of time evaluating both options. We have some people familiar with MySQL and that’s why I chose it. But our hosted provider has support options for both so if you have a better experience with PostgreSQL I’ll consider switching down the road or use it over MySQL for new projects.

#17

Yeah definitely, PostgreSQL is my preferred RDBMS, better RDBMS features and integrity.

1 Like
#18

Did you try FireBird?
I love this RDMS. Using it since 2009. Low or almost zero maintenance. Fast with a lot of features.

#19

Never had the chance. But thanks for bringing it up. I’ll add it to my list.