Entity framework and SQL queries and non EF generated types:
by Hans on 2014/06/25 15:27
in Categories: C#, Entity Framework, SQL

Entity framework and SQL queries and non EF generated types

So Entity Framework provides us with a basis for creating an easy to use data repository for our projects. Depending on your approach code first or the other way round generally the net effect is you add a table and you update the relevant edmx to reflect the newly added table and start writing some queries against the repository with your newly added table.

There is always an exception to the rule. Assume you have a complex SQL query that for one or more reasons cannot be achieved using a LINQ query. Well EF provides a nifty solution that allows you to explicitly define a SQL statement which will return data.

The below code snippet marked Example 1 shows exactly how this is achieved.

Example 1.

TestEntities db = new TestEntities();

public IEnumerable<T> GetSales<T>()
        return db.Database.SqlQuery<T>(SqlQueries.DealsGetAll);

public IEnumerable<Deal> GetSales()
        return db.Database.SqlQuery<Deal>("select * from dbo.Deals");

So take a look at the second method I call the db.Database.SqlQuery method which accepts a SQL query. EF runs the query returns the results and maps this to the Deal object effectively returning a list of List<Deal>.Note the method has overloads so you can also specify parameters.

What is really cool about this method is that the object DEAL being returned does not have to be in the edmx. Under the hood EF uses reflection and build a list of deals List<Deal> matching column names with object properties. So if you look at the first method I actually inject the type I want returned. Even cooler the object type does not even have to be a known type. It can be any type as long as EF can match column and property names.

So here’s something else you can do to manage these SQL queries. In my data access assembly I have created “SqlQueries” folder. Each of the SQL queries I create are saved in a text file and given an identifying name like “DealsGetAll.txt”. I then add a resource file to the assembly and add the text file to the resource file. So if you look carefully at the first method I created I actually pass the name of the text file containing my SQL query text to the SqlQuery method.

So this has been short but lots of fun. Until next time Happy Hacking !!

Post a Comment

Your Name:*  OR Screen Name:
Email Address:*
Post your Comment

    Note. All comments are sent to a moderator for approval prior to appearing on this page.

    © 2012 Saxon Systems. All rights reserved.Terms of Use  Privacy Policy  Contacts