Linq Select Into using T-SQL Function

Question

I have the following T-SQL working :

SELECT * INTO #visibleTasks FROM [#allTasks]  
WHERE [dbo].[func_GetTaskVisibility](taskId, state) = 1

I pass in temp table with 100's of TASKS and run a Function func_GetTaskVisibility against all the TASKS.

The function then checks against several requirements and for EACH #allTask it returns a 1 for good and 0 for bad.

I collected all the return = 1 and place into a new temp table called #visibleTasks.

How can I do the same call within LINQ?

NON WORKING CODE

 var visible = ( from f in allTasks 
    select new { 
    // to lookup foreach and return clean 
    visibleTasks = func_GetTaskVisibility(taskId,state) = 1
    }
    ).ToList();

Answer 1

Well, SELECT INTO is used to insert data, which Linq is not designed for, but the query part would be:

 var visible = ( from f in allTasks 
    // to lookup foreach and return clean 
    where func_GetTaskVisibility(taskId,state) = 1
    select f
    ).ToList();

Answer 2

var visible = ( from f in allTasks 
    // to lookup foreach and return clean 
    where MyDbContext.func_GetTaskVisibility(f.taskId,f.state) == 1
    select f
    ).ToList();

If you're using the Linq2SQL designer, you can put your function into the dbml just like you would your tables or a stored procedure, and then call it in linq and it'll generate the tsql correctly

Answer 3

You need to go through 2 steps for that because of the problem that sql functions can not be used in LINQ to SQL queries somewhere.

because of this you have to go through 2 steps like::

 var Tasks = ( from f in allTasks 
                    select new { 
                    ID=TaskID,
                    State=TaskState,
                    VisibleTask=0;
                    }
                    ).ToList();

for(int i=0;i<tasks.length;i++)
{
    Tasks[i].VisibleTask=  dataContext.func_GetTaskVisibility(Tasks[i].ID,Tasks[i].State)
}