none
SQL to LINQ conversion RRS feed

  • Question

  • I have this SQL Statement:

    SELECT tblEmployees.EmpID, tblEmployees.EmpName, tblPositions.PositionName, tblTeams.TeamName
    FROM tblTeams RIGHT JOIN (tblEmployees INNER JOIN tblPositions ON tblEmployees.EmpID = tblPositions.AssignedEmployeeID) ON tblTeams.TeamID = tblPositions.AssignedTeamID
    WHERE ((Not (tblEmployees.EmpID) = [lngID]));

    Here is the LINQ version(I think)

     Dim query = From Team In dtTeams
                        Join Emp In dtEmp
                        Join Pos In dtPostion
                        On Emp.Field(Of String)("EmpID") Equals Pos.Field(Of String)("AssignedEmployee")
                        On Team.Field(Of String)("TeamID") Equals Pos.Field(Of String)("AssignedTeam")
                        Where ((Not (Emp.Field(Of String)("EmpID") = strEmpID)))
                        Select
                            {
                                EmployeeID = Emp.Field(Of String)("EmpID"), _
                                EmployeeName = Emp.Field(Of String)("EmpName"), _
                                PositionName = Pos.Field(Of String)("PositionName"), _
                                TeamName = Team.Field(Of String)("TeamName")
                            }
            For Each q In query
                Debug.Print("EmployeeID = " & EmployeeID & vbCrLf & _
                            "EmployeeName = " & EmployeeName & vbCrLf & _
                             "PositionName = " & PositionName & vbCrLf & _
                             "TeamName = " & TeamName)
            Next

    The program throws the exception:

    Can some tell me what I did wrong?


    MRM256

    Saturday, November 3, 2018 3:51 PM

Answers

  • I saw the error message but the code in the code block didn't have q. anywhere. I then noticed something funky in your code so here is a suggested change and note I did this in notepad so unsure if it's fully correct but should be.

    • Removed several variables
    • Add New With to the select.
    • Prepended properties with a dot for anonymous type properties
    Public Function lst_LINQ_By_EmpID(ByRef dtEmp As DataTable, _
                                         ByRef dtTeams As DataTable, _
                                         ByRef dtPostion As DataTable, _
                                         ByVal strEmpID As String) As DataTable
    
            Dim query = From Team In dtTeams.AsEnumerable
                        Join Emp In dtEmp.AsEnumerable
                        Join Pos In dtPostion.AsEnumerable
                        On Emp.Field(Of String)("EmpID") Equals Pos.Field(Of String)("AssignedEmployee")
                        On Team.Field(Of String)("TeamID") Equals Pos.Field(Of String)("AssignedTeam")
                        Where ((Not (Emp.Field(Of String)("EmpID") = strEmpID)))
                        Select New With
                            {
                                .EmployeeID = Emp.Field(Of String)("EmpID"), _
                                .EmployeeName = Emp.Field(Of String)("EmpName"), _
                                .PositionName = Pos.Field(Of String)("PositionName"), _
                                .TeamName = Team.Field(Of String)("TeamName")
                            }
            For Each q In query
                Debug.Print("EmployeeID = " & q.EmployeeID & vbCrLf & _
                            "EmployeeName = " & q.EmployeeName & vbCrLf & _
                             "PositionName = " & q.PositionName & vbCrLf & _
                             "TeamName = " & q.TeamName)
            Next
            Return query
        End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by MRM256 Sunday, November 4, 2018 3:15 PM
    Sunday, November 4, 2018 3:08 PM
    Moderator

