Introduction In this last post I want to show some more complex queries. But I also want to remind you that you need to know when to quit. When queries get to complex you will have to jump through hoops to get them working with any ORM while it is all very “easy” to do in SQL. In other words use the right tool for the job. Setup Here is the code to set everything up.

In the prior PetaPoco post, I started to dig into many-to-one relationships a little. Chrissie followed up with yet more mapping behavior in his latest Simple.Data post, so I thought I would cover it in a bit more detail. Note: Chrissie has also covered one-to-many since I wrote this post the other night and has at least one more post following that

Introduction In my previous post I described how to deal with complex types and many to one dependencies now I will do the one to many relationships. For this I will add a collection to our Person. Public Class Person Public Property LastName As String Public Property FirstName As String Public Property Address As Address Public Property BadHabits As IList(Of BadHabit) End Class Public Class Address Public Property Street As String Public Property HouseNumber As String End Class Public Class BadHabit Public Property BadHabit As String End Class``` ## one to many For this we first have to add a table to our database. ```vbnet Private Sub CreateTableBadHabits() Using cn = New SqlCeConnection(ConnectionString) If cn.State = ConnectionState.Closed Then cn.Open() End If Dim sql = "create table BadHabit (Id int IDENTITY(1,1) PRIMARY KEY, BadHabit nvarchar (40) not null, PersonId int)" Using cmd = New SqlCeCommand(sql, cn) cmd.ExecuteNonQuery() End Using sql = "ALTER TABLE BadHabit ADD CONSTRAINT FK_BadHabit_Person FOREIGN KEY (PersonId) REFERENCES Person(Id)" Using cmd = New SqlCeCommand(sql, cn) cmd.ExecuteNonQuery() End Using End Using End Sub``` We will better normalize it later when we have some time ;-). As you can see I also added the foreign key. I will leave the index creation up to you. And we also need some more testdata. ```vbnet 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}) db.BadHabit.Insert(New With {.BadHabit = "Drinks", .PersonId = 1}) db.BadHabit.Insert(New With {.BadHabit = "Smokes", .PersonId = 1}) db.BadHabit.Insert(New With {.BadHabit = "Eats to much", .PersonId = 2}) ``` And now for the querying. ```vbnet Dim result = db.Persons.FindAllByLastName("lastname1").WithAddress For Each person In result Console.WriteLine("{0} {1} {2} {3} {4}", person.Id, person.LastName, person.FirstName, person.Address.Street, person.Address.HouseNumber) For Each badHabit In person.BadHabits Console.WriteLine(badHabit.BadHabit) Next Next``` Dude, that’s simple… I guess that’s why they call it Simple.Data and not PetaPoco 😉 Of course the above still lazy loads and gives this SQL. ```tsql Simple.Data.Ado: Text select [Person].[Id],[Person].[LastName],[Person].[FirstName],[Person].[AddressId],[Address].[Id] AS [__with1__Address__Id],[Address].[Street] AS [__with1__Address__Street],[Address].[HouseNumber] AS [__with1__Address__HouseNumber] from [Person] LEFT JOIN [Address] ON ([Address].[Id] = [Person].[AddressId]) WHERE [Person].[LastName] = @p1 @p1 (String) = lastname1 Simple.Data.Ado: Text select [BadHabit].[Id],[BadHabit].[BadHabit],[BadHabit].[PersonId] from [BadHabit] WHERE [BadHabit].[PersonId] = @p1 @p1 (Int32) = 1 Simple.Data.Ado: Text select [BadHabit].[Id],[BadHabit].[BadHabit],[BadHabit].[PersonId] from [BadHabit] WHERE [BadHabit].[PersonId] = @p1 @p1 (Int32) = 2``` That again is not always a desired behaviour and in our case we just want to eagerly fetch our data. Which of course is stupidly simple to do just by adding the WithStatement like this. ```vbnet Dim result = db.Persons.FindAllByLastName("lastname1").WithAddress.WithBadHabits For Each person In result Console.WriteLine("{0} {1} {2} {3} {4}", person.Id, person.LastName, person.FirstName, person.Address.Street, person.Address.HouseNumber) For Each BadHabit In person.BadHabits Console.WriteLine(BadHabit.BadHabit) Next Next``` And now I get this sql instead. ```vbnet Simple.Data.Ado: Text select [Person].[Id],[Person].[LastName],[Person].[FirstName],[Person].[AddressId],[Address].[Id] AS [__with1__Address__Id],[Address].[Street] AS [__with1__Address__Street],[Address].[HouseNumber] AS [__with1__Address__HouseNumber],[BadHabit].[Id] AS [__withn__BadHabits__Id],[BadHabit].[BadHabit] AS [__withn__BadHabits__BadHabit],[BadHabit].[PersonId] AS [__withn__BadHabits__PersonId] from [Person] LEFT JOIN [Address] ON ([Address].[Id] = [Person].[AddressId]) LEFT JOIN [BadHabit] ON ([Person].[Id] = [BadHabit].[PersonId]) WHERE [Person].[LastName] = @p1 @p1 (String) = lastname1``` And that’s a lot better. You can however not do this. ```vbnet person.BadHabits(0).BadHabit``` Because that will give you this error. > No default member found for type ‘SimpleQuery’. I think that is because BadHabits is of type SimpleQuery and the default for SimpleQuery is an IEnumerator. But you can do this instead. ```vbnet person.BadHabits.ToList()(0).BadHabit``` ## Conclusion What can I say. It’s simple.

Introduction After doing the blogposts about One to many and [many to one][1] it is now time to do one one about many to many with Simple.Data. Many to many It’s time to change our design. It is clear that our data is not normalized like it should. I need an intermediate table between Person and BadHabit. Here is the code to do that. Private Sub CreateTables() Using cn = New SqlCeConnection(ConnectionString) If cn.State = ConnectionState.Closed Then cn.Open() End If For Each SqlScript In CreateTableScripts() Using cmd = New SqlCeCommand(SqlScript, cn) cmd.ExecuteNonQuery() End Using Next End Using End Sub Private Function CreateTableScripts() As IList(Of String) Dim s As New List(Of String) s.Add("CREATE TABLE Address (Id int IDENTITY(1,1) PRIMARY KEY, Street nvarchar (40) NOT NULL, HouseNumber nvarchar (10))") s.Add("CREATE TABLE Person (Id int IDENTITY(1,1) PRIMARY KEY, LastName nvarchar (40) NOT NULL, FirstName nvarchar (40), AddressId int NOT NULL)") s.Add("ALTER TABLE Person ADD CONSTRAINT FK_Person_Address FOREIGN KEY (AddressId) REFERENCES Address(Id)") s.Add("CREATE TABLE BadHabit (Id int IDENTITY(1,1) PRIMARY KEY, BadHabit nvarchar (40) NOT NULL)") s.Add("CREATE TABLE BadHabitPerson (BadHabitId int NOT NULL, PersonId int NOT NULL)") s.Add("ALTER TABLE BadHabitPerson ADD PRIMARY KEY(BadHabitId, PersonId)") s.Add("ALTER TABLE BadHabitPerson ADD CONSTRAINT FK_BadHabitPerson_Person FOREIGN KEY (PersonId) REFERENCES Person(Id)") s.Add("ALTER TABLE BadHabitPerson ADD CONSTRAINT FK_BadHabitPerson_BadHabit FOREIGN KEY (BadHabitId) REFERENCES BadHabit(Id)") Return s End Function``` And we have to add some data to make this work. ```vbnet 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}) db.BadHabit.Insert(New With {.BadHabit = "Drinks"}) db.BadHabit.Insert(New With {.BadHabit = "Smokes"}) db.BadHabit.Insert(New With {.BadHabit = "Eats to much"}) db.BadHabitPerson.Insert(New With {.BadHabitId = 1, .PersonId = 1}) db.BadHabitPerson.Insert(New With {.BadHabitId = 2, .PersonId = 1}) db.BadHabitPerson.Insert(New With {.BadHabitId = 3, .PersonId = 2}) You will now notice that our previous query no longer works.

Introduction In my [previous blogpost][1] about Simple.Data I added a table and made a reference to that in my previous table. In other words A person now has an address. I then went on to make a query to join the two together hereby using the explicit join syntax of Simple.Data. If however your database uses foreign keys (which I know it will) then you have no need for a join. Simple.Data just figures it out for itself.

In yesterday’s post A couple of things to be aware of when working with tables in SQL Azure I showed you some things to be aware of in regards to tables. Today let’s talk about how to rebuild an index Let’s say you have a table with a clustered index on a uniqueidentifier column, you know that you have to use newid() instead of newsequentialid() since newsequentialid() is not supported. Clustering with newid() is not recommended because you will get page splits. Let’s take a look at how we can recreate the index

I was messing around with SQL Azure today and noticed a couple of things that are not supported compared to the regular version of SQL Server in regards to tables. I will list these in this post. All the code has been tested against the following version of SQL Azure as returned by @@version Microsoft SQL Azure (RTM) – 11.0.1892.4 Apr 24 2012 10:21:54 Copyright (c) Microsoft Corporation 1) Tables have to have a clustered index if you want to insert data into those tables There is actually no problem creating a table without a clustered index (heap)

You can search for data in your tables by using the LIKE operator. The LIKE operator determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. Here is what Books On Line has on the use of wild cards Wildcard character <th> <p> Description </p> </th> <th> <p> Example </p> </th> </tr> <tr> <td> <p> % </p> </td> <td> <p> Any string of zero or more characters. </p> </td> <td> <p> WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title. </p> </td> </tr> <tr> <td> <p> _ (underscore) </p> </td> <td> <p> Any single character. </p> </td> <td> <p> WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on). </p> </td> </tr> <tr> <td> <p> [ ] </p> </td> <td> <p> Any single character within the specified range ([a-f]) or set ([abcdef]). </p> </td> <td> <p> WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation. </p> </td> </tr> <tr> <td> <p> [^] </p> </td> <td> <p> Any single character not within the specified range ([^a-f]) or set ([^abcdef]). </p> </td> <td> <p> WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l. </p> </td> </tr> Let’s take a look at some examples, first create this table and insert some data.

So yesterday Chrissie and I did posts on Simple.Data and PetaPoco. Today he followed up with more complex examples, including keys and multiple table queries. PetaPoco is built specifically with primary keys as a first class citizen, so it will be interesting to see how it compares. Adding a column Like Simple.Data, adding a column to our database table is no problem at all. First lets add the column to our database, then we’ll look at how that affects both our existing code and an updated POCO with a matching field.

Introduction So yesterday I did [a post about Simple.Data][1] and Eli did [a post about Petapoco][2] 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.