locked
Is there a way to get a link query to return a row from a table? RRS feed

  • Question

  • User2130491911 posted

    So I am trying to access a database table that has a field that I need to match and if there is a match I need to select data out a field in  that table. I have tried and few things and I am coming up with nothing. 

    So here is what I have so far

        Function GetCurrentUserEmail() As String
    
            'Get Current System Client Info
            'Check xxxxx View to get the User's Email 
    
            Dim CurrentUser As String = System.Web.HttpContext.Current.Request.LogonUserIdentity.Name
           
    'Dim Users As VUE_xxxxxx_EMPLOYEE_DIVISONS = XXXdb.VUE_xxxxx_EMPLOYEE_DIVISONS.SingleOrDefault(Function(u) u.Network_ID.Contains(CurrentUser)) 'Dim UserEmail = Users.Email.ToString() Dim UserEmail = From Users In XXXdb.VUE_XXXX_EMPLOYEE_DIVISONS Where Users.Network_ID = "%" & CurrentUser & "%," Select Users.Email Dim Test = UserEmail.ToString() Return Test End Function

    What I would like to have is a Sql "Like" selection 

    So if the Current User matches the network ID then I want to select that users email address from the table and send it back to the 

    function that called it. 

    I find link a bit confusing as there is more than one way to wright a query but there is never giving any reasons why you would use the lambda thing over the from option.

    Same with where.  I thought contain would work but that seems like a useless option.

    But any help or guidance would be most appreciated! Thanks

    Friday, May 28, 2021 9:55 PM

Answers

All replies

  • User475983607 posted

    But any help or guidance would be most appreciated! Thanks

    The .NET equivalent to the SQL LIKE operator is Contains().

    Dim UserEmails As List(Of String) = (From Users In XXXdb.VUE_XXXX_EMPLOYEE_DIVISONS
                                         Users.Network_ID.Contains(CurrentUser)
                                         Select Users.Email).ToList()

    The official LINQ docs cover the basics.  There's a lot to read and learn.

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/

    Saturday, May 29, 2021 11:00 AM
  • User1120430333 posted

    Assuming there is only one record with the user's email address, this should work.

    Dim user = XXXdb.VUE_xxxxx_EMPLOYEE_DIVISONS.Where(Function(u) u.Network_ID.Contains(CurrentUser)).SingleOrDefault()

    If Not IsNothing(user)
    dim email = user.email
    EndIf

    If there is more than one record with the email address record,
    then you use FirstOrDefault() that returns thethe first occurrence.

    I don't see how there would be more than one record for a given user-id.
    So shouldn't it be u.Network.ID.Tostring() == currentuser with using SingleOrDefault()?


    Saturday, May 29, 2021 2:13 PM
  • User2130491911 posted

    Hi DA924 and thanks mgebhard!

    So my CurrentUser looks like this "NOV\H7221435"

    The list that is being being searched looks like 

    • H083838
    • H7221435      <- What needs to be matched
    • H722231
    • G8399409

    Will contain work to the second Item in the list ?

    Tuesday, June 1, 2021 2:17 PM
  • User475983607 posted

    Hi DA924 and thanks mgebhard!

    So my CurrentUser looks like this "NOV\H7221435"

    The list that is being being searched looks like 

    • H083838
    • H7221435      <- What needs to be matched
    • H722231
    • G8399409

    Will contain work to the second Item in the list ?

    The Contains() method finds a string within a string as explained in the docs; https://docs.microsoft.com/en-us/dotnet/api/system.string.contains?view=net-5.0.

    It sounds like your actual question is how to extract H7221435 from NOV\H7221435???  If so, that's just a simple Split(); https://docs.microsoft.com/en-us/dotnet/api/system.string.split?view=net-5.0.

    Dim username As String = "NOV\H7221435"
    Dim user = username.Split("\")(1)



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 1, 2021 2:38 PM
  • User2130491911 posted

    Thanks that split option allow the contains to work. Thanks!!

    Wednesday, June 2, 2021 7:32 PM
  • User1120430333 posted

    Thanks that split option allow the contains to work. Thanks!!

    The Split() populated dim user-id with just the user-id from string dropping everything before the user-id in the string. Then you could use  Where(ID == user-id, becuase you would have gotten the hit and not needed to use Contains doing a search in the string. To another developer looking at the code, it would seem rather confusing as to why you were using Contains().

    Wednesday, June 2, 2021 9:12 PM