Entity Framework & Direct SQL

Entity Framework is a great time saver.

When I think back to the days when I had to manually construct SQL statements, parse the results, control updates, and of course ensure my actual database design was in sync with what my code expected – well it sends a shiver down my spine!

I remember back in the day I even built myself a little utility that would take my database and spit out boiler plate code for basic CRUD operations.

But then EF was released – and although it was a little bit flakey at first (with people tutting and muttering about NHibernate), it was still a lovely little thing.  These days of course I use it all the time.  In fact as you may have seen in one of my earlier posts I’ve even copied more than a little for an Azure Table Storage version.

And of course couple EF with decent patterns such as the repository pattern and Dependency Injection and you have a rock solid foundation.

But, (there’s always a but) – EF is sometimes a bit slow when compared with issuing SQL commands directly – especially with batch operations.

For this reason the EF context exposes a Database property which in turn exposes a number of options for issue SQL commands directly.

I will show you how to use the two most common ones – SqlQuery and ExecuteSqlCommand

As I like to decouple as much as possible, and because I like my interfaces to only depend on core libraries I’m going to hide away some of the EF specific stuff.

So, first of all I like to have an IRepository<TEntity> interface and a Repository<TEntity> base class, that way each repository gets the basic CRUD methods and anything else I might want;

public interface IRepository where TEntity: class
void Delete(object id);
void Delete(TEntity entity);
System.Linq.IQueryable GetAll();
System.Linq.IQueryable GetAll(object filter);
IPagedResponseViewModel GetPaged(int take, int skip, string orderBy, bool orderByAscending, object filter);
TEntity GetById(object id);
TEntity GetFullObject(object id);
void Insert(TEntity entity);
void Update(TEntity entity);
void Commit();
void Dispose();
// Direct SQL Stuff
int ExecuteSqlCommand(string sql, object[] parameters);
ICollection SqlQuery(string sql, object[] parameters);
int ExecuteSqlCommand(string sql);
ICollection SqlQuery(string sql);
 public abstract class RepositoryBase : IRepository where TEntity : class
        internal DataContext context;
        internal DbSet dbSet;

        public RepositoryBase(DataContext context)
            this.context = context;
            this.dbSet = context.Set();

        public virtual int ExecuteSqlCommand(string sql)
            return context.Database.ExecuteSqlCommand(sql);
        public virtual int ExecuteSqlCommand(string sql, object[] parameters)
            return context.Database.ExecuteSqlCommand(sql, parameters);
        public virtual ICollection SqlQuery(string sql)
            return context.Database.SqlQuery(sql).ToList();
        public virtual ICollection SqlQuery(string sql, object[] parameters)
            return context.Database.SqlQuery(sql, parameters).ToList();
        public virtual void Commit()
        public virtual void Dispose()

As you can see all we are really doing is encapsulating the SqlQuery and ExecuteSqlCommand methods by taking in a string or a string and a list of parameters.

First lets look at ExecuteSQLCommand – as this is very straight forward, we simply pass in our SQL string such as

ExecuteSQLCommand("UPDATE SomeTable SET SomeColumn='Some Value'");

EF issues the command and returns an int indicating the number of affected rows.
If you want you can pass in parameters like this

ExecuteSQLCommand("UPDATE SomeTable SET SomeColumn='Some Value' WHERE ID=@p0", new object[]{3});

Now for SQLQuery. You’ll may notice SQLQuery uses TEntity (if you’re not familiar with Generics we use TEntity to refer to any class we pass in during the instantiation of our repository – therefore anything that refers to TEntity refers to whatever object we want to use – e.g. we would have

public class MyObject{
    int Id { get; set; }
    string someProperty { get; set; }

public class MyRepository: Repository<MyObject>{

so when then instantiate the actual repository

var myrepo = new MyRepository();

We get all the commands from the base Repository class referencing our MyObject class model.

Anyway, if we ignore our repository for now, if we were to just query Database.SQLQuery directly we’d use

Database.SQLQuery<MyObject>("SELECT * FROM MyObjects");

this will result in a list of MyObject – EF actually attempts to convert the results it receives back to the model you pass in.
So all we have done now is automate that because we already know the model from when we instantiated our repository – thus when calling our encapsulated method we just use

SQLQuery("SELECT ALL FROM MyObjects");

Again we can pass in parameters just like with SQLExecuteCommand.

So as you can see this gives you complete flexibility in using EF – as mentioned earlier – if I have to iterate through and update large datasets I sometimes construct my SQL Directly and use these methods instead.

Note : This post was created in response to a user question on my course about ASP.NET Development Techniques.  My Blog viewers can get the course for just $10 by clicking this link 

The Frustration of Tutorials and Walkthroughs

I have an issue with how most software developers learn to write software.

Like me, a lot of developers learn on their own by searching for who to do stuff in the internet. This is great and of course I doubt there is much you CAN’T find with this method.

However, when I start learning new technologies – for example when I first started looking at MVC quite a few years back (I started on MVC2) – I found that all the tutorials, including Microsoft’s, taught in such a way that was really quite bad programming practice.

Now I’m not saying it’s ALL bad, but recently I’ve been looking at various training courses online, and the majority all show the same thing – they teach you how to CODE, but they don’t really teach you how to write software.

Example. One MVC course which promises to teach you how to be a ‘complete’ developer, went through the basics – create a project, create a class, here’s what a for loop is, here’s what a while loop does. etc etc.
However there was NO mention of SOLID principals, no real explanation of Object Orientated programming, of using interfaces and abstractions. Dependency Inversion and a whole multitude of good programming practices simply ignored.

As a junior developer I found this VERY frustrating.

Of course, now I’ve been around a bit, I do understand a lot more – but it’s actually been quite a painful journey if truth be told. It does however explain why a lot of software houses don’t really like to employee freelancers who have only ever worked on their own.

So anyway, to address this woeful shortcoming, I have actually created my own course.

I created it on a site called Udemy.com – if you’ve never used it have a look – it’s pay per course rather than subscription based, but they do often run very good deals – and some of the courses are really very good.

Please, checkout my course here

There’s a substantial discount on what will normally be charged, and I’ve had over 1000 students within a few days! So I guess there are some people out there who actually want to learn how to write software properly!

The great thing about Udemy.com is that you get life time access to the courses – and that includes UPDATES. For example in my course I’ll be adding lectures on Module Injection and splitting your views up using Partial Views – another underused technique.

And I would welcome input please – what do you think most programmers miss my learning from random searching?