Using Custom Function in Linq Query

Question

I'm using L2S for database operations in my asp.net mvc application I have following simple query in my repository

(from pt in db.oaProjectTasks
                   where pt.ProjectID == ProjectID
                   join t in db.oaTasks on pt.TaskID equals t.TaskID
                   where t.ParentTaskID == null
                   let daypassed = GetDaysPassed(t.StartDate,t.Duration)
                   select new ChartTask{TaskNumber = t.TaskNumber,StartDate = t.StartDate,
                   DurationRemaining = t.Duration - daypassed,TaskDescription = t.Task, DaysPassed = daypassed,Duration = t.Duration }).ToList();

below is the defination of GetDayPassed method

private int GetDaysPassed(DateTime StartDate, int Duration) 
        {
            int retVal;
            if ((DateTime.Now - StartDate).Days > 0)
            {
                if ((DateTime.Now - StartDate.AddDays(Duration)).Days > 0)
                {
                    retVal = Duration;
                }
                else
                {
                    retVal = (DateTime.Now - StartDate).Days;
                }
            }
            else 
            {
                retVal = 0;
            }
            return retVal;
        }

there is no compile time error, however, when i execute the code it gives me invalidOperationException with following message.

Could not translate expression 'Table(oaProjectTask).Where(pt => (pt.ProjectID == Invoke(value(System.Func`1[System.Int64])))).Join(Table(oaTask), pt => pt.TaskID, t => t.TaskID, (pt, t) => new <>f__AnonymousType5f`2(pt = pt, t = t)).Where(<>h__TransparentIdentifier2 => (<>h__TransparentIdentifier2.t.ParentTaskID == null)).Select(<>h__TransparentIdentifier2 => new

how can I get around this? if calling a method in query is not allowed how can I make a simple calculation in Linq query instead of calling GetDayPassed method?

Branislav Abadjimarinov · Accepted Answer · 2010-11-16 14:00:14Z

5

You can try this:

(from pt in db.oaProjectTasks
 where pt.ProjectID == ProjectID
 join t in db.oaTasks on pt.TaskID equals t.TaskID
 where t.ParentTaskID == null
 select t)
    .ToList() // T-SQL query will be executed here and result will be returned
    .Select(t => new ChartTask {
       TaskNumber = t.TaskNumber,
       StartDate = t.StartDate,
       DurationRemaining = t.Duration - GetDaysPassed(t.StartDate,t.Duration),
       TaskDescription = t.Task, 
       DaysPassed = GetDaysPassed(t.StartDate,t.Duration),
       Duration = t.Duration });

The problem is that Linq to Sql tries to translate your custom function to T-SQL and since it doesn't know how to do that it will throw the exception. In my case Linq will construct the query, execute it (after the call to .ToList()) and your function will be called as Linq to objects query.

edited Nov 16, 2010 at 14:00

answered Nov 12, 2010 at 9:25

Branislav Abadjimarinov

5,1513 gold badges37 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Custom Function in Linq Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related