Entity Framework supports a concept called a ‘Defining Query’, which is basically a client side database View.  I only learned about that feature a few days ago, and I thought I’d share some tips about how to create them in a way that’s much easier than anything I could find out there.

The first thing you need to know is that there is no Designer Support for using this feature, and that means you have to be prepared to edit your .edmx file as XML.  If this sounds too scary, stop here! :)  The difference is that the technique I found lets you make extremely simple changes, whereas the ‘standard’ technique is plain scary.

So let’s take a quick look at the standard technique, which is explained on MSDN.  Basically, you have to add a ton of XML in many different places, and I personally wouldn’t want to do this by hand.  With my ‘trick’, you don’t need to change any XML.  All you have to do is paste your SQL query in there.

So what exactly is my trick?  It relies on temporarily creating a real database View (i.e. a server view), and have that drive all the XML creation for you!  Here are the basic steps:

  1. Create a regular SQL View
  2. Map it into the EF model using the designer
  3. Copy the SQL View’s query, and paste it into the SSDL’s <DefiningQuery> tag, replacing the query that’s there!
  4. Now you can delete the SQL View

 

A step by step example using everyone’s favorite database

Note: the sample is available on BitBucket.

 

So let’s say our goal is to create a Defining Query for Northwind called ProductsWithCategoryName, which returns products along with the name of the category they’re in.

Step 0: setting things up

Not really a step, but let’s assume that you already have some app (could be a console app) that has imported Northwind, so you’re able to go in Server Explorer and see the Tables, …

 

Step 1: Creating a real SQL View

So even though our goal is to create a Defining Query, we’ll create a real SQL view here (we’ll delete it later).  So let’s create our simple View:

  • Right click on Views / Add New View
  • In the Add Tables dialog, pick both Products & Categories and click Add.  It’s smart enough to guess that you want to use an INNER JOIN here.
  • Click the columns that you care about.  e.g. let’s pick ProductID, ProductName and UnitsInStock from the Products table, and CategoryName from the Categories table.

image

This automatically creates the View’s query for you, so you don’t actually need to know any SQL.  Your query should look like this:

SELECT        dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.UnitsInStock, dbo.Categories.CategoryName
FROM            dbo.Categories INNER JOIN
                         dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID
  • Save that query in your clipboard, you’ll need it soon!
  • Now just save the View as ‘ProductsWithCategoryName’

 

Step 2: Import the view in an EF model

 

So far, we haven’t touch EF.  Now is the time!

  • In your project, add a new “ADO.NET entity data model”
  • Choose Generate From Database, and pick your Northwind
  • When you get to ‘Choose Your Database Objects’, pick your ProductsWithCategoryName View.  You can also include other things if you want.

 

Step 3: paste your query in the edmx file

 

This is the step!  The one that we actually start talking about your ‘defining query’.

  • First you’ll need to open your edmx as XML: right click it, Open With, and choose Automatic XML.  In there, near the top , you should see something like this: 
<EntitySet Name="ProductsWithCategoryName" EntityType="NorthwindModel.Store.ProductsWithCategoryName" store:Type="Views" store:Schema="dbo" store:Name="ProductsWithCategoryName">
  <DefiningQuery>
      SELECT
      [ProductsWithCategoryName].[ProductID] AS [ProductID],
      [ProductsWithCategoryName].[ProductName] AS [ProductName],
      [ProductsWithCategoryName].[UnitsInStock] AS [UnitsInStock],
      [ProductsWithCategoryName].[CategoryName] AS [CategoryName]
      FROM [dbo].[ProductsWithCategoryName] AS [ProductsWithCategoryName]
  </DefiningQuery>
</EntitySet>

It already has a DefiningQuery, but it’s defined in term of your server View, which is no good since we plan to get rid of that view!  So simply paste the query you copied in step 1 on top of it.  You now have: 

<EntitySet Name="ProductsWithCategoryName" EntityType="NorthwindModel.Store.ProductsWithCategoryName" store:Type="Views" store:Schema="dbo" store:Name="ProductsWithCategoryName">
  <DefiningQuery>
      SELECT        dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.UnitsInStock, dbo.Categories.CategoryName
      FROM            dbo.Categories INNER JOIN
      dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID
  </DefiningQuery>
</EntitySet>

And that’s it!  If you look down in the edmx, you’ll see that all the other scary things that the MSDN article tells you to create by hand are already there!  This happened for free because your SQL View (which the designer does support) has the exact same structure as your ‘Defining Query’.

 

Step 4: delete the SQL View

 

The SQL view we created in step 1 has served its purpose, so you can delete it if you want.  Even though it has done all the hard work for you, and asked for nothing in return.  Go ahead, delete it! :)

 

Using the defining query in your code

 

Now you can just use your Defining Query in your code as you would if it were a regular View.  e.g.

static void Main(string[] args) {
    var context  = new NorthwindEntities();
    foreach (var p in context.ProductsWithCategoryNames) {
        Console.WriteLine(String.Format("{0} / {1} / {2}", p.ProductName, p.CategoryName, p.UnitsInStock));
    }
}

One caveat you need to be aware of is that since the designer doesn’t support defining queries, it won’t preserve your change if you re-save the edmx through the designer.  Apparently, they’re planning to fix that.

Anyway, that was my little contribution to the Entity Framework world…