SQL Server ADO.NET查询转换为WCF服务中的Linq

问题描述：

我试图将SQL查询转换为linq查询，但是当我运行查询时，它没有返回预期的结果。一些ado.net代码转换为linq有点复杂。SQL Server ADO.NET查询转换为WCF服务中的Linq

这里是我的ADO.NET代码：

public bool AuthenticateUser(UserLogin userLogin) 
{ 
    string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; 

    using (SqlConnection con = new SqlConnection(CS)) 
    { 
     var result = false; 

     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(); 

     if (rdr.HasRows) 
     { 
      while (rdr.Read()) 
      { 
       int RetryAttempts = Convert.ToInt32(rdr["RetryAttempts"]); 

       if (Convert.ToBoolean(rdr["AccountLocked"])) 
       { 
        result = false; 
       } 
       else if (RetryAttempts == 1) 
       { 
        result = false; 
       } 
       else if (RetryAttempts > 1) 
       { 
        int AttemptsLeft = (4 - RetryAttempts); 
        result = true; 
       } 
       else if (Convert.ToBoolean(rdr["Authenticated"])) 
       { 
        result = true; 
       } 
      } 
     } 

     return result; 
    } 
}

这里是LINQ查询到目前为止，我已经做了..

public bool AuthenticateUser1(UserLogin userLogin) 
{ 
    using (HalifaxDatabaseEntities db = new HalifaxDatabaseEntities()) 
    { 
     var exceeded = false; 
     var Totalcount = 0; 
     int RetryAttempts = 4; 

     var attamp = from X in db.tblUsers 
        where X.Username == userLogin.Username && X.Password == userLogin.Password 
        select X; 

     if (attamp != null) 
     { 
      var cheekenrty = from x in db.tblUsers 
          where x.RetryAttempts == RetryAttempts 
          select x; 

      if (cheekenrty.Equals(RetryAttempts)) 
      { 
       return exceeded; 
      } 
      else 
      { 
       return true; 
      } 
     } 
     else 
     { 
      Totalcount++; 
     } 

     return exceeded; 
    } 
}

亚胺酯窗户，可把用.. cheekenrty.GetType（？）

?cheekenrty.GetType() 
{Name = "DbQuery`1" FullName = "System.Data.Entity.Infrastructure.DbQuery`1[[HalifaxWCFProject.tblUser, HalifaxWCFProject, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]"} 
    Assembly: {EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089} 
    AssemblyQualifiedName: "System.Data.Entity.Infrastructure.DbQuery`1[[HalifaxWCFProject.tblUser, HalifaxWCFProject, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
    Attributes: Public | BeforeFieldInit 
    BaseType: {Name = "Object" FullName = "System.Object"} 
    ContainsGenericParameters: false 
    CustomAttributes: Count = 2 
    DeclaredConstructors: {System.Reflection.ConstructorInfo[1]} 
    DeclaredEvents: {System.Reflection.EventInfo[0]} 
    DeclaredFields: {System.Reflection.FieldInfo[2]} 
    DeclaredMembers: {System.Reflection.MemberInfo[33]} 
    DeclaredMethods: {System.Reflection.MethodInfo[23]} 
    DeclaredNestedTypes: {System.Reflection.TypeInfo.<get_DeclaredNestedTypes>d__23} 
    DeclaredProperties: {System.Reflection.PropertyInfo[7]} 
    DeclaringMethod: '((System.RuntimeType)cheekenrty.GetType()).DeclaringMethod' threw an exception of type 'System.InvalidOperationException' 
    DeclaringType: null 
    FullName: "System.Data.Entity.Infrastructure.DbQuery`1[[HalifaxWCFProject.tblUser, HalifaxWCFProject, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]" 
    GUID: {c4b5504f-41cb-377c-9215-465d09961eab} 
    GenericParameterAttributes: '((System.RuntimeType)cheekenrty.GetType()).GenericParameterAttributes' threw an exception of type 'System.InvalidOperationException' 
    GenericParameterPosition: '((System.RuntimeType)cheekenrty.GetType()).GenericParameterPosition' threw an exception of type 'System.InvalidOperationException' 
    GenericTypeArguments: {System.Type[1]} 
    GenericTypeParameters: {System.Type[0]} 
    HasElementType: false 
    ImplementedInterfaces: {System.Type[10]} 
    IsAbstract: false 
    IsAnsiClass: true 
    IsArray: false 
    IsAutoClass: false 
    IsAutoLayout: true 
    IsByRef: false 
    IsCOMObject: false 
    IsClass: true 
    IsConstructedGenericType: true 
    IsContextful: false 
    IsEnum: false 
    IsExplicitLayout: false 
    IsGenericParameter: false 
    IsGenericType: true 
    IsGenericTypeDefinition: false 
    IsImport: false 
    IsInterface: false 
    IsLayoutSequential: false 
    IsMarshalByRef: false 
    IsNested: false 
    IsNestedAssembly: false 
    IsNestedFamANDAssem: false 
    IsNestedFamORAssem: false 
    IsNestedFamily: false 
    IsNestedPrivate: false 
    IsNestedPublic: false 
    IsNotPublic: false 
    IsPointer: false 
    IsPrimitive: false 
    IsPublic: true 
    IsSealed: false 
    IsSecurityCritical: true 
    IsSecuritySafeCritical: false 
    IsSecurityTransparent: false 
    IsSerializable: false 
    IsSpecialName: false 
    IsUnicodeClass: false 
    IsValueType: false 
    IsVisible: true 
    MemberType: TypeInfo 
    MetadataToken: 33556295 
    Module (System.Reflection.MemberInfo): {EntityFramework.dll} 
    Module: {EntityFramework.dll} 
    Name: "DbQuery`1" 
    Namespace: "System.Data.Entity.Infrastructure" 
    ReflectedType: null 
    StructLayoutAttribute: {System.Runtime.InteropServices.StructLayoutAttribute} 
    TypeHandle: {System.RuntimeTypeHandle} 
    TypeInitializer: null 
    UnderlyingSystemType: {Name = "DbQuery`1" FullName = "System.Data.Entity.Infrastructure.DbQuery`1[[HalifaxWCFProject.tblUser, HalifaxWCFProject, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]"}

