Comparing arrays of bytes with NHibernate

Question

The following Linq to NHibernate query results in a System.NotSupportedException.

IEnumerable<File> FindByMd5(byte[] md5)
{
    return this.Session.Query<File>().Where(f => f.Md5.SequenceEqual(md5)).ToList();
}

How should I do this using Linq to NHibernate or QueryOver<File>()?

What is the type of `File.Md5`? – jason Jun 14 '11 at 14:36 — jason, Jun 14 '11 at 14:36

score 1 · Answer 1 · answered Mar 04 '15 at 08:45

Old Q but, still issues with Linq. So, using NHibernate and SQLite, when comparing against a byte array value, you can use query over restrictions or criterias.

session.QueryOver<TestItem>().WhereRestrictionOn(i => i.Foo).IsBetween(aaa).And(aaa);

or

session.CreateCriteria<TestItem>().Add(Restrictions.Eq("Foo", aaa))

score 1 · Accepted Answer · answered Jun 22 '11 at 11:51

Due to the fact that the error is already indicating that NHibernate does not support that feature. I would create a named query and solve the equation within a query. I have tested it with MySQL (using a common username and password comparison as example) and the following statement returns the desired row (password is a BINARY(32) field):

SELECT * FROM `user` WHERE `password` = MD5('test');

Using MSSQL you can do:

SELECT * FROM [user] WHERE [password] = HASHBYTES('MD5', 'test')

So to extend this to a named query you would create an .hbm.xml file like 'User.hbm.xml' with the following content:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="My.Model" namespace="My.Model">
  <sql-query name="GetUserByCredentials">
    <return class="My.Model.User, My.Model" />
    <![CDATA[
      SELECT * FROM User WHERE Username = :Username AND Password = MD5(:Password)
    ]]>
  </sql-query>
</hibernate-mapping>

To configure this I used Fluent NHibernate but something similar would be possible with just plain NHibernate:

Fluently.Configure()
    .Database(MySqlConfiguration.Standard
        .ConnectionString(x => x.FromConnectionStringWithKey("Test"))
        .AdoNetBatchSize(50))
    .Cache(c => c
        .UseQueryCache()
        .ProviderClass<HashtableCacheProvider>())
    .Mappings(m =>
    {
        m.FluentMappings.AddFromAssemblyOf<IHaveFluentNHibernateMappings>().Conventions.Add(ForeignKey.EndsWith("Id"));
        m.HbmMappings.AddFromAssemblyOf<IHaveFluentNHibernateMappings>();
    })
    .BuildConfiguration();

This statement looks for the ".hbm.xml" files in the assembly with the interface named "IHaveFluentNHibernateMappings"

With this in place you can do the following at session level:

public User GetUserByCredentials(string username, string password)
{
    IQuery query = Session.GetNamedQuery("GetUserByCredentials");
    query.SetParameter("Username", username);
    query.SetParameter("Password", password);

    return query.UniqueResult<User>();
}

And by calling the the GetUserByCredentials method, the custom query will be executed.

As you can see password is a string, so you need to convert your MD5 byte array to a string first by using:

System.Text.StringBuilder s = new System.Text.StringBuilder();
foreach (byte b in md5ByteArray)
{
   s.Append(b.ToString("x2").ToLower());
}
password = s.ToString();

Good luck!

I was hoping for a simpler solution using Linq, but your solution would work. — mll, Jun 24 '11 at 00:50
By the way, you might be interested in [`BitConverter.ToString`](http://msdn.microsoft.com/en-us/library/3a733s97.aspx) to convert a `byte[]` to a `string`. — mll, Jun 24 '11 at 00:54

score 0 · Answer 3 · answered Jun 24 '11 at 01:30

I’ve solved the problem by storing the MD5 as a string.

public class PersistedFile
{
    public virtual int Id { get; set; }
    public virtual string Path { get; set; }
    public virtual string Md5 { get; set; }
}

Files are saved using this method:

public PersistedFile Save(string filePath)
{
    using (var fileStream = new FileStream(filePath, FileMode.Open))
    {
        var bytes = MD5.Create().ComputeHash(fileStream);

        using (var transaction = this.Session.BeginTransaction())
        {
            var newFile = new PersistedFile
            {
                Md5 = BitConverter.ToString(bytes),
                Path = filePath,
            };
            this.Session.Save(newFile);
            transaction.Commit();
            return newFile;
        }
    }
}

Files are retrieved using this one:

public IEnumerable<PersistedFile> FindByMd5(string md5)
{
    using (var transaction = this.Session.BeginTransaction())
    {
        var files = this.Session.Query<PersistedFile>().Where(f => f.Md5 == md5).ToList();
        transaction.Commit();
        return files;
    }
}

Comparing arrays of bytes with NHibernate

3 Answers3