Using Raw Queries in Entity Framework

One question I see asked a lot in the various forums is:

"How do a do use raw SQL using Entity Framework?"

Many people think that, because they're using an ORM, they don't have access to the underlying database connection that they would have when using ADO.NET, and so can't perform many of the more specialist things they might want to do.

Although this might have been true in earlier versions of Entity Framework, the current versions offer you far more power and flexibility.

For the purposes of this post, we're going to assume EF 5 or higher. The current version available on NuGet is 6.1.3 as of me writing this.

Let's do what we always do at this point and start a new Console mode project.

I previously did an article about getting started quickly and easily with EF in this column

Rather than go through the full process of describing how to set up the sample app, I'll make the assumption that you read the previous post and have a console mode app ready to go.

The Data Class I'm using for this sample looks as follows:

namespace efrawqueries
{
   public class TestData
   {
      public int Pkid { get; set; }
      public string FirstName { get; set; }
      public string Surname { get; set; }
      public int Age { get; set; }
   }
}

I've also set my EF database class up so it looks like the following:

using System.Data.Entity;

namespace efrawqueries
{
   class Database : DbContext
   {
      public Database() : base("demoConnectionString")
      { }

      public DbSet<TestData> TestData { get; set; }

      protected override void
         OnModelCreating(DbModelBuilder modelBuilder)
      {
         modelBuilder.Entity<TestData>()
            .ToTable("TestData");
         modelBuilder.Entity<TestData>()
            .HasKey(x => new {x.Pkid});
      }

   }

}

It's up to you how you model your data, but from this point on anything I show will be based on the preceding data model.

The final part of this sample will be the main program.cs file, which looks as follows:

using System.Collections.Generic;

namespace efrawqueries
{
   class Program
   {
      static void CreateTestData()
      {
         using(var database = new Database())
         {
            var testRecords = new List<TestData>()
            {
               new TestData() {Pkid = 1, FirstName = "Peter",
                  Surname = "Shaw", Age = 21},
               new TestData() {Pkid = 2, FirstName = "Alan",
                  Surname = "Person", Age = 22},
               new TestData() {Pkid = 3, FirstName = "Jane",
                  Surname = "Doe", Age = 30},
               new TestData() {Pkid = 4, FirstName = "John",
                  Surname = "Smith", Age = 40},
               new TestData() {Pkid = 5, FirstName = "Uncle",
                  Surname = "Bob", Age = 50}
            };

            database.TestData.AddRange(testRecords);
            database.SaveChanges();
         }
      }

      static void Main()
      {
         CreateTestData();
      }

   }
}

If you compile and run this as is, you should end up with a demo table in your database containing five rows of data:

Raw1
Figure 1: Our database table with data in it

Once we've got the data into our database, go ahead and comment out the call to the "CreateTestData" function. Otherwise, it'll start causing us problems.

At this point, you easily can get your data back by using normal EF functionality as follows:

static void Main()
{
   //CreateTestData();
   using (var testDatabase = new Database())
   {
      var records = testDatabase.TestData;
      foreach (var record in records)
      {
         Console.WriteLine("{0}:{1}:{2}:{3}", record.Pkid,
            record.FirstName, record.Surname, record.Age);
      }
   }
}

Which, when run, should give you something like:

Raw2
Figure 2: Getting our data back by using normal EF methods

From Entity Framework 5 and onwards, the data context object (that's your DbContext-derived class that you use in the prreceding using statement) has a new method on it, called "SqlQuery". You use it by calling into the "Database" property on your context and passing an SQL String to it to execute.

Alter the main function in your program.cs to see what I mean:

static void Main()
{
   //CreateTestData();
   using (var testDatabase = new Database())
   {
      var result = testDatabase.Database.SqlQuery<int>
         ("select count(*) from dbo.TestData").First();
      Console.WriteLine("You have {0} records.", result);
   }
}

In this case, we're using the first version of the "SqlQuery" call that takes a generics parameter to specify the return type, then extracts only the first result returned.

We do this because every call to SqlQuery will return an IEnumerable collection, even if you just call something that returns a simple scalar as we did above.

There is also a second overload, one that takes the type and sql string and an array of option objects. It can include options for parameter-based SQL calling.

If you wanted to use "SqlQuery" to get back actual records based on your model, you could easily do the following:

static void Main()
{
   //CreateTestData();
   using (var testDatabase = new Database())
   {
      var results = testDatabase.Database.SqlQuery<TestData>
         ("select * from dbo.TestData");
      foreach (var record in results)
      {
         Console.WriteLine("{0}:{1}:{2}:{3}", record.Pkid,
            record.FirstName, record.Surname, record.Age);
      }
   }
}

This will give the same results as using the "DbSet" approach. You could, however, now create models in your DbContext, and easily make calls to Stored Procedures and other database functions and have the output returned directly into Entity Framework-based POCOs (Plain Old Class Objects) should you want to.

Got a tricky .NET problem you're trying to solve, or just got an interesting tip you might want to add to the Nuts & Bolts toolbox? Send me a Tweet, @shawty_ds, or drop a comment in the comments below and let me know your thoughts.



About the Author

Peter Shaw

As an early adopter of IT back in the late 1970s to early 1980s, I started out with a humble little 1KB Sinclair ZX81 home computer. Within a very short space of time, this small 1KB machine became a 16KB Tandy TRS-80, followed by an Acorn Electron and, eventually, after going through many other different machines, a 4MB, ARM-powered Acorn A5000. After leaving school and getting involved with DOS-based PCs, I went on to train in many different disciplines in the computer networking and communications industries. After returning to university in the mid-1990s and gaining a Bachelor of Science in Computing for Industry, I now run my own consulting business in the northeast of England called Digital Solutions Computer Software, Ltd. I advise clients at both a hardware and software level in many different IT disciplines, covering a wide range of domain-specific knowledge—from mobile communications and networks right through to geographic information systems and banking and finance.

Related Articles

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date