Entity Framework: Queries involving many to many relationship tables

Entity framework handles tables participating in Many to Many relationship in a nice manner. If the junction table (sometimes called bridge table, association table, link table, etc) only consists of the foreign keys and no other columns, then that table is abstracted by EF and the two sides get a navigational property exposing a collection of the other side. If the junction table contains other fields as well (e.g. the table has its own primary key column), then that junction table is included in the model with many-to-one relationships with both the participating tables. This post will present some common LINQ queries involving the many to many relationship tables in both scenarios: when the junction table is abstracted by EF, and when it is exposed. Read the rest of this entry »

LINQ: How to write queries with complex join conditions

This small post is a follow-up of my previous post in which I discussed some common scenarios and how we can handle them in our LINQ queries. This time, we will see how to write queries that have not-so-simple conditions as join predicates. Note that LINQ directly supports only equi-joins, that is, joins based on equal conditions. Hence, for simple primary / foreign key fields, we can simply use the syntax:

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID

For composite primary / foreign keys, we need to create an anonymous type with same data type and member names in the join predicate:

from c in Customers
join o in Orders on new {c.CompanyID, c.CustomerID} equals {o.CompanyID, o.CustomerID}

However, sometimes, we have join predicates that do not check equality, e.g. our join condition is based on a between check as in the following T-SQL query.

Select ..
 from Order o
 join AccountingPeriod ap
  on o.OrderDate between ap.PeriodStart and ap.PeriodEnd

Such situation can be handled in LINQ by using an always true join condition and transferring the join predicate to the where part. Here’s the LINQ translation for the above SQL query:

from o in dc.Orders
join ap in dc.AccountingPeriods on true equals true
where o.OrderDate >= ap.PeriodStart && o.OrderDate <= ap.PeriodEnd
select …

Ane even better version as pointed by zn00p in the comments section is:

from o in dc.Orders
from ap in dc.AccountingPeriods 
where o.OrderDate >= ap.PeriodStart && o.OrderDate <= ap.PeriodEnd
select …

Notice that this concept is valid for inner joins only. Since for inner joins, the join predicate can be moved to the where part of the TSQL query. The above trick is valid for both LINQ to SQL and LINQ to Entities (Entity Framework).

LINQ: How to build complex queries utilizing deferred execution and anonymous types

While working with LINQ to SQL and LINQ to Entities (Entitiy Framework), I really appreciate and utilize the deferred execution capabilities, i.e. the queries are not materialized (sent to server) until they are needed. This great feature can help us to construct our query in several steps and then send a single optimized TSQL version to the database for processing. This approach is similar to our application of CTE (Common Table Expressions) in T-SQL, with which we can build our T-SQL queries in small manageable steps and then finally run a single optimized query on the database. This post will present some scenarios to show how we can utilize the deferred execution capabilities and anonymous types to build complex queries in LINQ.

Read the rest of this entry »

.NET Framework 4: A first look at the new features

Visual Studio 2010 Release Candidate has been made available a few weeks back. The .NET Framework 4 is arriving with some great new enhancements for all of us, and in this post, I plan to briefly discuss some of those exciting features that caught my eye. Let’s begin:

Parallel extensions
Introducing parallelism in our applications was never this much easier. The new Task Parallel Library offers a lot more control over the work items (called Tasks) as compared to our classic Thread Pooling techniques. The semantics of the new parallel loops are much similar to our existing sequential loops and hence our existing logic can be parallelized with minimal effort. I have discussed the new parallel programming features in .NET framework 4 in my followup post here.

