Click here to Skip to main content
15,867,488 members
Articles / Web Development / ASP.NET

Custom Queries with RAW SQL under ADO.NET EF

Rate me:
Please Sign up or sign in to vote.
4.33/5 (5 votes)
15 Jun 2014CPOL4 min read 36.8K   335   16   2

Introduction

In this article I'm going to explain how to build and use custom object queries under ADO.NET Entity Framework with  raw SQL. I won't explain how to make an ADO.NET Entity Framework model, or connect it to the database, there are plenty of articles on the internet how to do that. So this article is for devs who have basic understanding in ADO.NET

Background

When you are dealing with a big amount of data in the database, a normal LINQ query will not seem to be the perfect choice, because the query might not be fast enough. If ther are ~100.000 records in a table, and you might want to not just list but search something with a criteria in that table, that could take many seconds (the time depends on complexity of course). Or you just want to make a special query, which depends on the user interaction, sometimes it's much easier to write - and change depending on user interaction - a single SQL query and give it to the entity context. 

The first time I had to make custom queries in EF, it took pretty much time and research till I found something useful, and those articles didn't have much detail about how to do this kind of stuff - only basic things -, and none of them explained deeply how to use this kind of solution. (Maybe there are good, detailed articles I just couldn't find any.) So after all, I wanna share what I learnt about this with you. I hope you'll have a good use of it.

Using the code

I'm gonna use DB first configuration.

Basic explanation can be found here:

http://msdn.microsoft.com/en-us/data/jj592907.aspx

But I wanna go and explain things deeper than this. 

 

We will have a table called Person. The person will have address(es) from another table.
Image 1

 

NOTE: Yes I know I made a spelling mistake. It's not Nationalty but Nationality... My bad :(

Data in the DB:

Persons

Image 2

Adresses

Image 3

Let's create an EF model from this database. (Everything can be found in the source file.)

The model class create by EF is named DbEntities in the project.

Classes created by the EF model wizard:

C#
public partial class Address
    {
        public int ID { get; set; }
        public string Street { get; set; }
        public string City { get; set; }
        public string ZIP { get; set; }
        public Nullable<int> PersonID { get; set; }
    
        public virtual Person Person { get; set; }
    }

public partial class Person
    {
        public Person()
        {
            this.Addresses = new HashSet<Address>();
        }
    
        public int ID { get; set; }
        public string Firstname { get; set; }
        public string Lastname { get; set; }
        public Nullable<System.DateTime> BirthDate { get; set; }
        public string Email { get; set; }
        public Nullable<int> IDNumber { get; set; }
        public string Nationalty { get; set; }
    
        public virtual ICollection<Address> Addresses { get; set; }
    }

 

The class I made for testing called EFQueryTest. You can find every method what shows how queries can be done in that single class.

C#
class EFQueryTest
   {
       DbEntities entities = new DbEntities();

       ...
       ...
   }

First test:

Normal query.

This is what is used the most. Just call the entity framework methods and datasets.

C#
public void NormalQuery()
       {
           Console.WriteLine("List ALL Persons in the database:");
           Console.WriteLine("");

           // call the persons dataset from the Entity model.
           foreach (var person in entities.Persons)
           {
               //write out datas of the person
               Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                   person.Firstname,
                                                   person.Lastname,
                                                   person.BirthDate.Value.ToShortDateString(),
                                                   person.Nationalty
                   ));
               Address personsAddress = person.Addresses.FirstOrDefault();
               Console.WriteLine("Address: {0}, {1} {2} ", personsAddress.Street, personsAddress.City, personsAddress.ZIP);
           }

           Console.ReadKey();
       }

 

Second test:

Normal query with LINQ search.

