BirnBaumBlüte BirnBaumBlüte - 3 months ago 20
Vb.net Question

LINQ Dynamic List

I have classes:

Public Class Data

Public Property ColumnList As List(Of Column)
Public Property RowList As List(Of Row)
End Class

Public Class Row

Public Property CellList As List(Of Cell)
End Class

Public Class Column

Public Property name As String
End Class

Public Class Cell

Public Property value As Object
Public Property col As Column
End Class


Then I want to check if a List of columnnames would be a Primary Key (like in SQL) for the data, e.g.

ColumnFieldNames = {"FieldA", "FieldB"}


ColumnList
contains 3
Column
s named
FieldA
,
FieldB
,
FieldC
.
RowList
contains 3
CellList
s each filled with values for
FieldA
,
FieldB
,
FieldC
and a reference to the
Column
.

Data.ColumnList(0).name = "FieldA"
Data.ColumnList(1).name = "FieldB"
data.RowList(0).CellList(0).value = 1
data.RowList(0).CellList(0).column = ColumnList(0)
data.RowList(0).CellList(1).value = 2
data.RowList(0).CellList(1).column = ColumnList(1)
data.RowList(1).CellList(0).value = 34
data.RowList(1).CellList(0).column = ColumnList(0)
data.RowList(1).CellList(1).value = 2
data.RowList(1).CellList(1).column = ColumnList(1)


etc.

I'd like some kind of
group by ... having count(*) > 1

or
select count(1)
- Statement so if I would choose
FieldB
as
ColumnFieldNames
it would return something, as there are more than one row containing
CellList.value = 2
. If I would choose {"FieldA", "FieldB"} as
ColumnFieldNames
it would return nothing.

Answer

I take it the main objective is to check if a set of fields may represent a primary key on your data structure. In other words, if a set of fields may uniquely identify a row.

As FloatingKiwi pointed out, you'd better use the DataTable class. It provides you with a number of handy utilities to store and manipulate with your data. And your question may be answered by this simple code:

Private Function IsPK_DataTable(data As DataTable, ParamArray fields As String()) As Boolean
    Return New DataView(data).ToTable(True, fields).Rows.Count = data.Rows.Count
End Function

Here the data is first wrapped with a DataView object which allows us to pick up only selected columns from our data table (with ToTable(..., fields)) and only rows which are unique for these selected columns (by passing True as the first parameter to ToTable()). Now, if the resultign row count matches the initial one, the passed field set may be primary key on our data.

The full demo code can be found here: https://dotnetfiddle.net/6fhMgv


If you still inclined to use your custom data structure representation, the respective primary key check function may look like this:

Private Function IsPK_CustomData(data As Data, ParamArray fields As String()) As Boolean
    Dim keyValues = data.RowList _
        .[Select](Function(r) r.CellList.Where(Function(c) fields.Contains(c.col.name)) _
        .[Select](Function(c) c.value).ToArray()).ToArray()
    For i As Integer = 1 To keyValues.Length - 1
        For j As Integer = i - 1 To 0 Step -1
            If keyValues(i).SequenceEqual(keyValues(j)) Then
                Return False
            End If
        Next
    Next
    Return True
End Function

Full demo: https://dotnetfiddle.net/RDl5Nl