Entity framework 4
The new version of Entity Framework seems to have quite a number of enhancements. It is great that Microsoft has addressed the user responses regarding the current limitations of Entity Framework 3.5. We are getting support for POCO (Plain Old CLR/C# Objects) and self-tracking entities which are great features for our enterprise and LOB applications having more than one tier. The new foreign key associations are another great enhancement since the users were finding it difficult to set associations without grabbing the entity from database. Model first development, lazy loading, code only abilities, complex types handling, better naming of entities, improvement in generated TSQL queries, ability to directly run store commands are some other wonderful features of the upcoming version of Entity Framework.

Managed Extensibility Framework
The Managed Extensibility Framework offer an excellent way for our enterprise LOB applications to dynamically expand. In previous versions of .NET, and without MEF, we need to manually load external assemblies and use reflective techniques to instantiate and use their objects/features. This has been made really easy with Managed Extensibility Framework in .NET 4. When an MEF enabled application is launched, it automatically discovers and loads all the extensions and hence allows us to easily add new features to an already deployed application. Here’s an excellent article by Glenn Block from last month’s MSDN magazine: Building Composable Apps in .NET 4 with the Managed Extensibility Framework. MEF seems quite promising I really look forward to use MEF in my future applications.

Silverlight 4
Wow!! We haven’t yet been able to fully digest Silverlight 3 and SL 4 is approaching. Microsoft is doing a lot of investment in Silverlight and the upcoming version has quite great features. The ability to consume COM objects has greatly reduced the limitations of Silverlight that were inherently present due to being a browser based technology. Tim Heuer once did a wonderful job by covering all the features of Silverlight 4 in this post. I will simply list a few ones here:

  • Drag and Drop support from desktop to a Silverlight App: This one would allow us for better UI experiences for some interactive applications. Imagine that we have got some Rich Text Editor application running and we drag a photo from our desktop and it finds its place in the current document. Cool!
  • COM integration: COM support in out-of-browser mode can lead us to achieving some very powerful interaction with the programs on the user machine. We can now access a SQL server database residing on user’s computer, we can now open a certain document in user’s MsProject app, and many more. For some insight, have a look at Justin’s wonderful post here.
  • MEF support: Now our Silverlight apps are extendable via MEF which is really cool.
  • Right click support: A perfect feature for our interactive applications, we now can control the context menu items as well.
  • Desktop Notifications: The Silverlight application can now show Taskbar notifications as well.
  • ICommand support : A great feature for MVVM lovers, we now have commanding support for in XAML without requiring any external MVVM framework.
  • Printing support: We can even control printing from our Silverlight application.

DLR and dynamic keyword
The CLR (Common Language Runtime) is now accompanied with DLR (Dynamic Language Runtime) in .NET Framework 4. This is going to allow easy interaction with dynamic languages and COM objects without explicitly using the reflection methods. More on this on this MSDN doc: DLR Overview.

Code contracts
This is a great enhancement for our architects who are always cautious about design principles. The new version of .NET now support specifying pre-conditions, post-conditions, and object invariants. This can result in improved testing and better contract verification in design-by-contract programming. More info on this MSDN doc: Code Contracts.

Optional Parameters
Like Visual Basic, we are getting support for optional parameters in C# with .NET 4. Although this is a debatable topic but it can be helpful in cases where we needed to develop several overloads of a single method. More on this topic in this MSDN doc: Named and Optional Arguments.

That’s all. These were my initial thoughts on the new features of .NET Framework. The full official list of the new enhancements can be viewed on MSDN here. Of course, the views and opinions will get richer by the passage of time when we start building our application in .NET 4. So let’s download Visual Studio 2010 RC from here and start enjoying the new features. Happy programming!

Entity Framework: Creating a model using views instead of tables

In Entity Framework, Views are “readonly” due to the DefiningQuery element. There are two approaches to make a view editable:

  1. Create stored procedures for Insert/Update/Delete: This is described in this document and this walkthrough.
  2. Make Entity Framework treat views as tables by doing some manual modifications in the generated EDMX file: This is described in this post.

A few days back, I created an entity model on top of views instead of tables. Since my physical model consisted of Views and not tables, so Entity Designer was not able to infer primary keys/relations etc., and I had to create them manually. In this post, I will highlight the steps I took to create a working entity model by hand. Please read at your own risk and feel free to contribute your ideas using the comments section. Note that not all views are updatable, and this MSDN document describes certain properties that a view definition must hold in order to be updatable.

Assume we have a JobWBS and a ResourceAssignment view with a One-to-Many relationship as depicted below:


If we try to create an “ADO.NET Entity Data Model” for the above views, Visual Studio will generate a model similar to this one.


Notice that the designer marked all fields as primary keys. We need to manually edit the generated edmx file by opening it with “XML editor”.


You will also notice the following warnings inside the edmx xml:

Errors Found During Generation:
warning 6002: The table/view 'testdb.dbo.vw_JobWBS' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

Read the rest of this entry »

Entity Framework: TimeStamp fields not recognized for optimistic concurrency model

Today, while working with Entity Framework, I noticed through SQL Profiler that my update queries were not using timestamp field. I looked into my model and found that my timestamp fields have Concurrency Mode property set to None. It was quite strange; The entity model was auto generated and I wonder why the EF designer could not identify my timestamp field (I remember Linq To SQL designer automatically took care of such fields). Anyway, I changed Concurrency Mode to Fixed and it worked great. So, as a final note, when generating entity model from database, don’t forget to set appropriate concurrency modes for your timestamp fields as it is not done by the designer itself.

setting concurrency mode for timestamp field

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.