Contains in Linq to Entitys

Linq to entities(LtoE) does not support “contains” as in the following Linq to Sql query:

var subquery = from product in MySandbox.Products
where product.Supplier.City.StartsWith("M")
select product.CategoryID;

var query = from category in MySandbox.Categories
where
category.CategoryName.Contains("y")
&& subquery.Contains(category.CategoryID)
select category;

the code will actually compile but throws a runtime error on the Contains. However the code can be rewritten to use Any:
var query = from category in MySandbox.Categories
where
category.CategoryName.Contains("y")
&& subquery.Any(i => i == category.CategoryID)
select category;

There is a larger issue with “contains” and code that looks like:
List ints = new List { 5, 6 };
var query2 = MySandbox.Categories.Where(e => ints.Contains(e.CategoryID));
This compiles but a runtime exception is thrown:
LINQ to Entities does not recognize the method 'Boolean Contains(Int32)' method, and this method cannot be translated into a store expression.

However there is an interesting solution at http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/

public static class QueryableExtensions
{
private static Expression<Func<TElement, bool>> GetWhereInExpression<TElement, TValue>(Expression<Func<TElement, TValue<< propertySelector, IEnumerable<TValue> values)
{
ParameterExpression p = propertySelector.Parameters.Single();
if (!values.Any())
return e => false;

var equals = values.Select(value => (Expression)Expression.Equal(propertySelector.Body, Expression.Constant(value, typeof(TValue))));
var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));

return Expression.Lambda<Func<TElement, bool>>(body, p);
}

///
/// Return the element that the specified property's value is contained in the specifiec values
public static IQueryable<TElemen> WhereIn<TElement, TValue>(this IQueryable<TElement> source, Expression<Func<TElement, TValu>> propertySelector, params TValue[] values)
{
return source.Where(GetWhereInExpression(propertySelector, values));
}

///
/// Return the element that the specified property's value is contained in the specifiec values
public static IQueryable<TElement> WhereIn<TElement, TValue>(this IQueryable<TElement> source, Expression<Func<TElement, TValue>> propertySelector, IEnumerable<TValue> values)
{
return source.Where(GetWhereInExpression(propertySelector, values));
}
}

Linq to Entitys vs Linq to SQL Lazy loading

Recently was asked to look at Linq to Entitys(LtoE) vs Linq to SQL. Linq to Entitys (LtoE) is an entirely different beast from Linq to SQL although they share Linq for language base query. LtoE sits on top of the ADO.NET Entity Framework (see http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx)

When you use Linq to SQL to create an object model from a database schema, you are consuming the database schema and immediately creating classes that represent that schema. This is partially offset in that FK relationships can allow the automatic hydrated of related tables into “contained” objects in the parent. However the Entity Framework provides a layer of abstraction above the database : you define a conceptual object model, you define the database schema in XML, and then you create a logical mapping between your conceptual object model and the database schema. Using this model, you are extremely well insulated from database changes. Most database changes can be absorbed by the schema and mapping without requiring a change to the object model - making it so you don't need to refactor and rebuild a bunch of objects that you're using throughout your application. This has the potential to be huge as we raise our thinking and design to the conceptual level and think in terms of business entities and not tables.

Here are few areas where there where differences:
Deferred or lazy Loading
LINQ to SQL automatically supports this The team behind EF didn't want this *automatic* behavior happening. The reason behind this decision is simple: When architecting a larger project, it is highly important for developers to clearly understand when they are accessing certain resources, such as the database. Consider the following where there is 1:N relationship between Serviceaddr and mailaddr thru a FK relationship:

serviceaddr m = dc.serviceaddr.Where(s => s.ServiceAddrKey == 288031).FirstOrDefault();
int num = m.mailingaddr.Count(); //num is equal to number of related mailaddr

however in EF the result is 0 and one has to:

serviceaddr m = dc.serviceaddr.Include("mailingaddr").Where(s => s.ServiceAddrKey == 288031).FirstOrDefault();
int num = m.mailingaddr.Count();
i must say that it kind of sucks that there is no type safe way to specify the Include as I could easily misspell mailingaddr.


for a good discussion see http://www.singingeels.com/Articles/Entity_Framework_and_Lazy_Loading.aspx

of course this can be an advantage as it pointed out in loading part of the object graph as we might not want all the FK relationship realized in the hydration of the parent object.

The answer to the readerQuotas exception thrown in previous example

The previous post showed how to use Web Developer Helper to identify why an exception was being thrown. The answer was nasty as it involves configuring readerQuotas which seems to be size of the buffer used in XML serialization. there seems to be a max of 8192 on this which seems pretty silly but anyways I added the following binding definition to my web.config

<basichttpbinding>
<binding name="basicHttpBinding" receivetimeout="00:10:00" sendtimeout="00:10:00" bypassproxyonlocal="false" hostnamecomparisonmode="StrongWildcard" maxbuffersize="2147483647" maxbufferpoolsize="524288" maxreceivedmessagesize="2147483647" messageencoding="Text" textencoding="utf-8" transfermode="Buffered" usedefaultwebproxy="true" closetimeout="00:10:00" opentimeout="00:10:00">
<readerQuotas maxDepth="32"
maxStringContentLength="2147483647"
maxArrayLength="2147483647"
maxBytesPerRead="4096"
maxNameTableCharCount="4096" />
</binding>
</basichttpbinding>>

then i use it in the following:

<endpoint contract="SchedulerV4.Web.ISchedulerServices" binding="basicHttpBinding" address="" bindingconfiguration="basicHttpBinding">