Bulb Flash:- Firing a Complex SQL Query using Entity Framework 4.0

In one of our recent projects, we decided to swap SQL Express with SQL Compact to ensure easy installation on client machines…Though the move was smooth there was one hitch….SQL Compact does not support Stored Procedures!!!

And here we were with lots of  medium  complexity SPs with Joins and GroupBys ,OrderBy and Min Max functions!

For Example

select top(@number) p.ProjectName as ProjectName, p.ProjectID as ProjectID,c.Client_Name as ClientName,Max(pl.EndTimeStamp) as LastTimeWorked
from Projects as p join ProgramLog as pl
p.ProjectID  =pl.fk_ClientCode
join Clients c
on pl.fk_ClientCode = c.ClientID

Group By p.ClientProjectID, p.ProjectName ,c.Client_Name
Order by MAX (pl.EndTimeStamp) desc

We clearly had two options

1. Replace the stored proc logic with LINQ to Entity queries. We gave that a shot but realized its not easy to write a LINQ query with all the Joins, GroupBys and Max statements etc

2. Fire the SQL query from Entity Framework…We knew this could be done using LINQ to SQL  very easily but hadn’t tried it with the EF yet. Searching online brought us to the “ObjectQuery” feature . You can read more about it here.

Am not going to get into the details because this didn’t work for us also. Although ObjectQuery works well for simple statements( even parameterized), it didn’t work for us for using “Joins”. We tried many options including those given here but to no avail..

What worked!!

What did work for us was this really simple and beautiful feature provided in EF 4.0 called ExecuteStoreQuery<>

So the solution was as simple as

string query = @"select top(@number ) p.ProjectName as ProjectName, p.ClientProjectID as ProjectID,c.Client_Name as ClientName,Max(pl.EndTimeStamp) as LastTimeWorked
           from Projects as p join ProgramLog as pl
           p.ClientProjectID  =pl.fk_ClientCode
           join Clients c
           on pl.fk_ClientCode = c.ClientID

           Group By p.ClientProjectID, p.ProjectName ,c.Client_Name
           Order by MAX (pl.EndTimeStamp) desc";
           var args = new DbParameter[] { new SqlParameter { ParameterName = "number", Value = count } };
           var result = entityContext.ExecuteStoreQuery<MostRecentProjects>(query, args);
           List<MostRecentProjects> resultList = result.ToList();

If you’ve been following closely you will notice I have the ExecuteStoreQuery method returning a list of MostRecentProjects…Since my query does not return a previously generated “entity” but involves a join statement returning a mix of values , this is not an edmx generated class but a simple custom class I created which has one property for each value returned by the query and the ExecuteStoreQuery method was nice enough to fill it for me!!(This was of course trial and error and are we glad it worked!!!)

This is what the class looks like

public partial class MostRecentProjects
      public int ProjectID { get; set; }
      public string ProjectName { get; set; }
      public string ClientName { get; set; }
      public DateTime LastTimeWorked { get; set; }


Hope this helps!

Until Next Time!


Bulb Flash:- Some practical WPF MVVMLight tips!

One of our major projects recently used WPF 4.0 with MVVM pattern.We used the MVVM Light Toolkit for implementing the MVVM Pattern.

The MVVM Light Toolkit definitely has a lot to offer to make your life easier but the documentation is not exemplary!

Few things we learnt down the road as we came to a close on the project are

1. You are better off using the ViewModalLocator. We didn’t use it initially and realized that our ViewModels are not getting Disposed, multiple view models are getting created especially when using the “Commanding” feature!

2. When using Commands , if you are not using the ViewModalLocator, try not to set the IsDataSource property as it will change the DataContext of your screen to the new ViewModel and there will be inconsistency during commanding as previously set variables will not be available in the new VM. Or if you do need to set the IsDataSource property send all data needed by the command handler as arguments( because previously set data will not be available to the new VM)

3. You need a default empty constructor in all your ViewModels or your XAML screens commands won’t work(They instantiate the empty construtor)

4. You can pass event args parameters in commands using PassEventArgsToCommand="True" in your EventToCommand XAML

5. When using messaging use the method overload Send<TMessage>(TMessage message, object token).Using the token to register and send messages ensures it is delivered only to valid subscribers!

6. Every time you register for an event using the Messenger.Default.Register….ensure you Unregister also using the Messenger.Default.Unregister or else you might have memory leaks!!

Will try to write more elaborate posts detailing out these and more issues .Meanwhile drop a note at anshulee@cennest.com if you have any queries/ need more tips related to MVVM…

Until then!


EF 4.0 Bulb Flash!:- Load only what you need!!

A small tip for those working with Entity Framework 4.0:- We all know the concept of Lazy Loading in the Entity Framework: With lazy loading enabled, related objects are loaded when they are accessed through a navigation property.

With lazy loading a drawback is that an object retrieved from the database comes loaded with all its navigable Objects so you may be querying an “Order” class but it comes loaded with the Order.Customer object .

