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.
NOTE: Yes I know I made a spelling mistake. It's not Nationalty but Nationality... My bad :(
Data in the DB:
Persons
Adresses
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:
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.
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.
public void NormalQuery()
{
Console.WriteLine("List ALL Persons in the database:");
Console.WriteLine("");
foreach (var person in entities.Persons)
{
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.
public void NormalQueryWithLinqSearch()
{
Console.WriteLine("List ALL Persons in the database who is British:");
Console.WriteLine("");
foreach (var person in entities.Persons.Where(x => x.Nationalty == "British"))
{
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:
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:
public void RawSQLQueryFailing()
{
Console.WriteLine("List ALL Persons in the database (raw sql query):");
Console.WriteLine("");
foreach (var person in entities.Database.SqlQuery<Person>("SELECT * FROM dbo.Persons"))
{
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:
public void RawSQLQuery()
{
Console.WriteLine("List ALL Persons in the database (raw sql query):");
Console.WriteLine("");
foreach (var person in entities.Database.SqlQuery<Person>("SELECT * FROM dbo.Persons"))
{
Console.WriteLine(string.Format("Name: {0} {1} ; Birthdate: {2}; Nationality: {3} ",
person.Firstname,
person.Lastname,
person.BirthDate.Value.ToShortDateString(),
person.Nationalty
));
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.
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:
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:
public void RawSQLQueryWithCustomObject()
{
Console.WriteLine("List ALL Persons in the database (raw sql query with custom object):");
Console.WriteLine("");
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"))
{
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.
public void RawSQLQueryWithCustomObjectWithWhere()
{
Console.WriteLine("List ALL Persons in the database (raw sql query with custom object with WHERE statement):");
Console.WriteLine("");
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' "))
{
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.
