With Dynamic LINQ, how can I dynamically choose my Table at runtime?

dynamic-linq linq linq-to-sql vb.net

Question

To enable engineers to do simple single table/view queries on our databases by choosing Database, Table, Fields, I'm creating an application.

I understand how to utilize the Dynamic LINQ Library Sample to enable runtime dynamic selection of the Select, Where, and Order by Clauses, but I'm stuck on how to allocate for table choice.

Is it possible to allow for dynamic selection of the "from" table during runtime? If so, could you please provide me an example or refer me to someone who has?

We are extremely grateful.


EDIT


Therefore, it seems that both responses are expressing the same main idea. I'll attempt to get it to work by converting the C# to VB.

The first response translates to

NotInheritable Class DataContextExtensions
    Private Sub New()
    End Sub
    <System.Runtime.CompilerServices.Extension> _
    Public Shared Function GetTableByName(context As DataContext, tableName As String) As ITable
        If context Is Nothing Then
            Throw New ArgumentNullException("context")
        End If
        If tableName Is Nothing Then
            Throw New ArgumentNullException("tableName")
        End If
        Return DirectCast(context.[GetType]().GetProperty(tableName).GetValue(context, Nothing), ITable)
    End Function
End Class

However, I keep getting an error message saying that Extension methods can only be specified in modules. However, even if I enclose it with module tags, the issue persists.

By enclosing it in Module Tags and removing the class tags, I was able to get it to build. Additionally, I can extract the last line from it and insert it right into my base function, enabling me to run it, but it seems to be returning nothing. There aren't any results when I attempt to enumerate them. I'll do further tests to see whether there is a problem with my codes or the new ones.


Here is how I converted the second example. I'm now heading out to attempt to make them function. I'll return after some testing with any queries or findings.

'get the table from a type (which corresponds to a table in your context)
Dim dataContextNamespace = "My.DataContext.Namespace"
Dim type = Type.[GetType](dataContextNamespace + tableName)
Dim table = dc.GetTable(type

'add where clauses from a list of them
For Each whereClause As String In whereClauses
    table = table.Where(whereClause)
Next

'generate the select clause from a list of columns
Dim query = table.[Select]([String].Format("new({0})"), [String].Join(",", selectColumns))

I appreciate the support. BBL

1
0
2/17/2011 8:03:53 PM

Accepted Answer

GetTable() may be used to get the matchingITable data you provide. After that, using DLINQ, it becomes quite simple.

The database AdventureWorks is used in this example. The context for my project is specified in theDatabaseTest gathering in theDatabaseTest.AdventureWorks namespace.

'' need my database and DLINQ extensions up top
Imports DatabaseTest.AdventureWorks
Imports System.Linq.Dynamic

'' sample inputs
Dim dc = New AdventureWorksDataContext()
Dim tableName = "Contact"
Dim whereClauses() = {"FirstName = ""John"" OR LastName = ""Smith"""}
Dim selectColumns() = {"FirstName", "LastName"}

'' get the table from a type (which corresponds to a table in your database)
Dim typeName = "DatabaseTest.AdventureWorks." & tableName & ", DatabaseTest"
Dim entityType = Type.GetType(typeName)
Dim table = dc.GetTable(entityType)
Dim query As IQueryable = table

'' add where clauses from a list of them
For Each whereClause As String In whereClauses
    query = query.Where(whereClause)
Next

'' generate the select clause from a list of columns
query = query.Select(String.Format("new({0})", String.Join(",", selectColumns)))

In hindsight, because you already know the name, employing reflection could have been the simpler approach to get the table. The names then may not match up exactly, so you'll need to make up for it.

Dim table As ITable = dc.GetType().GetProperty(tableName & "s").GetValue(dc, Nothing)
1
2/17/2011 10:46:21 PM

Popular Answer

Look at In LINQ DataContext, get table-data from table-name..

Using straight SQL commands to do this is usually preferable. LINQ will just obstruct your progress.

Conversion to VB:

NotInheritable Class DataContextExtensions
    Private Sub New()
    End Sub
    <System.Runtime.CompilerServices.Extension> _
    Public Shared Function GetTableByName(context As DataContext, tableName As String) As ITable
        If context Is Nothing Then
            Throw New ArgumentNullException("context")
        End If
            If tableName Is Nothing Then
                Throw New ArgumentNullException("tableName")
            End If
        Return DirectCast(context.[GetType]().GetProperty(tableName).GetValue(context, Nothing), ITable)
    End Function
End Class

Usage:

Dim myDataContext as New MyCustomDataContext
myDataContext.GetTableByName("ORDERS").Where("...")


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow