RIA Services: Working with Foreign Keys/Associations in Entity Framework

Yesterday, I got an strange observation regarding RIA Services. I needed to created an Entity Model from views instead of tables so it had no foreign key associations defined. I manually created the associations and was finally able to get a working model. I then added a RIA Domain Service and got the following error upon build:

“Unable to retrieve AssociationType for association ‘JobWBS_ResourceAssignment'”

Strange. But after some effort I came to know that the current(July 2009) preview of RIA services expects all the associations in the default foreign key naming convention, i.e., FK_ForeignKeyTable_PrimaryKeyTable. In my case, I had a 1-Many relationship between JobWBS and ResourceAssignment so I changed the association name to FK_ResourceAssignment_JobWBS and the project built successfully.

Update: If you are still not able to get rid of the error. Its probably because your SSDL schema does not have the proper associations defined. Have a look at this post on how to manually define associations in the physical (storage) model.

Running MS Virtual PC 2007 on Windows 7 RC

I am a strong supporter of virtualization and have created various virtual machines for different development environments. After upgrading to Windows 7 RC from its beta version, I decided to install the new Windows Virtual PC. The download page greeted me with a “Windows Virtual PC requires a CPU with the Intel™ Virtualization Technology or AMD-V® feature turned” warning. Despite knowing the fact that my 2.5GHz Intel Dual Core E5200 does not have hardware virtualization support, I decided to try my luck and installed Windows VPC. The installation went successfully but upon running the sofware, it gave me a “hardware not supported” error. I then installed my old friend, MS Virtual PC 2007 and it was installed successfully. However, I was shocked to see the following error upon running it:

“The program is blocked due to compatibility issues”

VPC 2007 blocked

What!!! Windows Virtual PC cannot run because I have no hardware support and MS Virtual PC 2007 cannot run because Microsoft wants me to use Windows VPC. Does this means I will not be able to run my development VMs unless I upgrade my hardware or quit using Windows 7??

Well, a developer should not give up so easily; After some searching, I was able to find the solution:

“Microsoft Virtual PC 2007 cannot run while Windows Virtual PC is installed. We need to uninstall Windows VPC and MS Virtual PC 2007 will run fine”

Nice solution. I quickly uninstalled Windows VPC from my windows installed updates as below:

Uninstall VPC update

So now, I can run my dev machines using MS Virtual PC 2007 on Windows 7 release candidate. Hooray!!!

Note: this solution is applicable to Windows 7 final release as well.

VPC 2007 running

I am back

I was on vacations for around 3 weeks and am I glad to see some great news awaiting me: I am pleased to announce that I won TSQL challenges 7 and 8. By the grace of Allah, this makes me a 3 times consecutive winner for TSQL Challenges. Here’s a certificate I received from the BeyondRelational team for being an outstanding challenger:

Certificate

And yes, TSQL Challenge 11 is about to close. So hurry up and submit your entries before Monday.

TSQL Challenge 10: Horizontal and Vertical sorting of a result set

TSQL Challenge 10 was an interesting one. We needed to sort a result set horizontally as well as vertically. That is, given the following as an input:

input
We need to write a query that :

  1. Sort the values horizontally: Arrange the values from smallest to the largest. for example, the first row contains values “2”, “1” and “3”, it should be arranged as “1”, “2” and “3”.
  2. Sort the rows vertically: This is the regular sorting that we are familiar with.
  3. Remove duplicates: Duplicate rows should be removed from the final output.

And here’s the expected output:

output
I tackled the problem systematically in series of steps, each represented by a CTE:

  1. First, I assigned a rowID to each row of the input
  2. Then I merged the three columns into one using unpivot transformation
  3. Then I sorted that merged result grouped by rowID (horizontal sorting)
  4. Then I used pivot to split the values into three columns based on that sorting
  5. Finally I sorted the result (vertical sorting) and displayed distinct rows

Here are the CTE definitions along with the result at each step: Read the rest of this entry »