All replies

  • you have not posted all of the exception message. you need to go into View Detail and copy, paste and post the exception message.
    Saturday, November 3, 2018 4:52 PM
  • Thank you,

    I don't know how much help it will be I could not See the entire screen.

    Thanks,


    MRM256

    Saturday, November 3, 2018 5:28 PM
  • Looks like you missed (and this is from viewing only) that you are not qualifying properties in the for-each e.g. EmployeeId should be q.EmployeeId etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, November 3, 2018 10:16 PM
    Moderator
  • I don't know how much help it will be I could not See the entire screen.

    Note that each line in the View Details , it can be highlighted with the mouse pointer/click  and a copy of the content of the line can be copied and pasted.

    I don't even see how the code even compiled, since the foreach is not addressing an object property addressed by 'q'.

    You see, becuase you can do a copy/paste of the exception message line in View Detail, you can copy the line to Google or Bing search engine to find articles about others that most likely have encountered the same error and what they did to possibly fix the problem.

    Saturday, November 3, 2018 10:44 PM
  • Hi Karen,

    I know your suggestion was based on a visual inspection, but when I tried your solution I got this error.

    The variables are still nothing. For giggles I will post my entire function to see it some light comes on in some other room :-).

     Public Function lst_LINQ_By_EmpID(ByRef dtEmp As DataTable, _
                                         ByRef dtTeams As DataTable, _
                                         ByRef dtPostion As DataTable, _
                                         ByVal strEmpID As String) As DataTable
            'Purpose:       Creates a DataTable using LINQ
            'Parameters:    dtEmp As DataTable - Employees DataTable
            '               dtTeams As DataTable - Teams DataTable
            '               dtPostion As DataTable - Postions DataTable
            '               strEmpID As String - Search Key
            'Returns:       A DataTable containing the results of the query
            Dim EmployeeID As String
            Dim EmployeeName As String
            Dim PositionName As String
            Dim TeamName As String
            'Dim dt As New DataTable
            'Use EmpID as search term
            Dim query = From Team In dtTeams.AsEnumerable
                        Join Emp In dtEmp.AsEnumerable
                        Join Pos In dtPostion.AsEnumerable
                        On Emp.Field(Of String)("EmpID") Equals Pos.Field(Of String)("AssignedEmployee")
                        On Team.Field(Of String)("TeamID") Equals Pos.Field(Of String)("AssignedTeam")
                        Where ((Not (Emp.Field(Of String)("EmpID") = strEmpID)))
                        Select
                            {
                                EmployeeID = Emp.Field(Of String)("EmpID"), _
                                EmployeeName = Emp.Field(Of String)("EmpName"), _
                                PositionName = Pos.Field(Of String)("PositionName"), _
                                TeamName = Team.Field(Of String)("TeamName")
                            }
            For Each q In query
                Debug.Print("EmployeeID = " & EmployeeID & vbCrLf & _
                            "EmployeeName = " & EmployeeName & vbCrLf & _
                             "PositionName = " & PositionName & vbCrLf & _
                             "TeamName = " & TeamName)
            Next
            Return query
        End Function

    There is no documentation telling mew how to convert a SQL String into a LINQ function.


    MRM256

    Sunday, November 4, 2018 1:38 PM
  • This is what I meant when indicating you need to reference properties.

    For Each q In query
        Debug.Print("EmployeeID = " & q.EmployeeID & vbCrLf & _
                    "EmployeeName = " & q.EmployeeName & vbCrLf & _
                     "PositionName = " & q.PositionName & vbCrLf & _
                     "TeamName = " & q.TeamName)
    Next


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, November 4, 2018 2:03 PM
    Moderator
  • Karen,

    I did this. Did you miss the error message I inserted in my post?

    Thanks,


    MRM256

    Sunday, November 4, 2018 2:07 PM
  • Karen,

    For completeness I have a screen shot of your modification and the Error List that VS generates when I try to run it.

    Thanks,


    MRM256

    Sunday, November 4, 2018 2:19 PM
  • I saw the error message but the code in the code block didn't have q. anywhere. I then noticed something funky in your code so here is a suggested change and note I did this in notepad so unsure if it's fully correct but should be.

    • Removed several variables
    • Add New With to the select.
    • Prepended properties with a dot for anonymous type properties
    Public Function lst_LINQ_By_EmpID(ByRef dtEmp As DataTable, _
                                         ByRef dtTeams As DataTable, _
                                         ByRef dtPostion As DataTable, _
                                         ByVal strEmpID As String) As DataTable
    
            Dim query = From Team In dtTeams.AsEnumerable
                        Join Emp In dtEmp.AsEnumerable
                        Join Pos In dtPostion.AsEnumerable
                        On Emp.Field(Of String)("EmpID") Equals Pos.Field(Of String)("AssignedEmployee")
                        On Team.Field(Of String)("TeamID") Equals Pos.Field(Of String)("AssignedTeam")
                        Where ((Not (Emp.Field(Of String)("EmpID") = strEmpID)))
                        Select New With
                            {
                                .EmployeeID = Emp.Field(Of String)("EmpID"), _
                                .EmployeeName = Emp.Field(Of String)("EmpName"), _
                                .PositionName = Pos.Field(Of String)("PositionName"), _
                                .TeamName = Team.Field(Of String)("TeamName")
                            }
            For Each q In query
                Debug.Print("EmployeeID = " & q.EmployeeID & vbCrLf & _
                            "EmployeeName = " & q.EmployeeName & vbCrLf & _
                             "PositionName = " & q.PositionName & vbCrLf & _
                             "TeamName = " & q.TeamName)
            Next
            Return query
        End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by MRM256 Sunday, November 4, 2018 3:15 PM
    Sunday, November 4, 2018 3:08 PM
    Moderator
  • Karen,

    Thanks that fixed the LINQ query problem.

    One silly question: Can a LINQ query be converted into a DataTable?

    Thanks,


    MRM256

    Sunday, November 4, 2018 3:17 PM
  • Although I am using a Linq projection using a known type while you use an anaymous type projection,  the use of the '_ '  to continue a statment over lines is not needed. So your Linq query statment is not correct in that regards.

     Public Function GetProjectsByUserId(ByVal userid As String) As List(Of DtoProject) Implements IDaoProject.GetProjectsByUserId
    
            Dim dtos = New List(Of DtoProject)
    
            dtos = (From a In context.Projects.Where(Function(a) a.UserId.Contains(userid))
                    Select New DtoProject With {.ProjectId = a.ProjectId,
                                                .ClientName = a.ClientName,
                                                .ProjectName = a.ProjectName,
                                                .Technology = a.Technology,
                                                .ProjectType = a.ProjectType,
                                                .UserId = a.UserId,
                                                .StartDate = a.StartDate,
                                                .EndDate = a.EndDate,
                                                .Cost = a.Cost}).ToList()
           
    
            Return dtos
    
        End Function

    Sunday, November 4, 2018 3:23 PM
  • Use this language extension

    Module OtherExtensions
        <DebuggerStepThrough()>
        <Runtime.CompilerServices.Extension()>
        Public Function ToDataTable(Of T)(ByVal sender As IEnumerable(Of T)) As DataTable
    
            Dim dt As New DataTable
            Dim fieldNameRow = sender.First
    
            For Each pi In fieldNameRow.GetType.GetProperties
                dt.Columns.Add(pi.Name, pi.GetValue(fieldNameRow, Nothing).GetType)
            Next
    
            For Each result In sender
                Dim nr = dt.NewRow
                For Each pi In result.GetType.GetProperties
                    nr(pi.Name) = pi.GetValue(result, Nothing)
                Next
                dt.Rows.Add(nr)
            Next
    
            Return dt
    
        End Function
    End Module

    Example

    Public Class Person
        Public Property Id() As Integer
        Public Property Name() As String
    End Class

    Demo

    Dim people As New List(Of Person) From
            {
                New Person() With {.Id = 1, .Name = "Karen Payne"},
                New Person() With {.Id = 2, .Name = "Zack Payne"}
            }
    Dim dt = people.ToDataTable()


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, November 4, 2018 3:29 PM
    Moderator
  • Karen,

    After I got the LINQ function in the VB.NET application to work properly I converted it into C#, and the translation dropped something vital.

    Here is the functioning VB.NET code:

    Dim query = From Team In dtTeams.AsEnumerable
                Join Emp In dtEmp.AsEnumerable
                Join Pos In dtPostion.AsEnumerable
                On Emp.Field(Of String)("EmpID") Equals (Pos.Field(Of String)("AssignedEmployee"))
                On Team.Field(Of String)("TeamID") Equals Pos.Field(Of String)("AssignedTeam")
                Where ((Not (Emp.Field(Of String)("EmpID") = strEmpID)))
                Select New With
                     {
                          .EmployeeID = Emp.Field(Of String)("EmpID"), _
                          .EmployeeName = Emp.Field(Of String)("EmpName"), _
                          .PositionName = Pos.Field(Of String)("PositionName"), _
                          .TeamName = Team.Field(Of String)("TeamName")
                      }

    This one works in my VB application.

    Here is the C# translation.

    var query = from Team in dtTeams.AsEnumerable
                join Emp in dtEmp.AsEnumerable on Team.Field<string>("TeamID") 
    	    equals (Pos.Field<string>("AssignedTeam"))
                where ((!(Emp.Field<string>("EmpID") == strEmpID)))
                    select new
                    {
                        EmployeeID = Emp.Field<string>("EmpID"),
                        EmployeeName = Emp.Field<string>("EmpName"),
                        PositionName = Pos.Field<string>("PositionName"),
                        TeamName = Team.Field<string>("TeamName")
                    };

    This code line in the VB function is missing from the C# version.

    Join Pos In dtPostion.AsEnumerable

    Without this line the LINQ function fails in C#.

    Can you help?

    Thanks,


    MRM256

    Monday, November 5, 2018 3:39 PM
  • Karen,

    After I got the LINQ function in the VB.NET application to work properly I converted it into C#, and the translation dropped something vital.

    Here is the functioning VB.NET code:

    Dim query = From Team In dtTeams.AsEnumerable
                Join Emp In dtEmp.AsEnumerable
                Join Pos In dtPostion.AsEnumerable
                On Emp.Field(Of String)("EmpID") Equals (Pos.Field(Of String)("AssignedEmployee"))
                On Team.Field(Of String)("TeamID") Equals Pos.Field(Of String)("AssignedTeam")
                Where ((Not (Emp.Field(Of String)("EmpID") = strEmpID)))
                Select New With
                     {
                          .EmployeeID = Emp.Field(Of String)("EmpID"), _
                          .EmployeeName = Emp.Field(Of String)("EmpName"), _
                          .PositionName = Pos.Field(Of String)("PositionName"), _
                          .TeamName = Team.Field(Of String)("TeamName")
                      }

    This one works in my VB application.

    Here is the C# translation.

    var query = from Team in dtTeams.AsEnumerable
                join Emp in dtEmp.AsEnumerable on Team.Field<string>("TeamID") 
    	    equals (Pos.Field<string>("AssignedTeam"))
                where ((!(Emp.Field<string>("EmpID") == strEmpID)))
                    select new
                    {
                        EmployeeID = Emp.Field<string>("EmpID"),
                        EmployeeName = Emp.Field<string>("EmpName"),
                        PositionName = Pos.Field<string>("PositionName"),
                        TeamName = Team.Field<string>("TeamName")
                    };

    This code line in the VB function is missing from the C# version.

    Join Pos In dtPostion.AsEnumerable

    Without this line the LINQ function fails in C#.

    Can you help?

    Thanks,


    MRM256

    This qualifies for a completely new post. Then once done I will help.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 5, 2018 4:41 PM
    Moderator