Category Archives: EntityFramework

Unit Testing – Effort

Effort.EF – it is library to mock you EntityFramework database context and allow you test code without access to this ‘real’ database like MSSQL or Oracle.
Effort is installed together with EntityFramework and NMemory (in memory database).

Imagine you have a simple class with the only one method in it. This method counts people stored in database (Entity Framework is used).

    public class PeopleManager
    {
        public static int CountPeople(TSQL2012Entities entities)
        {
            return entities.ThePeople.Count();
        }
    }

You want to unit test this method, but without changing your real database. You can use Effort then.
Just create UnitTests project, install Effort.EF (using i.e. Nuget) add reference to your database project and project to be tested.
Now you can write like below.

        [TestMethod]
        public void PeopleManagerCountPeopleTransientTest()
        {
            //transient database context is created
            using (var context = new TSQL2012Entities(Effort.EntityConnectionFactory.CreateTransient("name=TSQL2012Entities")))
            {
                //entities are added
                context.ThePeople.Add(new ThePeople() { id = 0, firstname = "John", lastname = "Smith" });
                context.ThePeople.Add(new ThePeople() { id = 1, firstname = "Jane", lastname = "Brown" });
                
                //changes are stored into in memory database
                context.SaveChanges();

                //method is tested
                var peopleManagerCount = PeopleManager.CountPeople(context);
                Assert.AreEqual<int>(2, peopleManagerCount); //passed
            }
        }

That’s easy! There is only one thing to remember. When you create context using CreateTransient method, your in memory database is cleared always when you go outside the using clause. To avoid such behaviour you can use CreatePersisten method. See examples below.

        [TestMethod]
        public void PeopleManagerCountPeopleTransient2Test()
        {
            //transient database context is created, entites are added and stored
            using (var context = new TSQL2012Entities(Effort.EntityConnectionFactory.CreateTransient("name=TSQL2012Entities")))
            {
                context.ThePeople.Add(new ThePeople() { id = 0, firstname = "John", lastname = "Smith" });
                context.ThePeople.Add(new ThePeople() { id = 1, firstname = "Jane", lastname = "Brown" });
                context.SaveChanges();
            }

            //database context is cleared

            //method is tested, but database context is empty, so it fails
            using (var context = new TSQL2012Entities(Effort.EntityConnectionFactory.CreateTransient("name=TSQL2012Entities")))
            {
                var peopleManagerCount = PeopleManager.CountPeople(context);
                Assert.AreEqual<int>(2, peopleManagerCount);
            }
        }


        [TestMethod]
        public void PeopleManagerCountPeopleOnePersistentTest()
        {
            //persistent database context is created, entites are added and stored
            using (var context = new TSQL2012Entities(Effort.EntityConnectionFactory.CreatePersistent("connectionA", "name=TSQL2012Entities")))
            {
                context.ThePeople.Add(new ThePeople() { id = 0, firstname = "John", lastname = "Smith" });
                context.ThePeople.Add(new ThePeople() { id = 1, firstname = "Jane", lastname = "Brown" });
                context.SaveChanges();
            }

            //data is not cleared

            using (var context = new TSQL2012Entities(Effort.EntityConnectionFactory.CreatePersistent("connectionA", "name=TSQL2012Entities")))
            {
                //method is tested and test passes
                var peopleManagerCount = PeopleManager.CountPeople(context);
                Assert.AreEqual<int>(2, peopleManagerCount);
            }
        }

More information: https://effort.codeplex.com/

Entity Framework – Find() vs. FirstOrDefault()

Imagine you need to find an entity using your database context filtering by primary key.
There are two main options:
a) .FirstOrDefault()
b) .Find()

The latter one is the better one.
FirstOrDefault() method always executes a query on the database.
Find() method is more complicated. At first it checks if required entity is already loaded into in memory database context. If it is – the result is returned without hitting the database. If not query on the database is executed.

See example below. At first we select five rows from the database. Then we use FirstOrDefault – ‘query1’ is sent to the database, despite the fact that the entity we are looking for already exists in context. Then we try to get the same entity using Find method. ‘Query2’ is not sent to the database, because this entity already exists in our context. The last ‘query3’ uses Find method, but required entity is not existing in our context yet, therefore query is sent to the database.

private static void FindVsFirstOrDefault()
{
	using (var context = new TSQL2012Entities())
	{
		context.Database.Log = (string msg) => { Console.WriteLine(msg); };
		var people = context.Employees.Where(p => p.empid <= 5).ToList();

		//asks database for a person using PK
		Console.WriteLine("FirstOrDefault");
		var query1 = context.Employees.FirstOrDefault(p => p.empid == 1);

		//asks first the database context (in memory). Entity is already loaded, so it returns it without querying the database.
		Console.WriteLine("Find 1");
		var query2 = context.Employees.Find(1);

		//asks first the database context (in memory). Entity is not loaded yet, so it asks the database.
		Console.WriteLine("Find 6");
		var query3 = context.Employees.Find(6);

	}
}

Notice that Find() method can operate only when primary key is passed as a parameter.

Entity Framework – IQueryable vs. IEnumerable

Let’s imagine we want to get some data from the database. We use Entity Framework.
Our table in the database has 1000000 rows and was mapped to the class below:

    public partial class MyPeople
    {
        public string firstname { get; set; }
        public string lastname { get; set; }
        public decimal id { get; set; }
    }

We added two extension methods to get only people with odd id. The bodies of these methods are the same, but one little detail:
a) WhereOdd1 – uses IEnumerable,
b) WhereOdd2 – uses IQueryable.

    public static class DbExtensions
    {
        public static IEnumerable<MyPeople> WhereOdd1(this IEnumerable<MyPeople> source)
        {
            return source.Where(x => x.id % 2 != 0);
        }

		public static IQueryable<MyPeople> WhereOdd2(this IQueryable<MyPeople> source)
		{
			return source.Where(x => x.id % 2 != 0);
		}
    }

Now we have two following methods to read data from database. Results for both methods should be the same.

        private static void LinqToObjectsVsLinqToSql()
        {
            using (var ctx = new TSQL2012Entities())
            {
                ctx.Database.Log = (string msg) => { Console.WriteLine(msg); };
                var sw = Stopwatch.StartNew();
                var people = ctx.MyPeople.SampleIEnumerableQuery().ToList();
                Console.WriteLine("LinqToObjects: " + sw.ElapsedMilliseconds);
                //it took 12 seconds and 550MB
                //query executed on the database is more or less like: SELECT * FROM MYPEOPLE;
            }

            using (var ctx = new TSQL2012Entities())
            {
                ctx.Database.Log = (string msg) => { Console.WriteLine(msg); };
                var sw = Stopwatch.StartNew();
                var people = ctx.MyPeople.SampleIQueryableQuery().ToList();
                Console.WriteLine("LinqToSql: " + sw.ElapsedMilliseconds);
                //it took 6 seconds and 280MB
                //query executed on the database is more or less like: SELECT * FROM MYPEOPLE WHERE ...;
            }
        }

When we run two following methods to get a few people we can see that the first one is much slower and RAM consuming that the latter one.
It’s due to the fact that when you use IQueryable you really use LINQ-to-SQL and query is executed on the database engine.
When you use IEnumerable you use LINQ-to-Objects – all rows are loaded into memory and then filtered out.