Be careful what you LINQ for: Mixing LINQ to SQL with LINQ to Entities

28 בMay 2014

no comments

An exception I encountered today, led me to pay more attention about the differences between LINQ to SQL and LINQ to Entities. Here is what I learned;

Imagine your in a school, and the students council has announced the winners in the school’s yearly programming competition. We have a bunch of students, and the winners names. Let’s get all the winning student, that’s easy:

 using (var db = new School())
{
    //Create students list
    var students = new List<Student>
    {
        new Student { ID = 0, Name = "John" },
        new Student { ID = 1, Name = "James" },
        new Student { ID = 2, Name = "Jim" }
    };
    db.Studens.AddRange(students);
    db.SaveChanges();

    //Create winners list
    var winners = new List<Winner>
    {
        new Winner{ Name = "Jim", PrizeWon = 100 },
        new Winner{ Name = "Sam", PrizeWon = 100 }
    };

    //Find the first student to be a winner
    db.Studens.AddRange(students);
    db.SaveChanges();

    var firstWinner = from student in db.Studens
                                 where student.Name == winners.First().Name
                                 select student;
}

The above code won’t get us what we wanted. See if you can say where will it fail.

The code will throw a run-time exception if we’ll try to access “firstWinner”, since we are dealing with two different types: “winners” is an IEnumrable, where as “students” is an IQueryable. The compiler sees the LINQ expression that includes object “db.Stundets” and decides to call it’s  IQueryable interface methods . “winners”, which doesn’t implement the IQeuryable interface, is being called with it’s  IEnumrable interface methods.

IL_01b5: call class [System.Core]System.Linq.Expressions.Expression`1<!!0> [System.Core]System.Linq.Expressions.Expression::Lambda<class [mscorlib]System.Func`2<class LinqTestConsole.Program/Student, bool>>(class [System.Core]System.Linq.Expressions.Expression, class [System.Core]System.Linq.Expressions.ParameterExpression[])
IL_01ba: call class [System.Core]System.Linq.IQueryable`1<!!0> [System.Core]System.Linq.Queryable::Where<class LinqTestConsole.Program/Student>(class [System.Core]System.Linq.IQueryable`1<!!0>, class [System.Core]System.Linq.Expressions.Expression`1<class [mscorlib]System.Func`2<!!0, bool>>)

Compiled IL code of our example, line 24 

This brings us to the differentiation between the IQueryable and IEnumrable, and the way LINQ implements them. For our example, we can look at both as the return type of a set of extension methods, which are implemented totally different.

The IQueryable extension method is implemented to be eventually be translated into an expression tree which later compiles into an SQL query (or any other LINQ provider).

public static IQueryable<TSource> Where<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
{
	if (source == null)
	{
		throw Error.ArgumentNull("source");
	}
	if (predicate == null)
	{
		throw Error.ArgumentNull("predicate");
	}
	return source.Provider.CreateQuery<TSource>(Expression.Call(null, ((MethodInfo)MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[]
	{
		typeof(TSource)
	}), new Expression[]
	{
		source.Expression,
		Expression.Quote(predicate)
	}));
}

IQuerable “where” implemeenation from System.LINQ

IEnumrable extension method is implemented using an enumerator which simply iterates over the items it receives.

public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate)
{</pre>
<pre>	if (source == null)
	{
		throw Error.ArgumentNull("source");
	}
	if (predicate == null)
	{
		throw Error.ArgumentNull("predicate");
	}
	if (source is Enumerable.Iterator<TSource>)
	{
		return ((Enumerable.Iterator<TSource>)source).Where(predicate);
	}
	if (source is TSource[])
	{
		return new Enumerable.WhereArrayIterator<TSource>((TSource[])source, predicate);
	}
	if (source is List<TSource>)
	{
		return new Enumerable.WhereListIterator<TSource>((List<TSource>)source, predicate);
	}
	return new Enumerable.WhereEnumerableIterator<TSource>(source, predicate);
}

IEnumrable “where” implemeenation from System.LINQ

Since we started the LINQ expression from a LINQ to SQL expression, it will be passed into the LINQ provider (SQL), which will fail to read the IEnumrable extension method and throw an exception.

So, how can we iterate a mix of LINQ to SQL and LINQ to entities?

Well basically we can’t, as we have seen above. But we can change our code to support the differed execution on the two types.

One way to do it, is to get the IQueryable interface to return as an IEnurable using the AsEnumerable() extension method (ToArray(), ToList, etc. would also work).

 var firstWinner = from student in db.Studens.AsEnumerable()
                   where student.Name == winners.First().Name
                   select student;

 

 

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*