I am trying to determine how to use the TypedView where method and I am not having any success. (Net 3.5)

I am filling a typed view with a list of all the fields in a database (including the table name the fields are in). When the user selects a table from a UI drop down I want to filter the list to only those fields for that table.

I created a view in the DB to provide the list of fields.

I can load the view with all of the fields but am having great difficulty determining how to filter the list based on the table name.

in a sub to filter I am starting with: (sTable is passed in)

Dim filter As IPredicate = (VwTablePropertiesFields.TableName = sTable)

I have 2 Typed Views defined:

_ecFields (contains all of the fields and table names) _ecFieldsFiltered

How do I filter the typed view?

Thanks

You should filter the typedView's fields. Please read this.

So, you somehow (hashtables, or something like that) must discover the name of the field you want to filter, then use it on typedView's fields to build your filter.

Thanks for your reply.

Yes I did see that section in the help file. Perhaps I should explain in a bit more detail.

I have a grid with many rows. On each row I want to provide a method to identify a table and (in a column) and a field (in another column) to map the data from that row to (this is setup instructions for where data will be written into a database. So the column with the tables names (combobox) will provide the user with a list of all available tables. The user will select a table from the list and I want to filter the next column (fields) to display ontl the fields available for the selected table. When the user goes on to the next row (there can be 100's of rows), they can map to a different table and field combination.

Thus I thought I could load all of the field names (and table names) at one time in a TypedView since it is coming from a View in the database. Then after each table is selected I would just filter the main view to only display the rows appropriate for the table selected.

Maybe I am not approaching this best.

I saw that the typed view has a where method on it but I could not get it to work nor did I find any examples of using it.

Thus my question on this forum.

Thus I thought I could load all of the field names (and table names) at one time in a TypedView since it is coming from a View in the database. Then after each table is selected I would just filter the main view to only display the **rows **appropriate for the table selected.

If I understand you correctly, I guess you ment to filter the columns displayed not to filtre the rows....right?

This can't be done in plain SQL, can it? if you know the SQL query you want to execute, please post it.

The Grid is bound to an Entity: (DocumentListQuestionId, DocumentListId, DocumentQuestionId, SequenceNumber, RuleHeaderId, ColumnNumber, ParentDocumentListQuestionId, QuestionHint, MapTable, MapField, MapTableType, DataFormat, QuestionValidationTypeId)

The Column "MapTable" is a varchar and is bound to a TypedView which is a list of all tables in the database (this does not need to be filtered). [SELECT TOP (100) PERCENT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published, lob_data_space_id, filestream_data_space_id, max_column_id_used, lock_on_bulk_load, uses_ansi_nulls, is_replicated, has_replication_filter, is_merge_published, is_sync_tran_subscribed, has_unchecked_assembly_data, text_in_row_limit, large_value_types_out_of_row FROM sys.tables ORDER BY name]

The column "MapField" is bound to a different TypedView which contains all of the fields in the database as well as a column for the table to which it belongs.

[SELECT TOP (100) PERCENT OBJECT_NAME(c.id) AS table_name, c.name, t.name AS data_type, c.isnullable, com.text AS default_text, c.length, c.prec AS numeric_precision, c.scale AS numeric_scale, c.colorder FROM sys.syscolumns AS c LEFT OUTER JOIN sys.systypes AS t ON c.type = t.type AND c.xtype = t.xtype LEFT OUTER JOIN sys.syscomments AS com ON com.id = c.cdefault ORDER BY table_name, c.name, c.colorder]

After the user selects a table name (MapTable), I want to filter the MapField to only the fields for that table (for this row of the Grid). If no table is selected then no fields show up. I am only concerned about the current row and will not "limit to list" all of the rows.

The sql for the filter will thus look like this:

Where table_name = 'MyTableName' or Dim filter As IPredicate = (VwTablePropertiesFields.TableName = sTable) as a predicate.

Thanks

Please can you post the actual code you are using to construct the filter and load the view?

Also can you post the generated SQL query that is actually run when you run your code.

Thanks

Matt

The Sub ControlAdd is the method used to load my data and perfrom any binding.

Friend _eCol As New EntityCollection(Of DocumentListQuestionEntity)(New DocumentListQuestionEntityFactory())
Friend _eQuestions As New EntityCollection(Of DocumentQuestionEntity)(New DocumentQuestionEntityFactory())
Friend _eDocs As New EntityCollection(Of DocumentListEntity)(New DocumentListEntityFactory())

Friend _eBase As DocumentListQuestionEntity
Friend _eDelete As New EntityCollection(Of DocumentListQuestionEntity)()
Friend _DocListId As Integer
Friend _ecTables As New VwTableNamesTypedView
Friend _ecFields As New VwTablePropertiesTypedView

'Dim _ecFields As New ITypedView2(Of VwTablePropertiesRow)

Friend _ecFieldsFiltered As New VwTablePropertiesTypedView

Public Overrides Sub ControlAdd() _IsStartUp = True Dim sorter As New SortExpression(DocumentQuestionFields.QuestionText Or SortOperator.Ascending)

    _Adapter.FetchEntityCollection(_eQuestions, Nothing, 1000, sorter)

    sorter.Clear()
    sorter = New SortExpression(DocumentListFields.Name Or SortOperator.Ascending)
    _Adapter.FetchEntityCollection(_eDocs, Nothing, 1000, sorter)

‘=================== ‘Grid Binding ‘=================== Me.DocumentQuestionEntityBindingSource.DataSource = _eQuestions Me.DocumentListEntityBindingSource.DataSource = _eDocs Me.DocumentListEntityBindingSource1.DataSource = _eDocs

    Me.QuestionValidationId.DataSource = _EnumCache.GetList("QuestionValidationType")
    Me.QuestionValidationId.DisplayMember = "Name"
    Me.QuestionValidationId.ValueMember = "QuestionValidationTypeId"

    sorter.Clear()

‘=================== ‘Table Names and Binding ‘=================== sorter = New SortExpression(VwTableNamesFields.Name Or SortOperator.Ascending) _Adapter.FetchTypedView(_ecTables.GetFieldsInfo(), _ecTables, Nothing, 0, sorter, True)

    Me.MapTable.DataSource = _ecTables
    Me.MapTable.DisplayMember = "Name"
    Me.MapTable.ValueMember = "Name"

    sorter.Clear()

‘=================== ‘Field Names and Binding ‘=================== sorter = New SortExpression(VwTablePropertiesFields.Name Or SortOperator.Ascending) _Adapter.FetchTypedView(_ecFields.GetFieldsInfo(), _ecFields, Nothing, 0, sorter, True)

    Me.MapField.DataSource = _ecFields
    Me.MapField.DisplayMember = "Name"
    Me.MapField.ValueMember = "Name"

    _eCol.RemovedEntitiesTracker = _eDelete
    _IsStartUp = False
End Sub

Private Sub GetFilteredQuestions()
    Try

        _eCol.Clear()
        Dim filter As IPredicate = (DocumentListQuestionFields.DocumentListId = _DocListId)

        Dim bucket As IRelationPredicateBucket = New RelationPredicateBucket()
        bucket.PredicateExpression.Add(filter)

        _Adapter.FetchEntityCollection(_eCol, bucket)
        Me.DocumentListQuestionEntityBindingSource.DataSource = _eCol
        Me.DocumentListQuestionEntityBindingSource1.DataSource = _eCol

    Catch ex As Exception

    End Try
End Sub

**The method _FilterTableFields _is the one I want to use to filter the previously loaded typed list "_ecFields. The following code is not complete. I was trying to use the "Where" method but am not sure how to use it.**

Private Sub FilterTableFields(ByVal sTable As String)
    ' VB.NET

    Dim filter As IPredicate = (VwTablePropertiesFields.TableName = sTable)
    Dim iPred As System.Func(Of VwTablePropertiesRow, Boolean)


    _ecFieldsFiltered.Where(iPred)
    'Dim bucket As IRelationPredicateBucket = New RelationPredicateBucket()
    'bucket.PredicateExpression.Add(filter)
    _ecFields.


End Sub

you need to refetch the typed view from the database, along the lines of

            RelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.Add(NamesAndAddressesFields.FirstName == "Matt");
            NamesAndAddressesTypedView tv = new NamesAndAddressesTypedView();
            da.FetchTypedView(tv, bucket, true);

Matt

I know I can do that but that is not what I am wanting to do. If I have 1000 rows in the grid, then I could conceivably be making 1000 calls to the typed view.

I want to load the typedview one (1) time with all of the data. Then I want to display a filtered subset of the data depending on the users selection for the mapped table.

I was trying to use the "Where" method for a typed list, but I cant find any examples of how to use it. I suspect the "Where" method will work out best for me.

Any examples of how to use it?

In memory filtering is not available on TypedViews.

If you load the data you want as an EntityCollection rather than a typed view, you can project it to an IEntityView2 and use the .Filter property to filter it In-Memory.

MTrinder wrote:

In memory filtering is not available on TypedViews.

To be more specific: it is possible, but using DataView filtering, as a typedview is a datatable. So LLBLGen Pro specific predicates aren't usable on a typedview.

Hi,

To filter a TypedView you can do following (I implemented this in my code and it is works fine)

tvGetMenuByUserIdTypedView menuOptions;

options.DefaultView.RowFilter = "fieldname= '" + value.ToString() + "'"; menuOptions = options;

// Do what you need to do with menuOptions, it contain only the rows filtered.

Happy Coding!