Entity Framework: Eager Load Data When In Projections

Entity Framework supports eager loading and data projections.
Eager Loading: loading related entities together to avoid multiple database calls
Projections: Retrieve a set of data entities from database and return a converted set of data entities

Till today, I thought EF eager loading and projections are working together in a single query.

Problem

I had a query similar to below:





When I opened Entity Framework Profiler, it always displayed multiple additional queries to load 'EmailAddresses'. So, eager loading was not working at all.

Then I did bit of googling and found that EF eager loading and projections are not working together.

Solution

I did following workaround to load data together.

First created a wrapper property to the property I needed to eager load:




Updated the data query to set the wrapper property:


It worked without any issue.

Save C# dates to SQL Server independant of SQL Server date format

Saving C# dates in SQL Server is very common. But if the date formats used in Application and SQL Server are different, it's a nightmare to handle it. Specially when consider future changes in format/regional settings/locale etc.

This simple trick in data saving will make sure dates will save without any problem even date formats are changed in Windows or SQL Server.

The trick:
1. Convert the C# date.ToString("") 
2. Use SQL Convert(datetime, , )
3. Match the formatting used in and

E.g.
DateTime dob = new DateTime(1980, 12, 31, 23, 59, 59);StringBuilder buffer = new StringBuilder();
buffer.Append("INSERT INTO [dbo].[Employees] ");
buffer.Append("(EmployeeID,EmployeeName,DateOfBirth,IsActive) ");
buffer.Append("VALUES ");
buffer.AppendFormat("(1,'Name',CONVERT(datetime, '{0}', 126), 1)", dob.ToString("yyyy-MM-ddThh:mm:ss.FFF"));
string sql = buffer.ToString();//Working SQL
**** Here SQL Format Number=126 is actually yyyy-MM-ddThh:mm:ss.FFF format
References:
1. SQL Server Date Format Numbers
2. C# Date Formatting 

HTML5 Audio Player with PlayList

HTML 5 audio is getting popular. If a page targets Apple devices, then HTML 5 is the way to continue.


Though I have seen many HTML 5 Audio tag samples, I couldn't find any good sample for creating a play list with Audio tag. This sample shows how to do that. Developers may write better coding than this, but this quick and simple sample was done to demonstrate the strategy.


First, define the player in HTML 5

<audio autoplay="false"
       controls="controls"
       id="html5AudioPlayer"
       src="temp-audio-file.mp3"
       stlye="width:400px;">
</audio>

* you should set a valid mp3 file for 'src' attribute of the audio tag. otherwise media play will not start.

Then define the javascript: javascript will play the next mp3 (in the playlist) at the end of each mp3. That will create the continoues playlist.

<script type="text/javascript">
//define the play list
var audioPlayList = new Array();
audioPlayList[0] = 'http://myserver.com/my-audio-01.mp3';
audioPlayList[1] = 'http://myserver.com/my-audio-02.mp3';
audioPlayList[2] = 'http://myserver.com/my-audio-03.mp3';

//find the audio section
var html5Audio = document.getElementById('html5AudioPlayer');

//attach event - on load
html5Audio.addEventListener('loadstart', function () {
  var currentMp3File = html5Audio.src;
  var firstMp3File = audioPlayList[0]; //play the first mp3
  //only interrupt if its the cold start
  if (currentMp3File.indexOf("temp-audio-file.mp3") >= 0) {
    html5Audio.src = firstMp3File;
    html5Audio.pause(); //auto start = false
  }
});

//attach event - on end - move to next one
html5Audio.addEventListener('ended', function () {
  var currentMp3File = html5Audio.src;
  for (var i = 0; i < audioPlayList.length; i++) {
    var audioFile = audioPlayList[i];
    if (currentMp3File.indexOf(audioFile) >= 0) {
      //next audio
      var nextAudioFile = audioPlayList[i + 1];
      if (nextAudioFile == undefined) {
        //start from first item again -loop
        nextAudioFile = audioPlayList[0]; 
      }
      html5Audio.src = nextAudioFile;
    }
  }
});

//attach event - play - to notify others
html5Audio.addEventListener('play', function () {
  var currentMp3File = html5Audio.src;
  //findthe index
  for (var i = 0; i < audioPlayList.length; i++) {
    var audioFile = audioPlayList[i];
    if (currentMp3File.indexOf(audioFile) >= 0) {
      audioplayertrackchanged(i);
    }
  }
});

//play a track by track index
function playtraack(index) {
  var nextAudioFile = audioPlayList[index];
  html5Audio.src = nextAudioFile;
}

//notify the current playing track number
function audioplayertrackchanged(newindex) {
  //do any page update etc here for each track
  //e.g. display a message etc
}
</script>

References:
I got this concept from http://stackoverflow.com/questions/2551859/html-5-video-or-audio-playlist.

Spring Bluff Railway Station

Spring Bluff is located in Queensland. It's about 130km from Brisbane and 14km from Toowoomba.

We went to Toowoomba carnival of flowers last September. We went to see Spring Bluff railway station on the way to Toowoomba.

