CODEWITHSUNDEEP
vb.netlinqdatatable
03Usr
03Usr

Reputation: 3435

Querying datatable.AsEnumerable with LINQ

I have a data table which I query to determine if a certain row exists, there are a few possible scenarios:

Rule 1:

Dim rt1 As EnumerableRowCollection(Of Double) = From row In dtCh.AsEnumerable() _
Order By row.Field(Of Int64)("ID") Descending
Where (row.Field(Of String)("L_TYPE") = "A" _
And row.Field(Of Int16)("Customer_Type") = 1)
Select row.Field(Of Double)("Price")

If rt1.Any() Then
    return CType(rt1.FirstOrDefault(), Decimal)
End If

Rule 2:

Dim rt2 As EnumerableRowCollection(Of Double) = From row In dtCh.AsEnumerable() _
Order By row.Field(Of Int64)("ID") Descending
Where (row.Field(Of String)("L_TYPE") = "B" _
And row.Field(Of Int16)("Customer_Type") = 0)
Select row.Field(Of Double)("Price")

If rt2.Any() Then
    return CType(rt2.FirstOrDefault(), Decimal)
End If

and there are 2 more rules, if I have a row returned for rule one, I use the price returned from the first query, if nothing has been returned from the first query then I move on to the second rule and use the price from the second one and move on to the third and fourth one if necessary...

But this seems a bit of a long winded way, I know all the possible scenarios and in which order I wanted to check the scenarios, is there any way of combining these and find out the price with one query?

Thanks

Upvotes: 1

Views: 5992

Answers (1)

Dave Williams
Dave Williams

Reputation: 2246

It's not 100% clear from your question but it seems you are assuming that there will only be one row corresponding to any given parameters e.g A1, B0 etc.

In your query you are using any() to determine if the list contains any elements and then trying to return Single() which will only work if there is only one element, so why are you using an Enumerable?

It would be better to look for the first item that corresponds to your condition and put your conditions in the order you want e.g

dtCh.AsEnumerable().OrderBy(Function(Row) Row.Field(Of Int64)("ID")).First(Function(Row) _  
(Row.Field(Of String)("L_TYPE") = "A" And Row.Field(Of Int16)("Customer_Type") = 1) Or _

(Row.Field(Of String)("L_TYPE") = "B" And Row.Field(Of Int16)("Customer_Type") = 0)).Price

EDIT: Ok I didn't quite get what you are looking for. I don't know if it is possible to query multiple times in one statement but I have one solution I just tried which works. It may not be to everyones taste but I quite like it. (Wish I knew how to indent and line space in code blocks?!)

Dim Query = dtCh.AsEnumerable().OrderBy(Function(x) x.Id)

Dim Conditions = 
{
    Function(Row) Row.Field(Of String)("L_TYPE") = "A" And _
    Row.Field(Of Int16)("Customer_Type") = 1,
    Function(Row) Row.Field(Of String)("L_TYPE") = "B" And _
    Row.Field(Of Int16)("Customer_Type") = 0
}.ToList()

For Each Condition In Conditions
    Dim Price = Query.FirstOrDefault(Condition)
    If Price IsNot Nothing
        Price.Price 'Get your price here.
        Exit For
    End If
Next

Upvotes: 2

Related Questions