根据我的预期，我必须做出什么样的改变来实现它？

这里是数据库截图：

我已经问你5次同样的问题了。如果你拒绝回答我们所问的每个问题，真的很难帮助你。我会在这里明确。 '如果（cheekenrty.Equals（RetryAttempts））'做**没有做你认为它做**。如果你可以分享'cheekenrty.GetType（）'我可以告诉你为什么。 – mjwills

我chnages它cheekenrty.GetType（）。它显示以下错误.. ,,不能将system.type转换为bool @ mjwills – Mohammad

我在cheekentry中徘徊，我将鼠标悬停在上面，我花费了它。我得到了结果视图函数，我花费了它，它的显示错误unbale，以避免opeartion和操作符不被支持。最后，我把鼠标放在RetryAttempts上，它显示值4，然后敲击else语句 - Mohammad 10分钟前@ mjwills – Mohammad

答

使用Linq to Entities，您可以在if和chain中使用.Any()尽可能多的条件。

public bool AuthenticateUser1(UserLogin userLogin) 
{ 
    using (HalifaxDatabaseEntities db = new HalifaxDatabaseEntities()) 
    { 
     var Totalcount = 0; 
     int RetryAttempts = 4; 

     if (db.tblUsers 
      .Any(x => x.Username == userLogin.Username && 
        x.Password == userLogin.Password && 
        x.RetryAttempts <= RetryAttempts)) 
        { 
         return true; 
        } 

      TotalCount++; 
      return false; 
    } 
}

什么方法返回？ – Mohammad

任何返回布尔值 –

答

我注意到，你没有不像你的ADO.NET版本加密在LINQ查询您的密码。

string encryptedpassword = FormsAuthentication.HashPasswordForStoringInConfigFile(userLogin.Password, "SHA1"); 

var attamp = from X in db.tblUsers 
      where X.Username == userLogin.Username && X.Password == encryptedpassword 
      select X;

答

var attamp = from X in db.tblUsers 
      where X.Username == userLogin.Username && X.Password == userLogin.Password 
      select X; 

if (attamp != null) 
{ 
    var cheekenrty = from x in db.tblUsers 
        where x.RetryAttempts == RetryAttempts 
        select x; 

    if (cheekenrty.Equals(RetryAttempts)) 
    { 
     return exceeded; 
    } 
    else 
    { 
     return true; 
    }

上述逻辑并不做什么，你认为它。

例如：

attamp永远null
cheekenrty绝不会是一个int如你预期
cheekenrty.Equals(RetryAttempts)永远是false

我怀疑你想要做的例如：

，您可以看到这些方法返回true或flase。在那里我应该添加true和false语句@ mjwills – Mohammad

'return attamp.RetryAttempts> = RetryAttempts;'会这样做（返回true或false）。 – mjwills

一件事。如果用户输入worng用户名或密码，我想要统计用户输入的错误数量与varibale数量并存入数据库的次数。如果用户输入正确的用户名和密码与4 attampts，最大值计数将是4 – Mohammad

SQL Server ADO.NET查询转换为WCF服务中的Linq

相关推荐