CODEWITHSUNDEEP
mysqlentity-framework
Tony Vitabile
Tony Vitabile

Reputation: 8614

What is the correct way to write a query that matches on a UUID column in MySQL?

I have an application which I am porting from Postgres to MySQL. Nevermind why. The application uses Entity Framework 4 to query the database.

For various reasons, I have to use Guids in my C# code, save them to the database, and then query data based on the saved values of the Guids. I'm not very familiar with MySQL & how it handles what are essentially blobs.

First, there is no UUID type in MySQL. I have to save them as BINARY(16) values. OK, fine. I have created the columns as BINARY(16) and the data is written into the table. Good.

My problem is that I can't see to match on the stored values of the Guids. I have written a unit test that writes data with a known Guid to the table then tries to retrieve it. The data is going into the database fine but when I try to read it back, I get no rows.

Here's a sample table schema:

CREATE TABLE `MyDatabase`.`MyTable` (
    `id`                INT             NOT NULL AUTO_INCREMENT,
    `guid`              BINARY(16)      NOT NULL,
    `applicationId`     INT(11)         NOT NULL,
    `name`              VARCHAR(256)    NOT NULL,
    Description         VARCHAR(256)    NULL,
    SessionTimeout      INT             NOT NULL,
    DomainId            INT             NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT IX_aspnetx_groups UNIQUE ( `applicationId`, `id` )
);

Here's the Entity Framework code:

var g = ( from r in context.MyTable
          where r.guid = id
          select r ).Single();

Here's the query that's generated by Entity Framework:

SELECT
`Extent1`.`id`, 
`Extent1`.`guid`, 
`Extent1`.`applicationId`, 
`Extent1`.`name`, 
`Extent1`.`Description`, 
`Extent1`.`SessionTimeout`, 
`Extent1`.`DomainId`
FROM `aspnetx_groups` AS `Extent1`
WHERE `Extent1`.`guid` = '81d7de5e-4212-4ff8-b3d4-9f115261971d' LIMIT 2;

When this executes, it returns no rows, resulting in a "sequence contains no elements" exception being thrown in my C# code.

How do I make this work?

Upvotes: 1

Views: 1918

Answers (2)

Tony Vitabile
Tony Vitabile

Reputation: 8614

It turns out that the problem I was having had to do with quirks of the Entity Framework connector in the MySQL Connector/Net package. There is a connection string setting that you need to add if you are using BINARY(16) as the data type of your Guids in the database:

Old Guids=True

Once you add that to the connection string, Entity Framework starts to emit code that really works when inserting, updating, or comparing Guids.

I've also come to the conclusion that UUID / Guid support in MySQL is only half-baked and needs some serious work to bring it up to a usable state.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108490

In the WHERE clause on your SELECT, it looks like you are comparing a BINARY(16) (guid on the left side of the equals) with a character string literal on the right side.

To perform a valid comparison, I would convert that character string literal into a BINARY(16), and then compare that to guid.

So, removing all the dash characters and then using the UNHEX function should do the trick:

WHERE `Extent1`.`guid` = 
  UNHEX(REPLACE('81d7de5e-4212-4ff8-b3d4-9f115261971d','-',''))

For performance, you'll want your query to reference the bare guid column on the left side (just like it does), and not wrap the guid column in any sort of functions. You'll want any conversion to be done on the literal side of the predicate, so that the conversion only has to be done once at the beginning of the query, rather than having to do a conversion for each row in the table.

Upvotes: 1

Related Questions