The railway station is old and reminds me of railway stations in central hills in Sri Lanka. As we heard, Spring Bluff railway station is a heritage site and now mostly a visitor attraction. But they operate trains from Toowoomba railway station to Spring Buff railway station during the Toowoomba flower festival.

The railway station has a beautiful garden, picnic area and a cafe. If you are travelling through Toowoomba, this is a good place to visit and have a break.

Importing Excel with OleDbDataReader: handle cells with text length > 255

Most developers are familiar with importing excel sheets with OleDbDataReader. But not many know that Excel-OleDbDataReader cannot handle cells with text longer than 255 characters. This happens due to an intended behaviour in Excel.


When importing an excel sheet with the OleDBDataReader, it uses a registry setting called TypeGuessRows  to decide how many rows to inspect to guess the type of a column. Usually this setting is 8 rows. 


By changing that setting to zero forces Excel to check all the record to determine the column type:

The path to the registry key:

Remember:
1. You need to change the registry setting in correct Excel version ( e.g. 8.0, 12.0 etc).
2. If you are in a 64 bit machine, remember this Wow4232Node in the path.

Repeatable Unit Tests with TransactionScope

In the beginning of most projects developers write unit tests. But when project grows it become difficult to manage the unit tests. Tough deadlines are the main culprit for non-maintained unit tests.

But poor test data management strategy is also a main culprit in non-maintainability of test cases. In a previous project I used TransactionScope avoid writing test data and causing un-manageable test cases

Strategy:

The simple theory is, if we rollback the transaction, test cases will not save any test data to the database. So what I have done is:

1.  Used a single class to provide test data (yes I used a fixed set of data for testing, if someone needs to generalized this, he/she can use a separate test data in a .XML file or another storage)
2. Wrote all database related functionality inside a TransactionScope and did a transaction rollback to avoid data save

Steps:

Used a single class to retrieve test data:

    public class TestDataProvider {

        public static Employee GetEmployee() {

            var employee = new Employee{

                Name = "Paul",

                DateOfBirth = new DateTime(1980, 5,5),

                JoinedDate = new DateTime(2005, 01, 01),

                Position = "Engineer"};

            return employee;

        }

    }   

Wrote all the database related functionality inside a transaction scope and did not commit the rollback:

    /// <summary>

    ///A test for AddEmployee

    ///</summary>

    [TestMethod()]

    public void AddEmployeeTest() {

        var employee = TestDataProvider.GetEmployee();

        using(var ts = new TransactionScope()) {

            int newEmployeeID = EmployeeLogic.AddEmployee(employee);

            Assert.AreNotEqual(0, newEmployeeID, "Employee Not Saved Properly");

            var saved = EmployeeLogic.FindByID(newEmployeeID);

            Assert.AreNotSame(saved.Name, employee.Name, "Different Name");

            Assert.AreNotSame(saved.DateOfBirth, employee.DateOfBirth, "Different DoB");

            //ts.Complete(); - do not complete the transaction

        }

    }

You should remember not to commit the transaction. Then the tests will run successfully without keeping any garbage data in database.

Note:
If you have identity data columns for your database tables, these test methods will increase the current identity value since it creates data rows in the database (data will be rollback, but not the identity value of the table). So you may need to execute a script to reset identity column value.

Data Sorting with Dynamic/Conditional ORDER BY Statements

Almost in every web and windows application, users need to view data sort by various parameters. This is a very common scenario but there are many (yeah, too many) ways of solving this.

Some developers prefer to sort items within in the application code, some in database.  The correct decision depends on the nature of the problem.

I thought to demonstrate dynamic data sorting logic implemented in database side in this post. First of all, imagine we have to implement following business scenario:

Products are stored in AdventureWorks [Production].[Product] table. We need to sort products as below:

Strategy:

I am going to use dynamics ORDER BY functionality in SQL Server.

Steps:
Define a C# Enum to store the sorting options:

public enum SortBy {

    NameAtoZ        = 0,

    NameZtoA        = 1,

    PriceHighttoLow = 2,

    PriceLowtoHigh=3,

    SellStartDateHightoLow   = 4,

    SellStartDateLowtoHigh   = 5,

}

Build the drop down list to view sort options by assigning values of SortBy enum as values:

<asp:DropDownList ID="ddlProducts" runat="server">

   <asp:ListItem Text="Name (A-Z)" Value="0"></asp:ListItem>

   <asp:ListItem Text="Name (Z-A)" Value="1"></asp:ListItem>

   <asp:ListItem Text="Price (Low-High)" Value="2"></asp:ListItem>

   <asp:ListItem Text="Price (High-Low)" Value="3"></asp:ListItem>

   <asp:ListItem Text="Sell Start Date (High-Low)" Value="4"></asp:ListItem>

   <asp:ListItem Text="Sell Start Date (Low-High)" Value="5"></asp:ListItem>

</asp:DropDownList>

Pass the selected value of the drop down to the stored procedure. In the stored procedure, use dynamic ORDER BY clause to perform sorting:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE uspGetProductsSortBy

(

      @SortBy Int

)

AS

BEGIN

      
SELECT Name, ListPrice, SellStartDate

From Production.Product

