Private Sub CreateTablePerson()
        Using cn = New SqlCeConnection(ConnectionString)
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            Dim sql = "create table Person (Id int IDENTITY(1,1) PRIMARY KEY, LastName nvarchar (40) not null, FirstName nvarchar (40))"
            Using cmd = New SqlCeCommand(sql, cn)
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub```
I can now simply change my code to read the Id.

```vbnet
Sub Main()
        CreateDatabase()
        CreateTablePerson()
        Dim db = Simple.Data.Database.OpenConnection(ConnectionString)
        db.Person.Insert(New With {.LastName = "lastname1", .FirstName = "firstname1"})
        db.Person.Insert(New With {.LastName = "lastname1", .FirstName = "firstname2"})
        Dim result = db.Persons.FindAllByLastName("lastname1")
        For Each person In result
            Console.WriteLine("{0} {1} {2}", person.Id, person.LastName, person.FirstName)
        Next
        Dim count = db.Persons.FindAllByLastName("lastname1").Count
        Console.WriteLine("Count: " & count)
        Console.ReadLine()
    End Sub```
So adding a column is not a problem.

## Adding a table

So lets change our Person table a bit and add an address table.

```vbnet
Private Sub CreateTablePerson()
        Using cn = New SqlCeConnection(ConnectionString)
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            Dim sql = "create table Person (Id int IDENTITY(1,1) PRIMARY KEY, LastName nvarchar (40) not null, FirstName nvarchar (40), AddressId int not null)"
            Using cmd = New SqlCeCommand(sql, cn)
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

    Private Sub CreateTableAddress()
        Using cn = New SqlCeConnection(ConnectionString)
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            Dim sql = "create table Address (Id int IDENTITY(1,1) PRIMARY KEY, Street nvarchar (40) not null, HouseNumber nvarchar (10))"
            Using cmd = New SqlCeCommand(sql, cn)
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub```
And now I can change my main method to this.

```vbnet
Sub Main()
        CreateDatabase()
        CreateTableAddress()
        CreateTablePerson()
        Dim db = Simple.Data.Database.OpenConnection(ConnectionString)
        db.Address.Insert(New With {.Street = "street1", .HouseNumber = "1"})
        db.Person.Insert(New With {.LastName = "lastname1", .FirstName = "firstname1", .AddressId = 1})
        db.Person.Insert(New With {.LastName = "lastname1", .FirstName = "firstname2", .AddressId = 1})
        Dim result = db.Persons.FindAllByLastName("lastname1")
        For Each person In result
            Console.WriteLine("Person: {0} {1} {2}", person.Id, person.LastName, person.FirstName)
            Dim address = db.Address.FindById(person.AddressId)
            Console.WriteLine("Address: {0} {1}", address.Street, address.HouseNumber)
        Next
        Dim count = db.Persons.FindAllByLastName("lastname1").Count
        Console.WriteLine("Count: " & count)
        Console.ReadLine()
    End Sub```
in the above I am doing it all wrong of course since I am creating a query to the address table for each of the rows in the persons. I think they call that a select N+1. I could and should do all this with one query.

And I can, kinda. But there is a catch.

First I will show you what works in C#

```csharp
var result = db.Persons.Query()
                .Join(db.Address).On(db.Persons.AddressId == db.Address.Id)
                .Select(db.Persons.LastName, db.Persons.FirstName, db.Address.Street, db.Address.HouseNumber);
            foreach(var person in result)
            {
                Console.WriteLine("Person: {0} {1}", person.LastName, person.FirstName);
                Console.WriteLine("Address: {0} {1}", person.Street, person.HouseNumber);
            }```
And that gives this SQL

```tsql
select [Person].[LastName],[Person].[FirstName],[Address].[Street],[Address].[HouseNumber] from [Person] JOIN [Address] ON ([Person].[AddressId] = [Address].[Id])```
So no more Select N+1. Oh joy.

Now we try the same thing in VB.Net.

```vbnet
Dim result = db.Persons.Query _
                .Join(db.Address).On(db.Persons.AddressId = db.Address.Id) _
                .Select(db.Persons.LastName, db.Persons.FirstName, db.Address.Street, db.Address.HouseNumber)
        For Each person In result
            Console.WriteLine("Person: {0} {1}", person.LastName, person.FirstName)
            Console.WriteLine("Address: {0} {1}", person.Street, person.HouseNumber)
        Next```
And grumble, grumble, curse, curse, swearword, swearword. It doesn’t work. It should, but it doesn’t. No really it doesn’t.

You get this errormessage.

> Public member ‘Address’ on type ‘Database’ not found.

But I swear it should work. 

So an hour or so later and trying other things. I get an idea.

```vbnet
Dim address = db.Address
        Dim result = db.Persons.Query _
                .Join(address).On(db.Persons.AddressId = address.Id) _
                .Select(db.Persons.LastName, db.Persons.FirstName, address.Street, address.HouseNumber)
        For Each person In result
            Console.WriteLine("Person: {0} {1}", person.LastName, person.FirstName)
            Console.WriteLine("Address: {0} {1}", person.Street, person.HouseNumber)
        Next```
And all of a sudden it works. I have yet to find a reason why the VB compiler doesn’t like it but I will look into it further when I have some time.

## Conclusion

You can add column easily and you can do joins quite easily. VB.Net however doesn’t seem to like it directly but there is a work around.

 [1]: /index.php/DesktopDev/MSTech/simple-data-and-vb-net
 [2]: /index.php/DesktopDev/MSTech/CSharp/playing-with-petapoco