Freigeben über

The easy way to create an Entity Framework ‘Defining Query’

  • Artikel

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…

Comments

  • Anonymous
    October 28, 2010
    Nice - Thanks for the post..

  • Anonymous
    November 09, 2010
    <Not a dev, just messing about> This is great and I can use this to create LIST views very easily. Can you also show a view with the Category field as a drop down? Thanks! -p

  • Anonymous
    March 11, 2011
    Careful not to use if you need to do any DML.  Seems as though you have to write stored procs to handle these.

  • Anonymous
    May 22, 2012
    if the entity updated again you will get this error Error 11007: Entity type 'ProductWithCategoryName' is not mapped. So don't delete the view from SQL

  • Anonymous
    July 24, 2012
    The comment has been removed

  • Anonymous
    August 25, 2012
    I have an issue , when i add the view to the model , it dosn't appear

  • Anonymous
    September 17, 2012
    Just a small question : how do you do if you have a condition "<>" in your sql query?

  • Anonymous
    September 02, 2014
    Thanks a ton, David for the clear cut solution. Other articles are like bla bla bla including MSDN.

  • Anonymous
    June 05, 2015
    Hey just keep the view. They really need to fix some of this designer stuff that does not work. Also, need to add some type of field to say don't over write this defining query on regeneration. I know the EF team moved fast but it's time to fix this stuff. I had to revert to using xml editor again yesterday. If you rename a view in SQL Server you have to manually go in and rename it. Should be able to rename view as needed. How about if you need to add more selection criteria to an existing view? CoderBoy