Background Color:
 
Background Pattern:
Reset
Monday, December 11, 2017
menu click
Search

LINQ Select items where an Item value is in a list.

Replicating the SQL statement Select * From TableA Where Field1 In (Select Field 1 From Table B)

Author: Ibrahim/Monday, August 19, 2013/Categories: Code Blog, VB Solutions

Rate this article:
5.0
Did you ever want to constrain a list  by values in another list using Linq?  Kind of like the IN clause in s SQL Select Statement. i.e. Select * From TableA Where Field1 In (Select Field 1 From Table B)

While there is no IN clause in Linq there are a variety of ways I have seen others do this. 

And of course :) I have my own VB.Net solution to this problem that was in answer to someone's question on an MSDN VB Forum.

The person asking the question was not being understood completely by the other responders so I asked a set of questions to get the requirements right before posing a solution.  It is always good to make sure you understand the problem before trying to solve it.

The Problem:

You have Rowset A with a Materials Field

You have Rowset B with a Materials Field

You want to create a Rowset C populated with rows from Rowset B where there is a matching Materials field in Rowset A

The desired result shown above.

The Solution:

First create a Winforms project and add three DataGidViews to the form.

In the code behind I created a class definition that represented the data

 

Public Class RowItem

    Sub New(Id As Integer, Item As String)
        Me.Indentifier = Id
        Me.Material = Item
    End Sub

    Property Indentifier As Integer
    Property Material As String

End Class


This provides an object that can be populated with the data and added to a list of type.  The original code provided by the person seeking a solution used DataTable objects.  I suggested to use this class definition instead so that as lists of type they could be manipulated using Linq.

I then created a function that took a RowItem as an argument and returned a boolean value based upon a rowitem in ListB having a match in ListA

 Function Test(X As RowItem) As Boolean
        Return (From Y In ListA Where Y.Material = X.Material).Any
    End Function

 The next step was to add the code that populated List A and B, then populated List C with the results of our Linq Query.

Finally we cause it all to happen in the Form's constructor:

 Private Sub CreateData()

        ListA.Add(New RowItem(Nothing, "AAA"))
        ListA.Add(New RowItem(Nothing, "BBB"))
        ListA.Add(New RowItem(Nothing, "AAA"))
        ListA.Add(New RowItem(Nothing, "BBB"))
        ListA.Add(New RowItem(Nothing, "AAA"))

        ListB.Add(New RowItem(Nothing, "AAA"))
        ListB.Add(New RowItem(Nothing, "BBB"))
        ListB.Add(New RowItem(Nothing, "CCC"))

        Dim Items = From DT In ListB Select DT Where Test(DT)

        If Items.Any Then
            ListC.AddRange(Items.ToList)
        End If

    End Sub

 

Here's all of the code in one place

 

Imports System.Linq
Imports System.Collections.ObjectModel

Public Class Form1

    Private ListA As New List(Of RowItem)
    Private ListB As New List(Of RowItem)
    Private ListC As New List(Of RowItem)

    Private Sub CreateData()

        ListA.Add(New RowItem(Nothing, "AAA"))
        ListA.Add(New RowItem(Nothing, "BBB"))
        ListA.Add(New RowItem(Nothing, "AAA"))
        ListA.Add(New RowItem(Nothing, "BBB"))
        ListA.Add(New RowItem(Nothing, "AAA"))

        ListB.Add(New RowItem(Nothing, "AAA"))
        ListB.Add(New RowItem(Nothing, "BBB"))
        ListB.Add(New RowItem(Nothing, "CCC"))

        Dim Items = From DT In ListB Select DT Where Test(DT)

        If Items.Any Then
            ListC.AddRange(Items.ToList)
        End If

    End Sub

    ''' 
    ''' Function returns true  when the RowItem type in argument
    ''' has a match in the dt1 rowset.
    ''' 
    ''' 
    ''' 
    ''' 
    Function Test(X As RowItem) As Boolean
        Return (From Y In ListA Where Y.Material = X.Material).Any
    End Function

    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        DataGridView1.DataSource = ListA
        DataGridView2.DataSource = ListB
        DataGridView3.DataSource = ListC
        CreateData()

    End Sub

End Class

Public Class RowItem

    Sub New(Id As Integer, Item As String)
        Me.Indentifier = Id
        Me.Material = Item
    End Sub

    Property Indentifier As Integer
    Property Material As String

End Class

Number of views (10053)/Comments (0)

Tags:

Please login or register to post comments.