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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s