Introduction
So yesterday I did a post about Simple.Data and Eli did a post about Petapoco based on what I did. And Eli commented that I did not have a primary key in my table.
In this post I will add that primary key and another table.
Adding a column
Adding a column should never be a big problem. But with Simple.Data it’s just no pain what so ever. Most ORM’s will require you to add a property to your model and then something to the mapping file. With Simple.Data the column is just available.
If I change the create table function to this.
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))"
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.
Chris is awesome.
