Mohammad Mohammad - 3 years ago 149
C# Question

Sql Query converting to Linq Query By using Wcf Service Platefrom

I am currently working on Wcf Service and consuming it in Angular JS application.
I am trying converting Sql query to Linq Query. I am creating user login system based on username and password . If user enter wrong username and password four time then I want locked user account into sql database . Otherwise if user enter wrong username and password first time but enter correct username and password second time then I want to return the method true and recount the Attempt to Zero . But I am getting expected result with the Linq query .

Here is my ADO.NET Code ...

public bool AuthenticateUser(UserLogin userLogin)
{

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spAuthenticateUser", con);
cmd.CommandType = CommandType.StoredProcedure;
string encryptedpassword = FormsAuthentication.HashPasswordForStoringInConfigFile(userLogin.Password, "SHA1");
SqlParameter paramUsername = new SqlParameter("@UserName", userLogin.Username);
SqlParameter paramPassword = new SqlParameter("@Password", encryptedpassword);

cmd.Parameters.Add(paramUsername);
cmd.Parameters.Add(paramPassword);

con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int RetryAttempts = Convert.ToInt32(rdr["RetryAttempts"]);
if (Convert.ToBoolean(rdr["AccountLocked"]))
{
return false;
}
else if (RetryAttempts > 0)
{
int AttemptsLeft = (4 - RetryAttempts);

}

else if (Convert.ToBoolean(rdr["Authenticated"]))
{
return true;
}

else
{
return false;
}
}
}
}


Here is my Linq query I tried to converted but it is not giving expecting result .

public bool AuthenticateUser(UserLogin userLogin)
{
using (HalifaxDatabaseEntities db = new HalifaxDatabaseEntities())
{
var attamp = from X in db.tblUsers
where X.Username == userLogin.Username&& X.Password == userLogin.Password
select X.RetryAttempts != 4;
return attamp.Single();

}
}


What will the correct Linq for Following Sql query

Answer Source

You may want to take out the password from filter to calculate total number of retries. but use it while actually authenticating the user.

Also once it is successfully authenticated reset the counter to zero.

bool LimitExceeded()
{
var exceeded=false;
var totalRetries =-1;
var attamp = from X in db.tblUsers
                where X.Username == userLogin.Username 
                select X

 if( attamp.Any())
 {
        if(attamp.firstOrDefault().RetryAttempts.HasValue)      totalRetries =attamp.firstOrDefault().RetryAttempts;
        exceeded=  totalRetries>4;
 }
 return exceeded
}

The above code is not written using any code editor. That may not compile. I just wrote to give you an idea.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download