Using the Linq IQueryable Toolkit

The IQToolkit provides you with a framework for building your own LINQ providers. The toolkit comes with providers for Microsoft SQL, Microsoft SQL CE, Access, MySQL, and SQLite.

This post will show you how to use the toolkit to talk to Microsoft SQL. Later, I’ll be attempting to write my own providers for DB2 and Interbase 7.1.

Download the toolkit
First thing to do is download the IQToolkit (version 0.16a as of this posting).

Reference the toolkit
I setup a console project to test the toolkit. Within your project, create a reference to the IQToolkit.Data.dll and the IQToolkit.dll. Then, add a reference to the dll for the provider you wish to use. In this case, we are using the IQToolkit.Data.SqlClient.dll for Microsoft SQL.

Table objects
Next, you will need to create a class object for each table you need to work with in the database. I have a table that has two fields: PLCID and SummaryProdMode. Create a class object to hold these properties and then use the Column attribute to bind the property to the table column.

Imports IQToolkit.Data.Mapping

Public Class Machine

    Private _id As Integer
    Private _mode As Boolean

    <Column(IsPrimaryKey:=True)> _
    Public Property PLCID() As Integer
        Get
            Return _id
        End Get
        Set(ByVal value As Integer)
            _id = value
        End Set
    End Property

    <Column()> _
    Public Property SummaryProdMode() As Boolean
        Get
            Return _mode
        End Get
        Set(ByVal value As Boolean)
            _mode = value
        End Set
    End Property

End Class

DataContext
The data context contains read only properties for each database table being used. In this case, I’m dealing with the DCM_Machines table.

Imports IQToolkit
Imports IQToolkit.Data.Mapping
Imports System.Linq

Public Class MachineDataContext

    Private _provider As IEntityProvider

    Public Property EntityProvider() As IEntityProvider
        Get
            Return _provider
        End Get
        Set(ByVal value As IEntityProvider)
            _provider = value
        End Set
    End Property

    <Table()> _
    Public ReadOnly Property Machines() As IEntityTable(Of Machine)
        Get
            Return EntityProvider.GetTable(Of Machine)("DCM_Machines")
        End Get
    End Property

    Public Sub New(ByVal provider As IEntityProvider)

        _provider = provider

    End Sub

End Class

Using the provider
Use the following code to setup and use the provider with LINQ.

Imports IQToolkit.Data

Module Module1

    Sub Main()

        Dim provider = DbEntityProvider.From( _
            "IQToolkit.Data.SqlClient", _
            "YOUR_CONNECTION_STRING", _
            "Machine")
        Dim db = New MachineDataContext(provider)

        Dim machs = (From m In db.Machines _
                    Select m).ToList()

        For Each m In machs

            Console.WriteLine("id=" & m.PLCID)

        Next

        Console.ReadLine()

    End Sub

End Module

You may also like...

5 Responses

  1. Jaggy says:

    Hello! First af all thanks for great tutorial!

    Is there a way to use IQToolkit to insert, update and delete?

    I tried to make this on my own but experiencing some problems:
    1) I made a code based on yours and i can make it insert in ms access db but, i have to provide a valid key otherwise – get error. Quiet a strange thing when thinking about insert – key must be provided automaticly.
    2) I cant delete from db – getting error somhere in depth of IQtoolkit. – i am not familiar with C# to figure it out by myself = (
    3) I can`t use “where” in query like Dim machs = From m In db.Machines where m.PLCID=something – get something like “not implemented exeption”. I can query “machs” of course.

    A have a feeling that i am doing something wrong but cant figure out what exactly. = )

  2. @Jaggy
    I’m sorry to say, I’ve not invested anymore time into this. I was using this for work and ended up going a different route.

    In the Machine class above, there may be another attribute you need to put on the primary key field (PLCID) to signify that it is an autonumber. Just by marking it as primary key it would think it must be required on inserts. In the case above, the PLCID would be a value I would want to set when inserting the record.

    If you’re using MS Access, you should be able to use Linq to talk directly to it. The Toolkit is really for creating providers to non-Microsoft databases (if one doesn’t already exist) that can talk to Linq.

    Check out C# when you get a chance. I much prefer it to VB, although I’ve spent many years with PHP so that may be why.

  3. kent says:

    Hello! I see you are attemping to write the DB2 provider for IQToolkit from this article. Have you finish it? I am looking for the provider for DB2 these days, but I can not find any information about it.

  4. @Kent
    No, I ended up scrapping this idea and using a custom library that I wrote to access DB2.

    It’s not as nice as using a tool like Linq, but there is another option. Look into SQL Linked Servers. You can link an SQL server to DB2 and then connect and run queries through SQL. This should allow you to use Linq with DB2.

  5. asava samuel says:

    Jackson

    This may help, here is Linq to SQLite.
    https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *