LinqToSql and HashBytes
I need to execute this Sql command (which works fine in management studio):
select * from Users where Login = 'test' and PasswordHash = HashBytes('SHA1', 'test')
I wrote this c# linqtosql:
var user = db.ExecuteQuery<User>("select * from Users where Login = {0} and PasswordHash = HashBytes('SHA1', {1})", loginTextBox.Text.Trim(), passwordPasswordBox.Password).SingleOrDefault();
but it never works!!
can anybody help me?
thanks!
This is the DataContext's log:
select * from Users where Login = @p0 and PasswordHash = HashBytes('SHA1', @p1)
-- @p0: Input NVarChar (Size =开发者_StackOverflow社区 4000; Prec = 0; Scale = 0) [test]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [test]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
I profiled it via db.Log = Console.Out
, with the result:
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
select * from Users where Login = @p0 and PasswordHash = HashBytes('SHA1', @p1)
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Admiral Trask]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Arutha]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
Looking at this, I imagine the problem is that the param is NVarChar
rather than varchar
- so taking the hash (a binary operation) is different. If you hashed it as varchar
, you should probably cast the string to varchar
before the HASHBYTES
.
The following works, for example:
var user = db.ExecuteQuery<User>(@"select * from Users where Login = {0}
and PasswordHash = HashBytes('SHA1', CAST({1} as varchar(40)))", cn, pw)
.SingleOrDefault();
A simpler illustration of this is:
SELECT HASHBYTES('SHA1','12345'), HASHBYTES('SHA1',N'12345')
What you really want to do is to map a C# method to the db function HashBytes, which is done something like this
[Function(Name = "HashBytes")]
[return: Parameter(DbType = "VarChar(100)")]
string HashBytes(string hashtype, string text)
{ .... }
Details are given here: http://msdn.microsoft.com/en-us/library/bb386973.aspx
That will allow you to write the query in proper LINQ:
var q = from u in db.Users
where u.Login == cn && u.PasswordHash == HashBytes("SHA1", pw)
select u;
It seems you missed quotes in template string for login and password.
var user = db.ExecuteQuery<User>("select * from Users where Login = '{0}' and PasswordHash = HashBytes('SHA1', '{1}')", loginTextBox.Text.Trim(), passwordPasswordBox.Password).SingleOrDefault();
精彩评论