In the first part of this series I went over how to create a basic skeleton of controllers and models so that we could create a mock test and get it green lighted along with some other tests for getting the basic navigation methods tested and green lighted.  In this part of the series I'm going to go over the model from a LINQ point of view.  After that in subsequent parts of this series I'll start building out the views, utilizing of course a test driven development process.  So no more blabbering, let's code.

LINQ is definitely going to change the world of development for a lot of people.  Maybe not so much for people who have been using generated OR/M software for a while.  The idea of actual business objects and linking business logic onto the actual data in a database isn't particularly new, but it is ridiculously useful when writing code.  In addition there are dozens of other benefits too; reduced ramp up time for developers on the data schema, less meddling by the coders in the DBA's space, and the list continues.

So what I'm going to go over now includes setting up some real model data, schema, and LINQing it to the actual model classes.  I won't really do everything in good practice right off as I am initially just going to get it to work.  I'm still unfamiliar enough using LINQ that I'm not even 100% sure yet what good practice is using this particular OR/M.  After that in some subsequent sections I will then apply additional good practice and probably add more unit tests.

First thing to do is setup a schema like the one I've displayed in the image below.  Click on the image to get a full size original of the image.

Once you've created that image then we'll setup a view that will be used for displaying the correlated data all together.  To do this I created a view based on the select statement below.

SELECT       
    Trip.TripId,
    Purpose.Purpose,
    [Type Of Trip].TripType,
    Trip.Name,
    Trip.Notes,
    Trip.Mileage,
    Trip.DateOfTrip,
    Origination.FullName AS Origination,
    Origination.AbbreviationCode AS OriginationAbbreviation,
    Desination.FullName AS Destination,
        Desination.AbbreviationCode AS DestinationAbbreviation
FROM            dbo.tt_Trips AS Trip
    INNER JOIN    dbo.tt_TripPurpose AS Purpose ON Trip.PurposeId = Purpose.PurposeId
    INNER JOIN    dbo.tt_TripTypes AS [Type Of Trip] ON Trip.TypeId = [Type Of Trip].TypeId
    LEFT OUTER JOIN    dbo.tt_Locations AS Origination ON Trip.OriginationLocationId = Origination.LocationId
    LEFT OUTER JOIN dbo.tt_Locations AS Desination ON Trip.DestinationLocationId = Desination.LocationId

Once the view is created then add a new LINQ dbml file to your project.  The image below shows the LINQ to SQL file which I've named Transportation.dbml. 

When the file is added click on the plus beside the file and you'll see we also have the Transportation.dbml.layout and Transportation.designer.cs files.  What you see displayed when you open the .dbml file is actually the .layout file.  The layout file is where you will drag and design you objects derived from the database.

Now open up the server explorer and the database you've created the schema in.  Now select all of the tables and views, then drag and drop them into the Transportation.dbml layout page.

When you're done you should have a diagram representation as displayed below.  These are now the objects that you have for your model.

Before moving on to the tests, do a quick rebuild all just to make sure all the pieces are still working.

If any errors pop up feel free leave a comment.  I ran into a few fiddling around with all of this and could easily help out.

Once you have your entities created we're ready to test these to make sure we have good in and out with the actual database.  The following example isn't using any of the fancy unit testing frameworks or mocking, what I'm going to do - since I too am still getting familiar with the LINQ technology pieces - is literally to write out some tests that do clean inserts, updates, and deletes.  With that said, lets get going.

First off for the testing project I had to add the following using statements to the test class.

using System.Linq;
using MvcApplication;
using MvcApplication.Models;

I had to also add the linq reference also since it isn't included by default in the test framework.  The Models namespace I had to add also.   The MvcApplication part was added when I created the solution back in the first part of this series.

The first test I wrote is for testing the location, with a really straight forward get of a single item, if it doesn't exist I create the object and stick it in the database.  Finally the last part has an assertion to verify the proper objects are the same.

[TestMethod]
public void CheckInsertAndDataForLocationEntity()
{
    TransportationDataContext transportationDb = new TransportationDataContext(@"Data Source=ADRON-PC\SQLEXPRESS;Initial Catalog=transitTracker;Integrated Security=True;Pooling=False");

    tt_Location location = transportationDb.tt_Locations.Single(l => l.FullName == "Portland");

    if (location.FullName != "Portland")
    {
        location.FullName = "Portland";
        location.AbbreviationCode = "PDX";
        location.Note = "PDX is the airport and railroad station code for Portland, Oregon.";
        location.LocationId = Guid.NewGuid();
        transportationDb.tt_Locations.InsertOnSubmit(location);
        transportationDb.SubmitChanges();
    }

    tt_Location getPortland = transportationDb.tt_Locations.Single(l => l.FullName == "Portland");

    Assert.AreEqual(location, getPortland);
}

