1

I'm having trouble with this Linq query. I have a data table (including a Name column) with all the data I want to process. I want to do a Linq query to this data table, but I want to exclude names also found in myNegativeList where amount >= 15.

myNegativeList has (Name = "John"; Amount = 5) John should not be excluded from the Linq Query. myNegativeList also has (Name = "Sally"; Amount = 100) Sally should be excluded from the Linq Query.

Class ListItems
    Public Name As String
    Public Amount As Decimal
End Class

Sub GetList()

    'get data table
    Dim NoticeTable As DataTable = GetTable 'has Name and other data

    'get my list of names I don't want
    Dim myNegativeList As List(Of ListItems) = getMyList

    'Psuedo code here 
    Dim Cust = From Notice In NoticeTable _
               Where Notice.Name not in (Select Name from myList where Amount >= 15)

End Sub

How do I do a Linq query excluding names (existing in myNegativeList AND amount >= 15)

D_Bester
  • 5,723
  • 5
  • 35
  • 77

2 Answers2

1

Try this:-

Dim result = From dr In NoticeTable.Rows
                 Where Not myNegativeList.Any(Function(x) x.Amount >= 15 AndAlso x.Name = dr("Name"))
                 Select dr("Name")

I am not sure why .Net Fiddle is not supporting DataTable, but you can copy the code i have tried from Here.

Rahul Singh
  • 21,585
  • 6
  • 41
  • 56
  • If I'm not mistaken the Linq Join is an inner join that would exclude most of my data records from the data table. Only a few appear in the myNegativeList. – D_Bester Nov 11 '14 at 10:56
  • @D_Bester - Yes, I misunderstood your question! I thought you want to filter only those names from dataTable which are present in negative list, Anyways have updated the code. Please check and you can check the sample data I have used in Fiddle. – Rahul Singh Nov 11 '14 at 12:51
  • This is great! I used `Select dr` instead of `Select dr("Name")` because I actually wanted the entire data row. Thanks a lot. – D_Bester Nov 12 '14 at 02:22
  • I checked out your fiddle and saw the error message asked for a reference to `System.XML`. I added it and the error was gone. – D_Bester Nov 12 '14 at 04:49
  • @D_Bester - Okay Thanks for that! Got confused cz in .Net DataTable is present in `System.Data` namespace. – Rahul Singh Nov 12 '14 at 06:00
1

Try and do it like this:

Dim Cust  = From row In NoticeTable.AsEnumerable() _ 
           Let name = row.Field(Of String)("Name") _ 
           Where Not myNegativeList.Where(Function(c) c.Amount >= 15 ) _ 
                                   .Any(Function(c) c.Name = name) _
           Select row

Look here for another example on how to do Linq with DataTable

Community
  • 1
  • 1
Jens Kloster
  • 11,099
  • 5
  • 40
  • 54