My User table contains PasswordHash and PasswordSalt fields. I'm trying to do a lookup of a User based on their username and unhashed password. Since the salt is in the db I need to do the sha1 of the salt and password in the query.

How do I go about doing this? I was thinking I could create a new IEntityField2 and pass it a DbFunctionCall, but how would I pass UserFields.PasswordSalt into this to do the concatenation?

Is there another/better way of doing this?

Edit: ok, I've kind of got this working. It's generating the sql correctly and i'm able to copy that sql to managment studio and run the query and get the expected result. However, the EntityCollection isn't returning anything.

EntityCollection<UserEntity> col = new EntityCollection<UserEntity>();

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    IEntityField2 passwordField = new EntityField2("Password", new DbFunctionCall("SubString(master.dbo.fn_varbintohexstr(HashBytes('SHA1', {0} + {1})), 3, 40)", new object[] {password,UserFields.PasswordSalt}));

    IRelationPredicateBucket bucket = new RelationPredicateBucket();
    bucket.PredicateExpression.Add(UserFields.Username == username);
    bucket.PredicateExpression.AddWithAnd(UserFields.PasswordHash == passwordField);

    adapter.FetchEntityCollection(col, bucket, 1);
}

Query: SELECT TOP 1 [Platform].[dbo].[User].[ID] AS [Id], [Platform].[dbo].[User].[AccountID] AS [AccountId], [Platform].[dbo].[User].[Username], [Platform].[dbo].[User].[PasswordHash], [Platform].[dbo].[User].[PasswordSalt], [Platform].[dbo].[User].[ContactID] AS [ContactId], [Platform].[dbo].[User].[UserType], [Platform].[dbo].[User].[IsLocked], [Platform].[dbo].[User].[DateCreated], [Platform].[dbo].[User].[DateModified], [Platform].[dbo].[User].[DatePasswordChanged], [Platform].[dbo].[User].[Status] FROM [Platform].[dbo].[User] WHERE ( ( [Platform].[dbo].[User].[Username] = @Username1 AND [Platform].[dbo].[User].[PasswordHash] = SubString(master.dbo.fn_varbintohexstr(HashBytes('SHA1', @LO1c5483dd2 + [Platform].[dbo].[User].[PasswordSalt])), 3, 40)))

Parameter: @Username1 : AnsiString. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "wbrowning".

Parameter: @LO1c5483dd2 : String. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: "testpassword".

Rule of thumb, if the query returns results, then the collection should receive these results.

Could you please attach a simple repro solution, with the database script, including one table with one row to test. No binaries, just the code and the script, and the lgp file.

Thanks.

Ok I figured out the problem. My original sha1 hash is created in c# using ascii encoding. The password string I'm passing in to the object array for DbFunctionCall is being turned into a unicode nvarchar. After capturing the query with sql profiler and changing it to be varchar it works.

Thanks.

No problem, always happy to help. It's not uncommon for people to figure out their issue once they've had to think through it to explain it to us...

Most of my replies on here are just gentle prods to get people to go back and think through properly what they are trying to do...!

Matt