LINQ Dynamic List

Question

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 Columns named FieldA, FieldB, FieldC. RowList contains 3 CellLists 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.

Dmitry Egorov · Accepted Answer · 2016-08-10 07:47:26Z

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

Community · Accepted Answer · 2017-05-23 11:44:19Z

1

You seem to be reinventing the wheel here. The DataTable class can handle your class structure already and supports sql like filter expressions. For the grouping try checking out the following post: Efficient DataTable Group By

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Aug 10, 2016 at 2:57

FloatingKiwi

4,5161 gold badge20 silver badges46 bronze badges

1 Comment

15ee8f99-57ff-4f92-890c-b56153 Over a year ago

The old wheel was too round.

Collectives™ on Stack Overflow

LINQ Dynamic List

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related