C#
public void NormalQueryWithLinqSearch()
       {
           Console.WriteLine("List ALL Persons in the database who is British:");
           Console.WriteLine("");

           // call the persons dataset from the Entity model, who's nationality is British

           foreach (var person in entities.Persons.Where(x => x.Nationalty == "British"))
           {
               //write out datas of the person
               Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                   person.Firstname,
                                                   person.Lastname,
                                                   person.BirthDate.Value.ToShortDateString(),
                                                   person.Nationalty
                   ));
               Address personsAddress = person.Addresses.FirstOrDefault();
               Console.WriteLine("Address: {0}, {1} {2} ", personsAddress.Street, personsAddress.City, personsAddress.ZIP);
           }

           Console.ReadKey();
       }

These two were the warming up. I guess everyone who has ever used EF knows these kind of methods shown above.

 

Creating a query with raw SQL.

According to the article I pasted above it can be done like this:

C#
using (var context = new BloggingContext()) 
{ 
    var blogNames = context.Database.SqlQuery<string>( 
                       "SELECT Name FROM dbo.Blogs").ToList(); 
}

As it can be seen we can set the object type what we want back from the EF.
Database.SqlQuery<TElement> --> TElement stands for the Object what we want back.

So lets use this on our EF model.

We want back our Person object from the model, so it will be like this:

Database.SqlQuery<Person>("SQL QUERY").ToList();

Lets make a test!

Here we have this code snippet:

C#
public void RawSQLQueryFailing()
        {
            Console.WriteLine("List ALL Persons in the database (raw sql query):");
            Console.WriteLine("");
            // call the persons from the Entity model with raw sql query

            foreach (var person in entities.Database.SqlQuery<Person>("SELECT * FROM dbo.Persons"))
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    person.Firstname,
                                                    person.Lastname,
                                                    person.BirthDate.Value.ToShortDateString(),
                                                    person.Nationalty
                    ));
                Address personsAddress = person.Addresses.FirstOrDefault();
                Console.WriteLine("Address: {0}, {1} {2} ", personsAddress.Street, personsAddress.City, personsAddress.ZIP);
            }

            Console.ReadKey();
        }

It's very important, that this method will fail! WHY?

Because we didn't get the address from the database!

As you can see on the SQL query "SELECT * FROM dbo.Persons", we selected only the Persons from the database, if you run this method the datas of the first Person will be written out to the console, but when the method gets to that part when it needs to write out the Address, it will fail because it didn't get the Address object from the database.

We should get the Address for the Persons. Alas we're not able to use JOINs this time. Though the query would get the data from the database but wouldn't assign it to the Address object in the Person class.

We can do something like this:

C#
public void RawSQLQuery()
        {
            Console.WriteLine("List ALL Persons in the database (raw sql query):");
            Console.WriteLine("");
            // call the persons from the Entity model with raw sql

            foreach (var person in entities.Database.SqlQuery<Person>("SELECT * FROM dbo.Persons"))
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    person.Firstname,
                                                    person.Lastname,
                                                    person.BirthDate.Value.ToShortDateString(),
                                                    person.Nationalty
                    ));
// here we make a new query and assign the address object
                Address personsAddress = entities.Database.SqlQuery<Address>("SELECT * FROM dbo.Addresses AS a WHERE a.PersonID = " + person.ID).FirstOrDefault();
                Console.WriteLine("Address: {0}, {1} {2} ", personsAddress.Street, personsAddress.City, personsAddress.ZIP);
            }

            Console.ReadKey();
        }

This is far not the best solution! We're making two queries, it's two unnecessary requests to the db.

The best if we make a new object what we can use to make a new query and assign any values from the database we want.

C#
public class CustomQueryObject
{
    public string PersonFirstname { get; set; }
    public string PersonLastname { get; set; }
    public DateTime PersonBirthdate { get; set; }
    public string PersonNationality { get; set; }
    public string AddressStreet { get; set; }
    public string AddressCity { get; set; }
    public string AddressZIP { get; set; }
}

The SQL query will be a bit more complex than the ones before:

