Entity Framework

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()
        {
            context.SaveChanges();
        }
        public virtual void Dispose()
        {
            context.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 

Entity Framework and Interface issues

There is, and has been for quite some time a serious issue with Entity Framework – namely that it doesn’t support complex interfaces.

A simple entity based on an interface such as this is fine

public class Root : IRoot
{
public int RootId { get; set; }
public string Data { get; set; }
}

However as soon as your start to introduce child objects, for example an
ICollection Leafs
well now we have an issue, because the interface needs to define the collection as
ICollection Leafs
and that’s where it all goes a bit pear-shaped because EF can’t figure out that Leafs is a navigation property because it’s based on an interface instead of a concrete type.

I have spend many a wasted few hours searching for a fix to this, the majority of the articles I find simply state that EF does not support Interfaces.

In the past I’ve worked around it by using a DTO, so basically my DTO class is what EF will use to create my database mappings, but I then need to use something like AutoMapper, or roll my own mapper in order to convert my DTO to a concrete class based on an interface and vice versa.

This also has the issue that although retrieving and adding NEW entities works OK, as soon as you try to do an update to an existing entity with Attach it all starts to fall apart again.
Once again I managed to fixed this by simply ensuring that when I’m mapping from my concrete to the DTO I get the EXISTING DTO from the database, and then loop through all it’s properties copying and updating as required.

So recently, in a fit of despair, I sat down and hammered out a way to find an alternative solution that was a bit more elegant.

OK, so first we have our standard interface thus:

public interface IRoot
{
string Data { get; set; }
System.Collections.Generic.ICollection Leafs { get; }
int RootId { get; set; }
}

So we have an interface properly defined, but EF doesn’t know how to get the navigation property. So we need to create one along side our Collection that the interface expects, so lets go with

public ICollection Leafs { get; set;}
public ICollection LeafNavigation {get; set;}

OK that’s great but now of course these are two separate lists, so we’ll add an internal list and connect our two ICollections up to it. We’ll also ass some constructors for good measure.

public class Root : IRoot
{
private List _leafs;

public Root(ICollection leafs) {
this._leafs = leafs as List;
}

public Root(List leafs)
{
this._leafs = leafs;
}

public Root() {
this._leafs = new List();
}

public int RootId { get; set; }
public string Data { get; set; }

public ICollection Leafs { get { return _leafs.ConvertAll(l => (ILeaf)l); } }

public ICollection LeafNavigation { get { return _leafs; } set { _leafs = value.ToList(); } }
}

So now EF is happy, we’ve implemented the interface so we can use IRoot rather than Root. But now we have another issue – basically I can’t seem to ADD Leaf Entities to IRoot.Leafs – maybe someone can point out what I did wrong – because although the code lets me add the leaf with

IRoot root = new Root();
root.Add(new Leaf(){ Data="Some data"});

it just does’t actually ADD it to the underlying collection (I’ve tried a number of permutations – like I said if I have done something obvious PLEASE let me know!)

Anyway, I managed a simple work around – basically lets just add and Add method to the actual class that takes a Leaf and adds it to the underly connection thus the finished class and interface look like this.

public interface IRoot
{
void AddLeaf(ILeaf leaf);

string Data { get; set; }
System.Collections.Generic.ICollection Leafs { get; }
int RootId { get; set; }
}

public class Root : IRoot
{
private List _leafs;

public Root(ICollection leafs) {
this._leafs = leafs as List;
}

public Root(List leafs)
{
this._leafs = leafs;
}

public Root() {
this._leafs = new List();
}

public void AddLeaf(ILeaf leaf) {
_leafs.Add(leaf as Leaf);
}

public int RootId { get; set; }
public string Data { get; set; }

public ICollection Leafs { get { return _leafs.ConvertAll(l => (ILeaf)l); } }

public ICollection LeafNavigation { get { return _leafs; } set { _leafs = value.ToList(); } }
}

And Voila, this works a treat, I can add Leafs, and update them, along with the root and EF just binds everything as normal. Add, Update and Get all work seamlessly.

I’ve uploaded this to GitHub – and I hope this helps anyone who may be having similar issues!
https://github.com/squareconnection/EFInterfaces