[Thanks to Julielerman for pointing out this inconsistency.  Lazy Loading loads related entities on Navigation and does not come “loaded” with them]

While you may want this in some cases, it makes sense to disable this feature in performance oriented applications and load only what you need!

As against what is written in MSDN , our experience is that when an entity context object gets created, its LazyLoadingEnabled property is defaulted to true!… This is also a reported issue with microsoft

So first step would be to disable the LazyLoadingEnabled!

ProgramEntities entityContext = new ProgramEntities();
entityContext.ContextOptions.LazyLoadingEnabled = false;

List<Order> orderList= entityContext.Orders.ToList();

Next, load what you need!

orderList.ForEach(p => entityContext.LoadProperty(p, "Customer"));

Now you can access the Customer as Order.Customer while ensuring you did not load other related  navigable properties like Order.Contents etc etc.

Hope this helps “lighten” up your code a bit!!

Cennest !!

The First Few Steps to Building a Claims Based Application

If you’ve been through my earlier blog “An Introduction to the world of claims” you are now familiar with the most frequently used terms in the “Identity world”.

Next lets take a generic walkthrough of the basic steps you will go through when you make a Claims Aware/Claims Based/Relying party application

Step 1:-  You will make you application Claims Aware

The Windows Identity Foundation (WIF) provides a common programming model for claims that can be used by both Windows Communication Foundation (WCF) and ASP.NET applications. You need to add the Microsoft.IdentityModel.dll as a reference dll which gives you another property called Identity.Claims.

Making your application Claims aware involves a couple of steps at the config level(we won’t get into details right now) but the good news is that your core code does not get changed. Most probably your code is of the form

protected void Page_Load(object sender, EventArgs e)
    var user = (User)Session["LoggedUser"];
    var repository = new TimeSheetRepository();
    var expenses = repository.GetTimesheets(user.Id);
    this.MyTimesheetsGrid.DataSource = expenses;

Even after making your application claims aware this code remains unchanged. What changes is an addition to your Global.aspx which reads the claims delivered by the STS and creates the Session User out of it.

So you will have something like this in your Global.asax file

protected void Session_Start(object sender, EventArgs e)
    if (this.Context.User.Identity.IsAuthenticated)
        string issuer =
        string givenName =
        string surname =
        string costCenter =
        var repository = new UserRepository();
        string federatedUsername =
        GetFederatedUserName(issuer, identity.Name);
        var user = repository.GetUser(federatedUsername);
        user.CostCenter = costCenter;
        user.FullName = givenName + " " + surname;
        this.Context.Session["LoggedUser"] = user;

Don’t focus on the code details yet. The point to be taken home is that your code did not change. You just had to make some changes in your web.config, added a handler in your Global.asax and your app is ready


Step2:- Decide on your Issuer(STS)

You accept claims from “Issuers” you trust. Examples of standard off the shelf issuers are ADFS, LiveID, Kerberos etc. Based on your requirement you may end up using one of these ready made issuers or maybe as an application developer with custom authentication requirements you may be asked to make an issuer. Even if its the latter case, there are sample issuer templates(ASP.NET Security Token Service and WCF Security Token Service) provided in VS2010 to allow you to make Custom Issuers and lot of guidance on how to configure them so don’t worry about it.


Templates in VS2010 Create new Website!

Step 3:- Make your application “Trust” the issuer.

Like i said earlier,your application needs to trust the token issuer , only then should it accept a claim it received. When you configure an application to rely on a specific issuer, you are establishing a trust (or trust relationship) with that issuer.
There are several important things to know about an issuer when you establish trust with it:
• What claims does the issuer offer?
• What key should the application use to validate signatures on
the issued tokens?
• What URL must users access in order to request a token
from the issuer?

Just like in a Web Service information about the service can be read from a WSDL, in this case you get all your answers by asking for a FederationMetadata document. This is an XML document that the issuer provides to the application. It includes a serialized copy of the issuer’s certificate that provides your application with the correct public key to verify incoming tokens. It also includes a list of claims the issuer offers, the URL where users can go to get a token, and other more technical details, such as the token formats that it knows about

VS2010 makes trusting an issuer easy by providing an “Add Service reference kind of experience” for adding an Issuer..

Right Click on your application and select “Add STS Reference” to start the configuration wizard..More details on how to populate the wizard in another post..Point to be taken home :- its easy to add a trusted issuer!


Step4:- Configure your STS to know about the application!!

The issuer needs to know a few things about an application before it can issue it any tokens:
• What Uniform Resource Identifier (URI) identifies this application?
• Of the claims that the issuer offers, which ones does this application require and which are optional?
• Should the issuer encrypt the tokens? If so, what key should it use?
• What URL does the application expose in order to receive tokens?
Each application is different, and not all applications need the same claims. One application might need to know the user’s groups or roles, while another application might only need a first and last name. So
when a client requests a token, part of that request includes an identifier for the application the user is trying to access.

