CODEWITHSUNDEEP
linqlinq-to-sql
Muhammad Adeel Zahid
Muhammad Adeel Zahid

Reputation: 17784

Using Custom Function in Linq Query

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?

Upvotes: 2

Views: 844

Answers (1)

Branislav Abadjimarinov
Branislav Abadjimarinov

Reputation: 5131

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.

Upvotes: 5

Related Questions