How to Implement Native SQL Server Queries with the Entity Framework

By: Ray Barley | Updated: 2010-04-27 | Comments (1) | Related: > Entity Framework

Problem

I have some complex queries that I need to run against my SQL Server database and I'm using the Entity Framework (EF) in my application. I'm having difficulty crafting these queries using LINQ to Entities. What I really need is to just inject my native queries into the Entity Framework model. Is this possible? Check out this tip to find the solution.

Solution

The Entity Framework (EF) does allow you to add your own native SQL queries to the EF model. This feature is called a Defined Query. The easiest way to get familiar with the Defined Query is to take a look at how EF works with a database view. When you add a database view to your EF model, the view is actually implemented using a Defined Query. Just like a view, with a Defined Query you get a read-only entity that represents your result set.

In this tip we will review the XML for an EF model that has a single entity which is a database view, then use our understanding to implement a native query using a Defined Query.

We will use the following sample database schema as an example:

The Entity Framework (EF) does allow you to add your own native SQL queries to the EF model

The above schema is a hypothetical one for the MSSQLTips web site. We have the following view which is used to get us the information we need for a particular web page:

CREATE VIEW [dbo].[vTipList]
AS
SELECT 
  t.TipID
, a.FirstName + ' ' + a.LastName Author
, t.Title
, c.TipCategoryName
, t.PublishDate
, t.URL
FROM  dbo.Author a
INNER JOIN dbo.Tip t ON t.AuthorID = a.AuthorID 
INNER JOIN dbo.TipCategory c ON c.TipCategoryID = t.TipCategoryID
WHERE (t.PublishDate IS NOT NULL)

What we would like to do is create a query that will show us the most discussed tips, which we define as those tips that have 3 or more comments in the past 24 hours. In the next sections we will review the EF model to see how it handles a database view then add a native query to the model to retrieve the most discussed tips.

Reviewing the EF Model

An EF model in a Visual Studio project is an XML file with a .edmx extension. From your Visual Studio project you can right click on an EF model in the Solution Explorer, select Open With from the popup menu, then choose XML Editor to view (or edit) the XML of the EF model. The following is an example of the XML view for an EF model:

There are four main sections of the EF model as shown above:

SSDL content represents the schema of the underlying database
CSDL content is the conceptual schema; it is the model that we use in our code and it can be quite different than the SSDL content (this is a great feature in EF allowing us to hide the underlying schema from our application code)
C-S mapping content transforms the SSDL into the CSDL
EF Designer content contains information used to visually render the model in the designer view; we typically ignore this section

When you expand the edmx:StorageModels node, there are two noteworthy elements inside of the Schema element. First we have the EntitySet element as shown below:

Think of the EntitySet as the name of a collection of entities of a particular type. The Name is vTipList and the EntityType is EFTipsSampleModel.Store.vTipList. Note that the DefinedQuery element is just a standard T-SQL select. We can specify just about any select statement we like within a DefinedQuery element.

The second noteworthy element inside of the Schema element is the EntityType as shown below:

In the case of a Defined Query, think of the EntityType as the list of columns that are returned from a select statement. In order to specify our own native query in the EF model, we will need an EntitySet and an EntityType element.

The edmx:ConceptualModels element in the CSDL content section is shown below:

The above EntityType matches what we saw in the SSDL content section because the conceptual model is the same as the schema in this case. Note that the conceptual model is the basis for the classes that get generated by EF that we use in our code. The Key element above includes all columns that do not allow NULL since there isn't a primary key in a view.

The last section is the C-S mapping content which contains the edmx:Mappings element as shown below:

As the name suggests this section maps the underlying database schema to the EntityType in the conceptual layer. In this case the mapping is one-to-one.

Creating a Defined Query

Based on our review of the EF model's XML, we are now ready to add our native query to the model. In order to implement the Defined Query we will have to manually edit the EF model's XML file. Having to edit an XML file is not an ideal solution. In addition there is a caveat: if you update the model from the database, you will have to perform the edits to the XML file again.

We'll use the following query to retrieve the most discussed tips:

SELECT
  t.TipID
, a.FirstName + ' ' + a.LastName Author
, t.Title
, c.TipCategoryName
, t.PublishDate
, t.URL
FROM dbo.Author a
INNER JOIN dbo.Tip t ON t.AuthorID = a.AuthorID
INNER JOIN dbo.TipCategory c ON c.TipCategoryID = t.TipCategoryID
INNER JOIN (
  SELECT
    TipID
  , COUNT(*) CommentCount
  FROM dbo.TipComment
  WHERE CommentDate > DATEADD(DAY, -1, GETDATE())
  GROUP BY TipID
  HAVING COUNT(*) > 3
) x ON x.TipID = t.TipID

The above query selects columns from the relevant tables to retrieve the information about each tip, then joins to a nested select that gets the list of tips that have more than 3 comments in the past 24 hours. There is one important change that you must make to the above query. Since you will be copying the query inside of the DefinedQuery element in the EF model's XML file, you have to use escaped notation for any less than or greater than symbols; e.g. < and >. This is required to not interfere with the XML which uses these symbols to delimit the beginning and end of each element.

In order to minimize the editing of the XML file, the above query returns the same columns as the sample view that we used in our review of the EF model's XML file. This allows us to simply replace the T-SQL in the DefinedQuery element with our query above.

Since things aren't this simple in the real world, let's state exactly what needs to be done to add your own native query to the EF model as a Defined Query:

Create your query and make sure that it returns the results you expect
Add the EntitySet and EntityType elements to the SSDL content and put your query in the DefinedQuery element inside of the EntitySet
Add the EntitySet and EntityType elements to the CSDL content
Add the EntitySetMapping element to the C-S mapping content

Remember that to simplify this process, you could temporarily add a view to your database that returns the columns that you need in your native query, add that view to the EF model, then copy and paste your native query into the DefinedQuery element for the view.

Sample Code

Now we are ready to write some sample code to exercise our Defined Query in the EF model. You can take a look at the code generated by EF; you will find it in the MSSQLTipsModel.Designer.cs file of the sample Visual Studio project. There are a couple of noteworthy things to point out:

The EFTipsSampleEntities class is the container for your entities; this class name was automatically generated when the model was created
The vTipList class represents the entity created from the view of the same name; it has a property for each column in the view (which matches the columns returned by our Defined Query)

The following is the simplest bit of code that will perform a query using the Defined Query and display each row and its columns:

  using (EFTipsSampleEntities ctx = new EFTipsSampleEntities())
  {
    var result = from t in ctx.vTipList
                 select t;
    foreach (vTipList t in result)
    {
      Console.WriteLine("{0} {1} {2} {3} {4}",
          t.TipID, t.Author, t.TipCategoryName,
          t.Title, t.URL);
    }
  }

The above code snippet uses LINQ to Entities in order to perform the query (shown in bold). The output looks like this:

The above query result returns a single row which is consistent with the sample data.

Next Steps

You can use a Defined Query to add a native query to your EF model; this can be a good approach when you have a complex query.
A benefit of the Defined Query is that you don't have to add a database object such as a view. While creating the view is a simple task, sometimes it's not desirable to make any changes to the database.
Another benefit of the Defined Query (and a view also) is that you can use it to easily add your own calculated columns to the model.
You can download a backup of the SQL Server 2008 sample database and the Visual Studio 2008 sample project here and use it as a basis for your own testing and experimentation.
For an introduction to Entity Framework, take a look at our earlier tip Intro to Entity Framework with SQL Server.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2010-04-27