Today, you have two direct options how to load related data for given entity. Use Include or use Load method. In this post, I’ll focus on Load, because this problem on Include is little bit easier than on Load. Unfortunately the Load method isn’t providing any option to filter or limit what you’re loading. That means, if you have customer with for example 200 invoices, you’ll load all these 200 invoice, even if you need only first 5 or something like that. And that’s wasting of resources.

But the solution isn’t so hard. First, Entity Framewok has automagic wiring of related entities. Hence if you load one customer and then some invoices. If there will be invoices for this customer the navigation properties will be working without problem and without any work form you. So the first solution for filtered load is simply load detail record with your where condition and condition on the master entity. But that’s “a lot” of work and developers are lazy. Fortunately there’s another way.

The EntityReference has a method CreateSourceQuery returning ObjectQuery. With this and knowledge from previous paragraph you can easily create overload/extension method for Load to load only what you need.

public static void Load<T>(this EntityCollection<T> relatedEnd, Expression<Func<T, bool>> predicate, MergeOption mergeOption) where T : class, IEntityWithRelationships
{
	(relatedEnd.CreateSourceQuery().Where(predicate) as ObjectQuery<T>).Execute(mergeOption).ToArray();
}

public static void Load<T>(this EntityReference<T> relatedEnd, Expression<Func<T, bool>> predicate, MergeOption mergeOption) where T : class, IEntityWithRelationships
{
	(relatedEnd.CreateSourceQuery().Where(predicate) as ObjectQuery<T>).Execute(mergeOption).ToArray();
}

public static void Load<T>(this EntityCollection<T> relatedEnd, Expression<Func<T, bool>> predicate) where T : class, IEntityWithRelationships
{
	Load(relatedEnd, predicate, MergeOption.AppendOnly);
}

public static void Load<T>(this EntityReference<T> relatedEnd, Expression<Func<T, bool>> predicate) where T : class, IEntityWithRelationships
{
	Load(relatedEnd, predicate, MergeOption.AppendOnly);
}

Above is simple extension method, to help you load only related entities you need based on some where condition. You can take the same idea and create a version with First, for instance. Or any filter or limit you may need.

Tags: Entity Framework, LINQ

Colin Meek wrote extremely handy method for using it as Contains method we know it’s on collections, but not supported in Entity Framework right now (v1/EF3.5). The method is called BuildContainsExpression. Today in MSDN forums I needed this, but negated. Thus I wrote the BuildNotContainsExpression. It’s just small change of original Colin’s method.

static Expression<Func<TElement, bool>> BuildNotContainsExpression<TElement, TValue>(Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)
{
    if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
    if (null == values) { throw new ArgumentNullException("values"); }

    ParameterExpression p = valueSelector.Parameters.Single();

    // p => valueSelector(p) != values[0] && valueSelector(p) != ...

    if (!values.Any())
    {
        return e => true;
    }

    var equals = values.Select(value => (Expression)Expression.NotEqual(valueSelector.Body, Expression.Constant(value, typeof(TValue))));
    var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.And(accumulate, equal));
    return Expression.Lambda<Func<TElement, bool>>(body, p);
}

Tags: Entity Framework, LINQ

Today my UI/ASP.NET guru workmate asked me whether it’s possible to provide sorting methods on queries (in our case Entity Framework) using column name as string instead of expression. Well, I thought, why it shouldn’t.

My first attention got to the MetadataWorkspace. After couple of attempts it became clear that this is not the best way. So I switched my attention to the expression trees. And that was the way. I ended up with this result.

private static IOrderedQueryable<T> OrderingHelper<T>(IQueryable<T> source, string propertyName, bool descending, bool anotherLevel)
{
    ParameterExpression param = Expression.Parameter(typeof(T), string.Empty); // I don't care about some naming
    MemberExpression property = Expression.PropertyOrField(param, propertyName);
    LambdaExpression sort = Expression.Lambda(property, param);

    MethodCallExpression call = Expression.Call(
        typeof(Queryable),
        (!anotherLevel ? "OrderBy" : "ThenBy") + (descending ? "Descending" : string.Empty),
        new[] { typeof(T), property.Type },
        source.Expression,
        Expression.Quote(sort));

    return (IOrderedQueryable<T>)source.Provider.CreateQuery<T>(call);
}

public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string propertyName)
{
    return OrderingHelper(source, propertyName, false, false);
}

public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> source, string propertyName)
{
    return OrderingHelper(source, propertyName, true, false);
}

public static IOrderedQueryable<T> ThenBy<T>(this IOrderedQueryable<T> source, string propertyName)
{
    return OrderingHelper(source, propertyName, false, true);
}

public static IOrderedQueryable<T> ThenByDescending<T>(this IOrderedQueryable<T> source, string propertyName)
{
    return OrderingHelper(source, propertyName, true, true);
}

It’s a set of extension methods with similar signature as the classic ones OrderBy, ThenBy, etc. only taking string parameter instead of expression. This string parameter is name of field in class to be used for sorting. Enjoy.

Tags: Entity Framework, LINQ