CODEWITHSUNDEEP
c#.netlinqentity-frameworkexpression
maniek099
maniek099

Reputation: 339

Linq to SQL Internal .NET Framework Data Provider error 1025

Can someone tell me why I can't select new objects from MS SQL database in Entity Framework in this way:

public static Expression<Func<LeaveDay, bool>> IsInDatesRange(DateTime startDate, DateTime endDate){
   return ld => ld.StartDate <= endDate && ld.EndDate >= startDate;
}


this.ObjectContext.People.Select(p => new NewPeopleObject
    {
      Guid = p.Guid,
      FirstName = p.FirstName,
      LastName = p.LastName,
      LeaveDays = p.CalendarData.LeaveDays.AsQueryable()
      .Where(LeaveDayExpressions.IsInDatesRange(startDate, endDate))
      .Select(ld => new LeaveDaySummary
         {
             StartDate = ld.StartDate,
             EndDate = ld.EndDate,
         })
    })

Without AsQueryable() I can't compile application, because LeaveDayExpressions.IsInDatesRange is static Expression. I have tried pass only Func to Where clause but it throws Internal .NET Framework Data Provider error 1025. With Expression and AsQueryable on LeaveDays I get this exception:

Code supposed to be unreachable

People is ObjectSet collection with one CalendarData object on one People and CalendarData has EntityCollection set of LeaveDays.

NewPeopleObject is a class with few properties and IEnumarable LeaveDaySummaries collection.

What can I do to pass Expression to Where clause without parsing linq to sql error?

Upvotes: 5

Views: 1565

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205769

I can't test the exact case (ObjectContext, ObjectSet etc. indicate some older EF version), but I was able to reproduce both aforementioned runtime exceptions in the latest EF6.1.3 (using DbContext and DbSet) as well.

Let forget about Func approach - EF needs to translate the query to SQL, so Expression is a must. Which in turn requires AsQueryable(). So far so good.

The problem is that EF does not like custom methods inside expression tree - it usually has no issues with top level methods, but definitely has issue with nested calls like in the sample (the problematic expression is part of the outer select expression).

I can't tell why, but in most of the cases (including this and when possible) putting the expression into local variable outside of the query and using it inside resolves the issue:

var leaveDayPredicate = LeaveDayExpressions.IsInDatesRange(startDate, endDate);
var result = this.ObjectContext.People.Select(p => new NewPeopleObject
{
    Guid = p.Guid,
    FirstName = p.FirstName,
    LastName = p.LastName,
    LeaveDays = p.CalendarData.LeaveDays.AsQueryable()
        .Where(leaveDayPredicate)
        .Select(ld => new LeaveDaySummary
        {
             StartDate = ld.StartDate,
             EndDate = ld.EndDate,
        })
});

For more advanced scenarios (like expression using something from the outer expression), you might need some expression processing library, for instance LINQKit Invoke / Expand / AsExpandable custom extension methods etc.

Upvotes: 6

Related Questions