As I’m preparing for the 70-516 (TS: Accessing Data with Microsoft .NET Framework 4) I will be publishing and updating the list of the exam objectives together with MSDN links.

Modeling Data (20%)

• Map entities and relationships by using the Entity Data Model.
  This objective may include but is not limited to: using the Visual Designer, building an entity data model from an existing database, managing complex entity mappings in EDMX, editing EDM XML, mapping to stored procedures, creating user-defined associations between entities, generating classes with inheritance and mapping them to tables
  This objective does not include: using MetadataWorkspace

• Map entities and relationships by using LINQ to SQL.

  This objective may include but is not limited to: using the Visual Designer, building a LINQ to SQL model from an existing database, mapping to stored procedures

• Create and customize entity objects.

  This objective may include but is not limited to: configuring changes to an Entity Framework entity, using the ADO.NET EntityObject Generator (T4), extending, self-tracking entities, snapshot change tracking, ObjectStateManager, partial classes, partial methods in the Entity Framework

• Connect a POCO model to the Entity Framework.
  This objective may include but is not limited to: implementing the Entity Framework with persistence ignorance, user-created POCO entities
  This objective does not include: using the POCO templates

• Create the database from the Entity Framework model.

  This objective may include but is not limited to: customizing the Data Definition Language (DDL) (templates) generation process, generating scripts for a database, Entity Data Model tools

• Create model-defined functions.

  This objective may include but is not limited to: editing the Conceptual Schema Definition Language CSDL, enabling model-defined functions by using the EdmFunction attribute, complex types

Managing Connections and Context (18%)

• Configure connection strings and providers.
  This objective may include but is not limited to: managing connection strings including Entity Framework connection strings, using the ConfigurationManager, correctly addressing the Microsoft SQL Server instance, implementing connection pooling, managing User Instance and AttachDBfilename, switching providers, implementing multiple active result sets (MARS)
  This objective does not include: using the ConnectionStringBuilder; Oracle data provider; creating and using a custom provider; using third-party providers

• Create and manage a data connection.

  This objective may include but is not limited to: connecting to a data source, closing connections, maintaining the life cycle of a connection

• Secure a connection.
  This objective may include but is not limited to: encrypting and decrypting connection strings, using Security Support Provider Interface (SSPI) or SQL Server authentication, read only vs. read/write connections
  This objective does not include:  Secure Sockets Layer (SSL)

• Manage the DataContext and ObjectContext.

  This objective may include but is not limited to: managing the life cycle of DataContext and ObjectContext, extending the DataContext and ObjectContext, supporting POCO

• Implement eager loading.
  This objective may include but is not limited to: configuring loading strategy by using LazyLoadingEnabled, supporting lazy loading with POCO, explicitly loading entities
• Cache data.
  This objective may include but is not limited to: DataContext and ObjectContext cache including identity map, local data cache
  This objective does not include: Velocity, SqlCacheDependency

• Configure ADO.NET Data Services.

  This objective may include but is not limited to: creating access rules for entities, configuring authorization and authentication (and WCF Data Services Team Blog), configuring HTTP verbs

Querying Data (22%)

• Execute a SQL query.

  This objective may include but is not limited to: DBCommand, DataReader, DataAdapters, DataSets, managing data retrieval by using stored procedures, using parameters, System.Data.Common namespace classes

• Create a LINQ query.
  This objective may include but is not limited to: syntax-based and method-based queries, joining, filtering, sorting, grouping, aggregation, lambda expressions, paging, projection
  This objective does not include: compiling queries
  • LINQ Overview
  • C# Query Expressions Basics ("syntax-based queries")
  • Standard LINQ operators
  • Joining
  • Filtering
  • Sorting
  • Grouping
  • Aggregation
  • Paging (Partitioning)
  • Projection
  • Lambda expressions

• Create an Entity SQL (ESQL) query.

  This objective may include but is not limited to: joining, filtering, sorting, grouping, aggregation, paging, using functions, query plan caching, returning a reference to an entity instance, using parameters with ESQL, functionality related to EntityClient classes

  • Entity SQL Overview
  • Filtering (Where)
  • Sorting
  • Group by, Grouppartition, Having 
  • Paging
  • Aggregate Functions
  • User-defined functions
  • Query Plan Caching
  • REF keyword - returning a reference to an entity instance
  • Parameters in eSQL
• Handle special data types.
  This objective may include but is not limited to: querying BLOBs, filestream, spatial and table-valued parameters
  This objective does not include: implementing data types for unstructured data, user-defined types, Common Language Runtime (CLR) types