SQL
SELECT 
p.Firstname as PersonFirstname,
p.Lastname as PersonLastname,
p.BirthDate as PersonBirthDate,
p.Nationalty as PersonNationality,
a.City as AddressCity,
a.ZIP as AddressZIP,
a.Street as AddressStreet
FROM dbo.Persons as p
LEFT JOIN dbo.Addresses as a on a.PersonID = p.ID

 

As you can see, the relation with the object properties is the assigned names of the SELECT part.

Lets see how the code looks like:

C#
 public void RawSQLQueryWithCustomObject()
        {
            Console.WriteLine("List ALL Persons in the database (raw sql query with custom object):");
            Console.WriteLine("");
            // call the persons dataset from the Entity model with raw sql and custom object

            foreach (var item in entities.Database.SqlQuery<CustomQueryObject>(
                               "SELECT " +
                               " p.Firstname as PersonFirstname," +
                               " p.Lastname as PersonLastname," + 
                               " p.BirthDate as PersonBirthDate," + 
                               " p.Nationalty as PersonNationality," +
                               " a.City as AddressCity," +
                               " a.ZIP as AddressZIP," +
                               " a.Street as AddressStreet" +
                               " FROM dbo.Persons as p" +
                               " LEFT JOIN dbo.Addresses as a on a.PersonID = p.ID"))
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    item.PersonFirstname,
                                                    item.PersonLastname,
                                                    item.PersonBirthdate.ToShortDateString(),
                                                    item.PersonNationality
                    ));

                Console.WriteLine("Address: {0}, {1} {2} ", item.AddressStreet, item.AddressCity, item.AddressZIP);
            }

            Console.ReadKey();
        }

With this kind of solution we can use every SQL functions. 

Here's an other small example what shows a simple SQL query with a WHERE clause. This will get all persons who's nationality is British.

C#
public void RawSQLQueryWithCustomObjectWithWhere()
        {
            Console.WriteLine("List ALL Persons in the database (raw sql query with custom object with WHERE statement):");
            Console.WriteLine("");
            // call the persons dataset from the Entity model with raw sql and custom object

            foreach (var item in entities.Database.SqlQuery<CustomQueryObject>(
                                      "SELECT " +
                                      " p.Firstname as PersonFirstname," +
                                      " p.Lastname as PersonLastname," +
                                      " p.BirthDate as PersonBirthDate," +
                                      " p.Nationalty as PersonNationality," +
                                      " a.City as AddressCity," +
                                      " a.ZIP as AddressZIP," +
                                      " a.Street as AddressStreet" +
                                      " FROM dbo.Persons as p" +
                                      " LEFT JOIN dbo.Addresses as a on a.PersonID = p.ID"+
                                      " WHERE p.Nationalty = 'British' "))
            {
                //write out datas of the person
                Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
                                                    item.PersonFirstname,
                                                    item.PersonLastname,
                                                    item.PersonBirthdate.ToShortDateString(),
                                                    item.PersonNationality
                    ));

                Console.WriteLine("Address: {0}, {1} {2} ", item.AddressStreet, item.AddressCity, item.AddressZIP);
            }

            Console.ReadKey();
        }

Summary

With this solution you will be able to use every SQL functions, what sometimes can be much smoother and faster than using EntityFramework functions. You can make normal DateTime comparisons, which can't be done in EF. You can use sql functions like SUM, CONCAT, CONVERT etc. 

 

If you've found this article helpful, please vote for the article, leave a message, and feel free to post back to this article. 

 

History

  • 15 June, 2014: Initial release.

 

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Hungary Hungary
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionReturn dynamic result from store-procedures with EF Pin
gazmendmehmeti17-Jun-14 21:21
gazmendmehmeti17-Jun-14 21:21 
AnswerRe: Return dynamic result from store-procedures with EF Pin
Tamas Lorincz12-Oct-14 4:35
Tamas Lorincz12-Oct-14 4:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.