LINQ to SQL / IEnumerable String Criteria

Yisman · #1 (**permalink**) December 12th, 2008, 03:51 AM

Hello everybody.
can you please help me out with this? i need to filter an IEnumerable resultset returned from a linq2sql statement. the filter has to be a string which includes the relevant criteria.

here is my current code:

Code:


Dim ps As String = UserPermission("Task")
Dim dcx AsNew MainBaseDataContext
Tasks = From t In dcx.Tasks.Where(ps)

obviously this doesnt work, the compiler complains that no overloaded version of 'Where' accepts this number of arguments.

the need/goal:
UserPermission is a function that looks up different permissions set in the db for users of different departments in the company and returns a criteria string. the string may look something like "(assignedto=99 or doneby=99) and deptID in(2,4,7)" or any other sql filter.
this must be the first filter assigned to any LINQ select method.
after that the UI needs to be able to filter further according to user input so i have the following code:

Code:


If ddlResolvedF.SelectedValue <> ""Then Tasks = From t In Tasks Where (t.DoneBy IsNothing) = ddlResolvedF.SelectedValue
If ddlActiveF.SelectedValue <> ""Then Tasks = From t In Tasks Where t.Active = ddlActiveF.SelectedValue

so the question boils down to this: How can I apply a criteria string to an IEnumerable returned from linq2sql?

thank you all for your time and waiting for your responses.
all the best
Yisman

Bob Bedell · #2 (**permalink**) December 14th, 2008, 06:13 PM

For the following code:

Code:

int[] deptIdsFilter = newint[] { 2, 4, 7 };
var tasks1 = from t in dcx.Tasks
where (t.assignedto == 99 || t.doneby == 99) && deptIdsFilter.Contains(t.deptid)
select t;

LinqToSql generates the folowing SQL (after I cleaned it up a lot and reformatted the parameters):

Code:

 
"SELECT assignedto, doneby, deptid " +
"FROM Tasks " +
"WHERE (assignedto = {0} OR doneby = {1}) AND deptid IN({2}, {3}, {4})";

You could just build the full SQL string, replacing the parameters with the hard-coded values you want and supplying others dynamically by othr means, then execute it with:

Code:

IEnumerable<Task> tasks = dcx.ExecuteQuery<Task>(sql, 99, 99, 2, 4, 7);

Might be one way to go.

HTH,

Bob

Yisman · #3 (**permalink**) December 15th, 2008, 04:54 AM

Youre THE guy!
my was i seeing stars here. i posted in 2 other forums, but you gave a real simple useful answer. in case someone needs the VB version , heres whtat i put:

Code:


Dim ps As RuleSet = UserPermission("Task")
Dim dcx AsNew MainBaseDataContext
Dim Tasks = dcx.ExecuteQuery(Of Task)("select * from acts where " & ps)
If ddlResolvedF.SelectedValue <> ""Then Tasks = From t In Tasks Where (t.DoneBy IsNothing) = ddlResolvedF.SelectedValue

God bless you all!

Need to download code?

Navigation