• Query XML.
  This objective may include but is not limited to: LINQ to XML, XmlReader, XmlDocuments, XPath
  This objective does not include: XSLT, XmlWriter

• Query data by using ADO.NET Data Services.

  This objective may include but is not limited to: implementing filtering and entitlement in ADO.NET Data Services, addressing resources, creating a query expression, accessing payload formats, Data Services interceptors

Manipulating Data (22%)

• Create, update, or delete data by using SQL statements.

  This objective may include but is not limited to: Create/Update/Delete (CUD), using DataSets, calling stored procedures, using parameters

• Create, update, or delete data by using DataContext.
  This objective may include but is not limited to: CUD, calling stored procedures, using parameters
  This objective does not include: ObjectTrackingEnabled

• Create, update, or delete data by using ObjectContext.

  This objective may include but is not limited to: CUD, calling stored procedures, using parameters, setting SaveOptions

• Manage transactions.
  This objective may include
  but is not limited to: System.Transactions, DBTransaction, rolling back a transaction, Lightweight Transaction Manager (LTM)

  This objective does not include: distributed transactions, multiple updates within a transaction, multiple synchronization of data within an acidic transaction

• Create disconnected objects.

  This objective may include but is not limited to: creating self-tracking entities in the Entity Framework, attaching objects, DataSets, table adapters

Developing and Deploying Reliable Applications (18%)

• Monitor and collect performance data.

  This objective may include but is not limited to: logging generated SQL (ToTraceString), collecting response times, implementing performance counters, implementing logging, implementing instrumentation

• Handle exceptions.

  This objective may include but is not limited to: resolving data concurrency issues (handling OptimisticConcurrency exception, Refresh method), handling errors, transaction exceptions, connection exceptions, timeout exceptions, handling an exception from the Entity Framework disconnected object, security exceptions

• Protect data.

  This objective may include but is not limited to: encryption, digital signature, hashing, salting, least privilege

• Synchronize data.

  This objective may include but is not limited to: online/offline Entity Framework, synchronization services, saving locally

• Deploy ADO.NET components.

  This objective may include but is not limited to: packaging and publishing from Visual Studio, deploying an ADO.NET Services application; packaging and deploying Entity Framework metadata

  This objective does not include: configuring IIS, MSDeploy, MSBuild

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:

To confirm that look in the Management Studio:

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:

(data types are string, int, string).

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

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:

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.

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.

When some math creeps into requirements list for your new web application, you get a bad feeling. It’s a problem because although the MathML standard has been there for years, only Gecko based browsers and Opera support it natively (WebKit based browsers in their latest builds, KHTML with no support) and the biggest problem – Internet Explorer still does not support it in 8th version and MathML support in IE9 has still not been confirmed. You have a few options:

• force your IE users to download and use an ActiveX MathML player – not quite a pretty solution,
• do some magic and convert (La)TeX formulas into images (Wikipedia approach) – there is mimeTeX and also an ASP.NET wrapper (but they render low quality GIF images),
• buy a commercial software (I found Equation Server for .NET, however, I didn’t try it),
• use jsMath!

jsMath

jsMath is a fantastic open source javascript library that renders any TeX formula into a series of positioned spans. If the user has TeX CM fonts installed, it uses them directly; if not, it has a fallback to displaying basic glyphs as images but this process is transparent to you as a web developer.

Deployment is simple. Download jsMath package from its homepage together with image fonts. Unpack image fonts into a subfolder called “fonts” of the jsMath folder. Now create a new ASP.NET web project/site and copy the jsMath folder to the root folder. Set any permanent port number in the project properties in Visual Studio because we need a fixed URL.

Open the jsMath/easy/load.js file and set root property to the full URL of the jsMath folder. If your web site runs on the 6500 port on localhost, for example, it will loke like this:

root: "http://localhost:6500/jsMath/",

(Of course, do not forget to edit also after deploy to your production environment.)

Now include this javascript into your .aspx file – I included it in my master page because I’m going to do math on almost all pages:

<script type="text/javascript" src='<%= ResolveUrl("~/jsMath/easy/load.js") %>'></script>

and let’s start doing math:

Some inline math: \(\sqrt{1-x^2}\) or \(ax^2+bx+c\),

jsMath will render this instantly:

If you wonder about the formula format, read any TeX or LaTeX manual.

Font embedding

With releases of Firefox 3.5, Safari 3.1 and Opera 10, web developers can use a feature called font embedding. It means that we don’t need to rely only on web-safe fonts and we can use any font by using the @font-face technique.

A common myth is that Internet Explorer does not support this technique. It’s false and the truth is that Internet Explorer has supported font embedding long before Firefox or Safari even existed – since IE4 (1997!). It supports, however, only one of formats specified in the CSS standard, called Embedded OpenType (.eot) but this usually is not a problem since conversion tools from TrueType exist.

Using the technique, we can almost always prevent the image-font fallback of our formulas even without requiring users to manually download and install CM fonts. Just copy all TTF and converted EOT fonts and include proper CSS stylesheets with EOT font for IE and TTF for all other browsers:

@font-face {
 font-family: jsMath-cmex10;
 src: url("jsMathFonts/jsMath-cmex10.eot") /* EOT file for IE */
}
@font-face {
 font-family: "jsMath-cmex10";
 src: url("jsMathFonts/jsMath-cmex10.ttf") /* TTF file for CSS3 browsers */
}

(this is just one font of 6 required by jsMath)

To make it easier for you, you may download the fonts together with the CSS file. Just include the CSS file in your pages or master like me:

<link href="../../Content/jsMath.css" rel="stylesheet" type="text/css" />

Now almost all your users will benefit from formulas rendered by CM fonts (at least IE, Firefox, Chrome, Safari and Opera! – that’s the vast majority).

As you can see, it’s all plain text – I can select it with no problems:

If you are curious, the first formula is converted to this HTML:

<SPAN class=typeset alt="\sqrt{1-x^2}"><NOBR><SPAN class=scale><SPAN style="POSITION: relative; DISPLAY: inline-block"><SPAN style="POSITION: absolute; TOP: 0em; LEFT: 0em"><SPAN style="POSITION: relative; WIDTH: 0.83em; TOP: -0.84em; LEFT: 0em"><SPAN class=cmsy10>p</SPAN></SPAN><SPAN style="POSITION: relative; WIDTH: 2.63em; TOP: -0.81em; LEFT: 0em"><SPAN style="BORDER-LEFT: 2.63em solid; HEIGHT: 1.5px" class=blank></SPAN></SPAN><SPAN style="WIDTH: 1px; DISPLAY: inline-block; MARGIN-RIGHT: -1px"></SPAN><SPAN style="MARGIN-LEFT: -2.63em" class=spacer></SPAN><SPAN class=cmr10>1</SPAN><SPAN style="WIDTH: 1px; DISPLAY: inline-block; MARGIN-RIGHT: -1px"></SPAN><SPAN style="MARGIN-LEFT: 0.22em" class=spacer></SPAN><SPAN class=cmsy10>À</SPAN><SPAN style="WIDTH: 1px; DISPLAY: inline-block; MARGIN-RIGHT: -1px"></SPAN><SPAN style="MARGIN-LEFT: 0.22em" class=spacer></SPAN><SPAN class=cmmi10>x</SPAN><SPAN style="POSITION: relative; WIDTH: 0.39em; TOP: -0.28em; LEFT: 0em"><SPAN class=size2><SPAN class=cmr10>2</SPAN></SPAN><SPAN style="WIDTH: 1px; DISPLAY: inline-block; MARGIN-RIGHT: -1px"></SPAN><SPAN style="MARGIN-LEFT: 0.05em" class=spacer></SPAN></SPAN>&nbsp;</SPAN><SPAN style="WIDTH: 3.47em; HEIGHT: 0.83em" class=blank></SPAN></SPAN><SPAN style="HEIGHT: 1.18em; VERTICAL-ALIGN: -0.16em" class=blank></SPAN></SPAN></NOBR></SPAN>

But remember. It’s all automatic and on-the-fly, you won’t even see this markup in View->Source.

Firefox and Chrome bug

There’s one more small glitch. Firefox and Chrome (and possibly also Safari) use an optimization which does not download and apply fonts specified by the @font-face rules if you don’t use them directly in the page markup. Since our formulas are parsed and converted by javascript and does not use directly any “font-family: jsMath-cmex10”, you end with fall-backed image fonts when using these browsers. This optimization is buggy because it does not take into account that you can set font-family css property also by javascript. My workaround is to use a dummy span with font-family set to jsMath-cmex10 (this is the font jsMath uses for testing whether the user has CM fonts) and some non-visible character:

<span style="font-family: jsMath-cmex10;">&nbsp;</span>

Note that you can’t hide it by display:none because the font won’t be loaded.

Place it somewhere to your masterpage where it won’t get in the way to other content.