Note that the context object (transportationDb) has the "SubmitChanges()" method on it.  The "InsertOnSubmit(entity)" is located on the static object of the entity type located on the context object.  This seems kind of illogical at first, but it works.  I've seen a few other ways that an insert like this is done, but I'm not sure which is truly a preferred way to do it.  I'm sure in the very near future though I'll either figure out the preferred way or find some material written up about it.

At this point I wrote a few more tests, but since they're crude as the one above, I'll leave them for the reader to build themselves.  I've downloaded mbUnit though to do some nice mocking tests and have started looking at some ways to unit test LINQ with mocks so it can be a really solid test layer.

So stay tuned for part three where I'll go over the creation of the views.  Once I'm done with the views it will then be time to head off into the integration world and get all this loosely coupled directly injected inversion of control stuff to work together.

Ah, interesting and positive coverage on Silverlight.

A friend of mine recently asked, what would be the ideal dream job/work scenario.  I started thinking about it and created this list (Which I'll be pointing him to).  I thought it would be an entertaining post, so here are the things I came up with.  Please comment with ideas and notions of what would make your dream job/work scenario.

• 30-45 minutes a day for blogging & getting the days thoughts together.
• 1-1 1/2 hour lunch with a proper 45 minute to hour long siesta afterwards.  Returning to work for the home run stretch.
• Pair development Agile style.  Switching on and off, with stories and the like.  Maybe experiment with the flexible nature of the development style of different developers.  Change pairs at least daily.  Work on reasonable daily work scopes, don't let the work lag day to day by assigning tasks to large to complete in a pair session.
• Downtown PDX Location or otherwise a town center location (Beaverton, Hillsboro, tigard, Gresham?? maybe not Gresham, Hawthorne, Belmont, Mississippi Ave, Alberta, etc).  It adds dramatically to the vitality and creativeness of the developers and their respective solutions.
• Friday telecommute for the morning and bulk of the day.  Then meet up for beer/food/beverage of choice day.  Meet about 3 or 4pm or whenever happy hour starts at a designated place that the team chooses.
• Assure everyone has a fairly decent laptop.  Not a desktop, there should be no reason to chain someone to a desk for development.  I've seen it happen and the laptop allows for a much more relaxed and conducive work tool.  Sometime of course, someone might prefer a desktop, someone might even need an extra hardware kick - which are perfectly good examples.  But a laptop should still be available too.
• The team needs to all be in the top % of their respective income brackets.  If they aren't, the disparity, when learned or guessed or figured out - which always seems to happen, pushes people out of teams, increases turnover, and causes all types of animosity.  Especially when the market is good and there are all those other upper bracket percentages available.
• The team needs to be social enough to pull off an effective Agile process, and it helps a huge amount if the team actually gets along on activities outside of work.  Most people that are developers today don't have too much of an issue with this, especially once everyone gets to know each other and the pair programming kicks in.

That's just some ideas.  So what else would ya'll out there in the world have for ideas?  Beer on Friday, Playstation 3, X-Box 360, foosball table, Olympic swimming pool, jet planes, rocket ships, moon landings?   ...whoops, got a little carried away.

I've been working toward a more and more proactive testing, or test driven development, approach to my work.  With some of my recent BI/OLAP work it has been a bit more difficult, as code isn't always what needs to be written, sometimes it is just some scripts or ETL processes.  However even these things can benefit from TDD awesomeness!

I however wanted to point out some that Haack pointed out on Research Supports the Effectiveness of TDD and TDD is Also an Organizational Process.  Both of these things, the first I knew would be forthcoming as Haack did, and the later something that I have often discussed with various colleagues.

Now that he's pointed it out I'm definitely going to have to push harder on new methods for implementing tests against more obtuse environments (such as BI/OLAP Development) and of course keep staying up to speed on the various rock and roll tools such as mbUnit and the various testing techniques.

With that in mind, I think I'll have to add a few mbUnit and TDD based how to entries to the ole' blog along with part 2 of my ASP.NET MVC how to.  Stay tuned!

Alright, so maybe it isn't a dozen, but it sure seems like that a lot of the time.  I was just sitting and going through some old ADO.NET style connection and command creation programmatically, then turned around on another project and started tossing some tables and views into a LINQ Diagram.  I couldn't help but think, "Wow, after all these years of .NET, there are now a ton of data access patterns and even numerous data connection patterns, classes, and methods.  I decided then, I'd do a quick enumeration of all the connections, data access patterns, and other such methodologies I've used to connect to various data sources.

The first methods I ever used where what was available with ole' .NET 1.0.  Actually, it wasn't even 1.0, it was the beta product.  One would instantiate a basic connection object, then instantiate a command object passing the stored procedure name and connection object just created as parameters to the instantiation.  From there I would then end up assigning the various parameters of the actual stored procedure.  There had been a few different ways that this would occur.  Sometimes all with one line, instantiation, assignment of the value, or sometimes I would end up breaking it out into multiple lines.  In yet another scenario I would have to instantiate the parameter objects first, so I would do that and then do assignments.  With the basic beta and 1.0 ADO.NET, one could easily spend dozens of ways connecting to data.

The Wade Reynolds way was rather interesting.  He was the senior developer (more like architect) at SCP Pool Corp while I was there.  He had created an entire self creating OR/M with reflection.  We'd call basic procedures and stuff via the procedure, and the parameters and everything would then be available for us to just assign to.  Overall it was a nice setup.  At the core it was using ADO.NET, but one wouldn't have been wiser for not realizing it.

The third type I remember was my whole sale stumbling full on into OR/M, data mapper, and the tools that generated these layers and objects.  I had worked at creating some business layers and such myself in the past, but an individual developer under tight deadlines really doesn't have a lot of spare time to churn out entire extra layers.  With the newly found tools, I started creating much more advanced, maintenance friendly, more business enabled applications at this point.  These generation tools really wiped the slate clean for the headache that the database usually was.

Now we've got LINQ, which I guess one could say is an OR/M or at least the engine that drives the entity library which is an OR/M.  I look back at the headaches of keeping things in and in sync with a database and it seems in the last couple years.  Over the last couple of years there has been a drastic simplifying drive, while drawing more business functionality and logic into the usage and creation of the database and the related objects used to keep the database.

I for one, am happy with the modern direction of data access and the clarification of the business elements.

I've been hearing a lot of discussion about cardinality this and cardinality that. With the topic becoming such a common point of discussion. I decided I would write up something.

First some definitions so that we all have a clear cut idea of what cardinality actually is.  Sometimes cardinality isn't fully understood at a basic level.  With some of the points I'm going to touch on a full understanding of the basis of cardinality will be needed.

Cardinality : 1. Cardinality is part of formal set theory.  A cardinal number is a type of number defined in such a way that any method of counting sets using it gives the same result.  2. Cardinality is a notion of the size of a set which does not rely on numbers.  It is a relative notion.  For instance, two sets may each have an infinite number of elements, but one may have a greater cardinality.

The second definition is the one that I will discuss further.  I must note, that it is odd that even before anyone is confused, the definitions are in opposition with each other.  Cardinality does or does not include numbers, both specifically defined by use.  For a little more information, check my previous entry Tip o' The Day.

Now on to some meat of this topic.  When designing cubes and planning the various dimensions of data, cardinality is a key concept that must be taken into account when doing so.  High cardinality high count items make bad dimensions, high cardinality low count and low cardinality low count items make great dimensions. Now before digressing and grumbling about how this might be a stupid statement, hear me out further.

High Count, High Cardinality Data Sets

If you have a large count data set that has no limits, and a high cardinality this is either a questionable data set to turn into a dimension.  These types of data sets; e-mail addresses, user names, or other high cardinality items are not good data sets to use as dimensions.  Sometimes there may be exceptions, but rarely would that be the case.  Generally when there is high cardinality, counts, or other measures and facts are the candidates for these data sets.

High Count, Low Cardinality Data Sets

These data sets usually make a decent dimension because of the ability to pare them down to a set numbers of unique values.  Keep in mind, that whatever a dimension is slicing into, should be visibly readable.  An example would be business departments, or business zones.  Each business department might have thousands or even millions of data points, but when a distinct value is derived from the set of data the cardinality is low enough that one ends up with a low number of actual unique items, making slicing much easier for the people viewing the actual reports.

Mix and Match of High Count and Cardinality Degrees

In both cases above, high count, low or high cardinality, often data sets can be stuck into either category.  Take the birthday of a user database for example.  There are 365 possible birthdays per year, not a good way to slice data.  But if you break it down to just the month or year, you end up with 12 months or x number of years.  This is a perfect example of something to use for a dimension.

On the same note one might have user data, but then have the user data of a particular department noted.  If the user count and user information could be derived and rolled up via the department the break out of user departments into a dimension makes sense.

There are dozens of other ways to look at data.  One of the interesting ideas I heard recently of a high count, high cardinality data set was e-mails.  The e-mails for a particular set where being tracked.  A user wanted to know where the domain of the e-mails where originating from, which individually wasn't something you'd want a Cube Processing to have to go through.  So instead of trying to derive the origination of each e-mail we had in the data set, we pared the data down to purely just the domain, removing the actual user name part of the e-mail.  From there we where able to pare down the e-mail domain originations into a clear and discernable dimension.

So when it comes to cardinality there can always be more than meets the eye.  Take a second look at high count data sets to make sure they're really high count, sometimes they can be pared down to reasonable amounts of data.

I often get into debates when a group or department attempts to implement Agile, then does a half ass job, then complains about how Agile failed.  One of the issues is exactly what Ross Pettit wrote about in his Agile Journal Article, Mythical Agile Shortcuts.

These myths that he outlines are only the tip of the ice berg.  There are dozens of others that can sink, or stifle a project.  So if you're on an Agile Project, learn these, and avoid them like the management anti-patterns they are.

Just a few Index 101 type points of advice.

When creating an index on an existing table, that has populated data, keep in mind that it could take a long time.  If it takes too long and is initiated via the SQL Management Studio the result is very likely to be a timeout.  Ideally when creating an index via the SQL Management Studio it is good to create the index before data population.

Otherwise the best way to create an index is either via T-SQL directly or create the index and maintenance of the index via a SQL Server Job.

To setup an index via SQL Management Studio follow these quick steps.

First right click and select Indexes/Keys... and click.  A dialog will pop up.

In the dialog, first click add (#1), then name the new index appropriately (#2), and then select the actual column or columns that you want the index placed on (#3).  The results should be similar to the image below.  Once everything is set click on close.

Next click on save (circled below) and the index will be created.  Remember, if there is anything in the table there is a good change at a time out.

To do it the truly über easy way, execute the following T-SQL command.

CREATE NONCLUSTERED INDEX IX_analysis_time ON dbo.profile_stats (analysis_time)

As mentioned, the UI times out trying to build this index for me because of the rows in the table.  This table had a mere 283204 rows and took 4:40.  So you can imagine with more data how much longer it might take.  A small 10-30 second time out for the UI is good considering it locks the interface up while it is trying to create the index.  For more information on the command check out the BOL entry on MSDN for CREATE INDEX.  Also it is good to check out the general index design guidelines.  Also check out my more exhaustive best practices entry for indexes.

Keep in mind when creating a index rebuild or index reorganization job that a period of time should be taken to observe the baseline behavior of a table.  Then setup a maintenance task to run on a scheduled basis of when you observe fragmentation and other things starting to occur.  For more information on reorganizing and the specific steps and commands for fragmentation and such, read the MSDN article.

To setup a job follow these steps.

Open up SQL Server Management Studio.

Expand the SQL Server Agent.  Select the Jobs Folder and right click, on the menu select New Job....

The New Job Dialog pops up.  Fill out each of the General Section fields with pertinent information.

Next select the Steps Section.

Another sub dialog appears, add the pertinent data to this field and add whatever the maintenance command is that you wish to execute on a regular basis.

After that, click on OK and then move on to Schedules.  Again click on the New... Button to bring up another sub dialog.

A New Job Schedule sub dialog appears.  Again, you know what to do.  Add pertinent information.

Next add any Alerts... same process.

You can even setup a specific response for the alerts.

Next setup any operators or new operators that you want to receive the alert.

Select the method that will receive the error alert.

Click ok until you're back out to the main screen.  At this point you should see the job item within the job folder.  It will run at the scheduled time or just right click on it and select the run option that way.

...and that's it.  Build, run,  and maintain those indexes!

I was working through changing a web project from building to .NET 3.5 to just .NET 3.0.  It was taking a little effort as I removed the .NET 3.5 features.  During that time though I had a problem with System.Web and I realized a cool feature that Visual Studio 2008 has built in.

When one selects a pervious build version, and you try to add a reference, the references that aren't available anymore are grayed out in the Add Reference dialog.

I took a ride on the MAX today, just to do some thinking and get away from downtown for a little while.  On the way back from the west side I'm sitting and just typing away at some code.  I'm not hitting F5 to run or anything, just merely typing some code and then saving, then viewing the web page.  The web page was appearing just fine.  But on the background I noticed that the virtual hosts where multiplying like rabbits!

Has anyone ever seen this?  What in the world is Visual Studio doing?  Is it just launching a new one of these every time I force a build by saving and viewing the URI?

I closed down Visual Studio 2008 but I'm watching to see if this repeats itself.