This step might be different for each Issuer. For ADFS for example the ADFS management console allows you to added a relying party..using an “Add a trusted relying party” option. This would allow you to configure ADFS by telling it what claims you need etc. If you are using an Existing STS you will need to study its behaviour. Incase of custom STS using the VS templates you can customise in code and a lot of work is done by the FedUtil(Add STS ref) also.

Now that you know the overall steps in making an end to end claims aware app..we will get into some more details next time!

Until Then


Introduction into the world of “Claims”( Windows Identity Foundation & Azure App fabric Access Control!)

Have you been working on a website or an application where you maintain a user database for people coming from different companies/domains ? Someone is responsible for maintaining the consistency of this database? Also you as an application developer are required to write code to check for authorization rights allowing/disallowing visitors to visit only authorized sections of your site?

All issues i’ve mentioned so far are pretty common in a multi-tenant type of an applications(like SAAS) which caters to multiple companies . Usually you would have each user of your app create a new username/password and store it in your app database..here are the disadvantages of such an approach

1. Your users have to remember ANOTHER set of username/passwords

2. You end up storing not just their username/passwords but also other details like their role, reporting manager etc etc which is btw already present on their corp net so you are basically duplicating information

3.You are responsible for maintaining the duplicate information…if the person got promoted to a manager , your database needs to be updated also

4. What if the person leaves the company? He still has a login into your application until it is manually removed!

Even if your user’s don’t really come from a domain or a company, aren’t there enough “Identity Providers” like Live, Google, OpenID out there?. Why do you need to “authenticate” these users?. Why not just ask an existing Identity provider to check out the user’s authenticity and let you know more about the user?Basically “Outsource” your “authentication” work  and focus on your core capability which is application logic..Sounds too good to be true?? Welcome to Claim based Architectures!!

Microsoft’s Windows Identity Foundation  provides a framework for building such “Claim based applications”.My next sequence of blogs will be an attempt to demystify the “Claims based Identity Model”.

If you are still reading this you are probably saying demystify WHAT???

So lets start with what claims based identity model means..

When you build claims-aware applications, the user presents her identity to your application as a set of claims  One claim could be the user’s name; another might be her email address. The idea here is that an external identity system is configured to give your application everything it needs to know about the user with each request she makes, along with cryptographic assurance that the identity data you receive comes from a trusted source.


Under this model, single sign-on is much easier to achieve, and your application is no longer responsible
 Authenticating users.
 Storing user accounts and passwords.
 Calling to enterprise directories to look up user identity details.
 Integrating with identity systems from other platforms or companies.
Under this model, your application makes identity-related decisions based on claims supplied by the
user. This could be anything from simple application personalization with the user’s first name, to
authorizing the user to access higher valued features and resources in your application.

Lets also define a few more key terms you would hear again and again..

Claims:- You can think of a claim as a bit of identity information, such as name, email address, age, membership in the sales role, and so on. The more claims your service receives, the more you’ll know about the user who is making the request. Claims are signed by an issuer, and you trust a set of claims only as much as you trust that issuer. Part of accepting a claim is verifying that it came from an issuer that you trust.There are steps as to how to establish a trust relationship between your application and an issuer. Will elaborate on those in another post..

Security Token:- A set of claims serialized and digitally signed by the issuer

This next one is confusing

Issuing Authority & Identity Provider

An issuing authority has two main features. The first and most obvious is that it issues security tokens. The second feature is the logic that determines which claims to issue. This is based on the user’s identity, the resource to which the request applies, and possibly other contextual data such as time of day.

Some authorities, such as Windows Live ID, know how to authenticate a user directly. Their job is to validate some credential from the user and issue a token with an identifier for the user’s account and possibly other identity attributes. These types of authorities are called identity providers

So basically not All Issuing Authorities are Identity Providers. Some of them just accept claims from Identity providers and convert them into claims acceptable by your application (Azure app fabric Access Control is such an example)..basically they don’t have authentication logic..just mapping logic.

Security Token Service(STS):- This is another confusing term as you will see people using Issuer and STS interchangeably.Basically A security token service (STS) is a technical term for the Web interface in an issuing authority that allows clients to request and receive a security token according to certain interoperable protocols

Relying Party:- When you build a service that relies on claims, technically you are building a relying party. Some synonyms that you may have heard are claims aware application, or claims-based application.

Pretty heavy definitions!!.. It took us some reading to finally find definitions that are easy to understand. Surprizingly the easiest definitions were not in “A Guide to Claims-based Identity” or “WindowsIdentityFoundationWhitepaperForDevelopers-RTW” but in “A Developer’s Guide to Access Control in Windows Azure platform AppFabric

If you have reached this line then you are definitely on your way to building the next gen identity aware apps…so look forward to our next set of blogs!!

Till then!