Feed Subscribe
Exception has been thrown by the target of an invocation.


Using table-valued database functions with Entity Framework 4.0

by ondrejsv 29. December 2010 11:20

Table-valued functions represent a useful mechanism to return custom-shaped data or data processed in some custom way. Unfortunately many people does not use them (they even don’t know about them) and instead use classic stored-procedures with wild SELECTs issued at the end (sometimes even in the middle of them).

The catch is however that Entity Framework tools does not support table-valued functions (TVF). If we use the Entity Data Model Wizard on the AdventureWorksLT database, it recognizes stored-procedures and even scalar-valued function but not TVF:

image

To confirm that look in the Management Studio:

image

The only scalar-valued function ufnGetSalesOrderStatusText is available for import as well as two store procedures uspLogError and uspPrintError but not two TVFs ufnGetAllCategories and ufnGetCustomerInformation. Note that I strongly discourage you from using the ufn and usp prefixes that this Microsoft sample database uses as it only clatters your identifier space similarly as the infamous Hungarian notation.

To confirm the sad fact even further you can start up the SQL Server Profiler before running the Entity Data Model Wizard and see how it talks to the database -- it issues three queries to the selected database – the first gets all tables, the second returns all views and the third some of the functions and stored procedures through the standard meta-view INFORMATION_SCHEMA.ROUTINES with one of its WHERE conditions: … AND (DATA_TYPE != 'TABLE' OR DATA_TYPE is null).

So any workaround? Yes – the Fuction SSDL element. Let’s make our hands dirty and open the EDMX file. Insert the following element under the Schema element:


<Function Name="GetCategories" IsComposable="false">
<CommandText>
SELECT ParentProductCategoryName, ProductCategoryName, ProductCategoryId
FROM dbo.ufnGetAllCategories()
</CommandText>
</Function>

WARNING: The current implementation of the EF EDMX generator is flawed. When you update the EDXM through the “Update Model from Database”, the whole SSDL part is regenerated which means that all customizations including our Function element are lost. This is one the biggest complaints from customers and it’s expected that this gets fixed in the VS 2010 SP1. Until then you can either

  1. manually reapply your changes after you update the model,
  2. or reapply the changes with a small ad-hoc program or PowerShell utility using some XDocument or XSLT magic,
  3. or don’t even use the model update function at all (you can update manually or synchronize with the Huagati DBML/EDMX Tools).

Close the EDMX file if you edit it in the Visual Studio and reopen the EF designer. Next create a new complex type for holding the results of the function:

image

(data types are string, int, string).

Finally import the function as any other stored procedure in the Model Browser:

image

Note that there is nothing stopping you to return a collection of any of your entities provided your function returns the correspondingly shaped table.

A small test

static void Main(string[] args) { using (var cx = new AdventureWorksLT2008Entities()) { var categories = cx.GetCategories(); foreach (var c in categories) { Console.WriteLine("{0} / {1}", c.ParentProductCategoryName, c.ProductCategoryName); } } }

confirms we have done it well:

image

Tags:

Windows Live Writer crashes on start when My Documents folder is not accessible

by ondrejsv 26. December 2010 12:13

Today I couldn’t start my Windows Live Writer as it had been constantly crashing on start. I had the 2010 version and the error exception on start was the infamous NullReferenceException. I upgraded to the latest Writer 2011 but no lack – this time it kept crashing with Windows Live own exception DirectoryException (obviously the team in the 2011 version did some more checks and create a special exception). But why?

It choked in the WindowsLive.Writer.CoreServices.ApplicationEnvironment method. A little dig in the WindowsLive.Writer.CoreServices.dll in the Reflector shows the culprit:

path = Environment.GetFolderPath(Environment.SpecialFolder.Personal); _myWeblogPostsFolder = _userSettingsRoot.GetString("PostsDirectory", null); if (string.IsNullOrEmpty(_myWeblogPostsFolder)) { if ((_productName == "Windows Live Writer") && string.IsNullOrEmpty(path)) { throw new DirectoryException(MessageId.PersonalDirectoryFail); } _myWeblogPostsFolder = Path.Combine(path, "My Weblog Posts"); }

Indeed, the Environment.GetFolderPath(Environment.SpecialFolder.Personal) gave null result since I don’t have a custom path for saving blog posts set in the Windows Live Writer.

Then I remembered that we have the My Documents folder redirected on the network share which was unavailable and I had turned off the offline files Windows feature (because it slows down the boot time considerably – to 15 minutes and more).

So either connected to the network to have your My Documents folder accessible or make it available offline when using Windows Live Writer.

Tags:

WebResource.axd gives 404 or don’t use this.GetType() in RegisterClientScriptResource

by ondrejsv 19. December 2010 11:26

Since ASP.NET 2.0 we can embed resources in assemblies and use them on pages very easily with help of the magical WebResource.axd. Once we embed a resource in the assembly and mark the assembly with the appropriate WebResoruce attribute, we can call the RegisterClientScriptResource on the ClientScriptManager (instance of which is exposed through the Page.ClientScript property). The signature of the method is:

public void RegisterClientScriptResource( Type type, string resourceName )

The problem starts if your resource is embedded in the web-site assembly and you use call the registration method on directly on the page and use this.GetType() for the type parameter. The script include gets in your page but the link to WebResource.axd gives you 404 – Not found. Why?

The links to WebResource.axd are in the format ~/WebResource.asx?d=encryptedScriptId&t=timestamp

where encryptedScriptId is the name of the assembly the resource is embedded in together with its name. The assembly used to look for the resource is the same assembly in which is type defined. This is quite different from the other registration methods on the ClientScriptManager (RegisterClientScriptInclude, RegisterClientScriptBlock, …) which also take the type parameter but solely for the purpose of building a key identifying the script so that framework does not register it twice. You can find a method to decrypt the script identifier on the Hristo Deshev blog. Remember the process ASP.NET processes a web page? For each and every web page ASP.NET creates a derived class (containing processed markup) that gets compiled in a different assembly than yours, indeed. This is why you cannot use this.GetType(). Just use typeof(you page class name) and you’ll be fine.

Tags: