Help with my LINQ query

you needto add a Where statement to your code, but your question is obscure since we have no knowledge of the structure of your tables, or the criteria for the "other" dependent table.

Yes I could do If count = 1 then  (build my Linq)

elseif 2 then (build with 1 dash...)


but if it changes all the time I cannot just go change my If statement..
thats waht I am facing as a problem

Well the other DataTable will return values

if total count is 1   the .Key  will get only the bound.Rows(0)("Column")

if 2   then bound.Rows(0)("Column") + "-" + bound.Rows(1)("Column")

etc.


my quesiton is how to build that without IF statement since the count may vary

Is this Linq-to-Sql or Linq-to-Objects (e.g. DataTable)?

myTableValue  is a DataTable



the "other" dataTable wit the count is this :

        Dim col As IEnumerable(Of DataRow)
        col = From c In myTable.AsEnumerable()
                   Where c.Field(Of String)("Link_Table_Cd") = master.SelectedItem.Key _
                   And c.Field(Of Integer)("Prompt_YN") = 1
                   Select c
        Dim bound As DataTable = col.CopyToDataTable()

Select allOpen in new window

then I would do :

If bound.Rows.Count = 1 Then
else if...

elseif ...
but this is exactly what I dont want to do because the count can change in the future..and I dont want to go all the time in the code to change it ..

Try this:

Dim result = bound.AsEnumerable() _
                  .Select(Function(row) row.Field(Of String)("Column")) _
                  .Aggregate(Function(accumulator, iterator) accumulator = accumulator & "-" & iterator)

Select allOpen in new window

What is accumulator and what is iterator ?

not sure to understand

Ah, yes I can see how that is confusing--it certainly was for me the first time I used it  = )

The names are arbitrary, but I intentionally named them that way for demonstrative purposes. The Aggregate extension method is used to return one overall result from a collection of items. The first parameter for the lambda function is a variable which will hold this overall values--and it will hold the intermediate "totals" along the way. The second parameter to Aggregate represents the current collection item being evaluated. Let me give you an example.

Take the following integer list:

Dim ints As New List(Of Integer)

ints.Add(1)
ints.Add(2)
ints.Add(3)
ints.Add(4)
ints.Add(5)

Select allOpen in new window

If I aggregated over that list using an addition operation as the aggregate function, then I could get an overall sum of the entire list. That might look like this:

Dim result = ints.Aggregate(Function (accum, iter) accum += iter)

Select allOpen in new window

In this example, when the Aggregate method starts, the accum variable is initiated to the first value in the list--1. The first iteration of the summation will show accum with a value of 1, and the iter variable will show 2. The second iteration of the summation will show accum with a value of 3, and the iter variable will show 3. The third iteration of the summation will show accum with a value of 6, and the iter variable will show 4. And so on...

In my previous post, instead of a summation operation, we are using a string concatenation operation. Each subsequent iteration of the lambda function appends the current row's (the iterator variable's) value to the overall result (including the hyphen delimiter).

SI I would do with result something like this ? :

        Dim q = (From m In myTableValue _
         Distinct
         Select New With _
         { _
            .Key = result, _
            .Value = result _
         }).GroupBy(Function(p) p.Key).Select(Function(g) g.First()).ToList()

Select allOpen in new window

? and eleminate the if ?

That example was intended to follow your earlier code:

Dim col As IEnumerable(Of DataRow)
col = From c In myTable.AsEnumerable()
           Where c.Field(Of String)("Link_Table_Cd") = master.SelectedItem.Key _
           And c.Field(Of Integer)("Prompt_YN") = 1
           Select c
Dim bound As DataTable = col.CopyToDataTable()

Dim result = bound.AsEnumerable() _
                  .Select(Function(row) row.Field(Of String)("Column")) _
                  .Aggregate(Function(accumulator, iterator) accumulator = accumulator & "-" & iterator)

Select allOpen in new window

I don't particularly fancy the multiple Linq operations, but I figured it would be the easiest for you to visualize.

its working with count  = 1

it returns the good value when I  do MessageBox.Show(result)

but when count is 2   it returns  " False "

mmm maybe there is an error that you didnt see?

but when count is 2   it returns  " False "

The actual string "False"? Is the column you are selecting of type Boolean?

no, when  count is 1 it was : the only row in bound wich is row(0) returns   "Salesman_Cd"

count 2 :   the 2 only rows in bound wich is row(0) should returns "Rept_ID" and row(1) "Rept_Class"

but I see only "False" wich doesnt make sense

My fault. Remove the "accumulator = " part:

Dim col As IEnumerable(Of DataRow)
col = From c In myTable.AsEnumerable()
           Where c.Field(Of String)("Link_Table_Cd") = master.SelectedItem.Key _
           And c.Field(Of Integer)("Prompt_YN") = 1
           Select c
Dim bound As DataTable = col.CopyToDataTable()

Dim result = bound.AsEnumerable() _
                  .Select(Function(row) row.Field(Of String)("Column")) _
                  .Aggregate(Function(accumulator, iterator) accumulator & "-" & iterator)

Select allOpen in new window

Ok its almost working. I did this :

        Dim q = (From m In myTableValue _
         Distinct
         Select New With _
         { _
            .Key = result, _
            .Value = result _
         }).GroupBy(Function(p) p.Key).Select(Function(g) g.First()).ToList()

Select allOpen in new window

when count is 2 I have all my .Key   with good value like  "01-02"

01 is ReptID and 02 is Rept_Class

but when count is 1 the result is "Salesman_Cd"   it should be like "Peter" you know

maybe you code Select aggregate doesnt like when bound count is 1 ? (no dashes is needed)

Can you provide an example of what the tables might look like? I'm having trouble visualizing your issue. You mention "01-02" as a good result, but you then say that with a single row you get something like "Salesman_CD". I would have expected you to get something like "01" (i.e. no dash). It sounds as though a different column is being selected, but I can't see why that would be happening based on the code you posted. Perhaps if I can see your table structure I can deduce why you get this behavior.

The values don't have to be exactly the same, but it would be helpful if the types of each column were.

ok myTable has the following:

Cd       Desc              Link_Table_Column_Name    Prompt

W1        Salesman       Salesman_Cd                         1
W2        Retail                Rept_ID                             1
W2        Retail                Rept_Dept                        1


so myTableValue is filled with :  Select * from  the good "Desc" value depending of the Cd I pass.

So if I pass  W1  it will be  select * from Salesman   and in this table I have

Salesman_Cd    Name

01                     Kaufmed

if W2:  Select * from Retail   and in this table I have:

Retail_ID     Retail_Dept
01                  02


so when doing your Linq code.. yes me too I would have think with Salesman that I would have only 01   but I receive "Salesman_Cd"

when Retail  I receive "01-02"

You're right it helps yes!

thank you Kaufmed