We help IT Professionals succeed at work.

Help with my LINQ query

348 Views
Last Modified: 2012-02-13
Hello EE,

I have this LINQ that works well :

        Dim q = (From m In myTableValue _
                 Distinct
                 Select New With _
                 { _
                    .Key = m.Field(Of String)(bound.Rows(0)("Link_Table_Column_Name").ToString()).ToString(), _
                    .Value = m.Field(Of String)(bound.Rows(0)("Link_Table_Column_Name").ToString()) _
                 }).GroupBy(Function(p) p.Key).Select(Function(g) g.First()).ToList()

Open in new window


myTableValue is a DataTable.
My problem is that, the .Key and .Value gets a value but from now on, depending on another DataTable Results I might need to do this:

.Key = m.Field(Of String)(bound.Rows(0)("Link_Table_Column_Name").ToString()).ToString() + "-" + m.Field(Of String)(bound.Rows(1)("Link_Table_Column_Name").ToString()).ToString()

you See ? its dynamical. sometimes the key gets only 1 value lets say  "01"  but sometimes It might be "01-02"

it might even go as far as 3 values. (with 2 dashes)

How can I construct my Linq statement depending on the other dataTable results dynamically?  Can you help me ? I hope everything is understandable.
Comment
Watch Question

Commented:
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.

Author

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

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

Author

Commented:
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()

Open 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 ..
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
Try this:

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

Open in new window

Author

Commented:
What is accumulator and what is iterator ?

not sure to understand
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
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)

Open 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)

Open 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).

Author

Commented:
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()

Open in new window


? and eleminate the if ?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
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)

Open in new window


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

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
but when count is 2   it returns  " False "
The actual string "False"? Is the column you are selecting of type Boolean?

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
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)

Open in new window

Author

Commented:
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()

Open 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)
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
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.

Author

Commented:
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"
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
You're right it helps yes!

thank you Kaufmed
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.