ORDER BY         

   Case When @SortBy=0 Then Name END ASC,

   CASE When @SortBy=1 Then Name  END DESC,

   CASE When @SortBy=2 Then ListPrice END ASC,

   CASE When @SortBy=3 Then ListPrice END DESC,

   CASE When @SortBy=4 Then SellStartDate END ASC,

   CASE When @SortBy=5 Then SellStartDate END DESC   

   --Need to convert dates to string, otherwise result is not 100% correct

END

GO

References:

• http://www.sqlteam.com/article/dynamic-order-by
• http://msdn.microsoft.com/en-us/library/ms188385.aspx

Maryborough: Our first stop in Australia



A scene from Ullula lagoon



Lenthall Dam Area




A close encounter with a whale

 

Maryborough Court House



Pub Fest 2010 - a group of participants

Fraser Coast Show 2010 

Fraser Coast Show 2010 - Wood chipping contest

Fraser Coast Show 2010 - Waiting for the rodeo



Maryborough City Hall





Maryborough is the city we are living currently. So I thought to write a blog post about the city and its surroundings.

Maryborough is in Queensland and it is located about 250km north of Brisbane, off the Bruce highway. It’s only 30km to Hervey Bay. Anyone visiting to Maryborough or Hervey Bay has several trasporation options:

• By Car – Best option in this part of country.
• TravelTrain – Maryborough has a railway station, anyone visiting Maryborough city or Hervey bay should get the coach connection from Maryborough station.
• Coach/Bus - Tory’s tour coach – This runs twice a day from Hervey Bay to Brisbane and return. In my opinion this is the second best option to travel because it takes only 4 hours from Hervey Bay to Brisbane and 3.5 Hours from Maryborough to Brisbane.

• Coach/Bus - Greyhound – They have many buses going through Maryborough both from Brisbane and to Brisbane. Greyhound’s travelling time is bit higher since it goes through all the cities on the route.
• Plane – Hervey Bay has a airport which has connections to Brisbane, Sydney and Lady Elliot island.
• Car Rentals/Taxi – After coming here, you can find many car rentals and taxis operating. So getting around is not difficult.

Maryborough is a self-styled heritage city in Queensland. According to the records, it started in 1847 and it had a port with ships coming from all over the world. Once it was suggested as the capital of the state (should be long ago).

 After all it’s busy days, now it is a clam and peaceful city with traces of history everywhere. Mary River is an important icon of the city with flowing through the city and making a scenic display. People here are very helpful and friendly, noticeably different than other places I have been.

The city has an information centre. Anyone visiting city should visit them, the staff is really friendly and helpful. They will give you a good insight to the city and surroundings, but I thought to list few things you can here:

• See Maryborough city – see the old buildings, museums and the sleepy city. When you walk on the wharf street you might think you are walking in past.
• Queens Park (at the edge of the river and next to the CBD) and Anzac Park/Ululah Lagoon – These make a relax feeling with it’s lush green. Also you can feed the ducks in Ullulah lagoon.

• Thursday’s heritage markets – every Thursday Maryborough turns busy with it’s heritage market. It has a variety of stalls. Also the steam train and river cruise are operating on Thursdays.
• Lake Lenthall– a popular recreational park just 20km north of Maryborough. It is in the Wongi forest and it is popular for fishing and camping.
• Teddignton weir – This is a good picnic area next to the weir. I have seen people fishing on the dam, but the sign says not to. So, I am not sure about fishing in here. But it is a good picnic area with a small swimming pool for children and a patch of rainforest type trees.

And few more things you can do around here:

• Fraser Island – most popular tourist destination in the region. We are yet to go there, but this is in the top of priority list.
• Whale watching in Hervey Bay - Hervery Bay is a very good location to watch whales. We went on 'Spirit of Hervey Bay' ship and it was amazing to whales playing and swimming around.
• Lady Elliot Island – Tourists can get flights from Hervey Bay airport to Lady Ellito Island.
• Childers – Just 50 km north of Maryborough. There are few wineries near the city. Also Childers is popular for it’s multicultural festival held on last week of July each year.
• Rainbow beach – it’s about 70km to Rainbow beach from Maryborough. Rainborw

Important events in Maryborough:

• Sunday in the Park – on last Sunday of each month, Queens park turns in to a busy place with brass bands, stream trains (both mini train and historical stream engine) and lot of crowd. River cruise is also operates on Sundays.
• Pub Fest – thousands of participants crawl from one pub to another to complete all the pubs in the pub trail. All of them wear decorated costumes and with music around, it’s a party day for Maryborough
• Mary Popins Festival – Maryborough is the birth place of P.L.Travers, author of Mary Poppins children’s books. So, city has a one day celebration with various type of events which will take you to the time of Mary Popins.
• Fraser Coast Show - this is an annual event with lot of activities, events, races and rodeos (last time they had different types of rodeos).
• There are many more events in the regions. You can find everything in http://www.frasercoastevents.com.au/

There are many more places and things to do in Maryborough and Hervery Bay area. It is very easy to find details from an information center. Also I tried to add many informative links as possible. Feel free to comment/email your ideas and corrections. All are wellcome.