Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
|
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 12th, 2008, 03:51 AM
Registered User
 
Join Date: Dec 2008
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Question LINQ to SQL / IEnumerable String Criteria

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
 
Old December 14th, 2008, 06:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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
The Following 2 Users Say Thank You to Bob Bedell For This Useful Post:
jminatel (December 15th, 2008), Yisman (December 15th, 2008)
 
Old December 15th, 2008, 04:54 AM
Registered User
 
Join Date: Dec 2008
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Smile Thanks!!

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!





Similar Threads
Thread Thread Starter Forum Replies Last Post
LINQ to SQL example for TheBeerHouse in Wrox Blox jminatel BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 51 January 17th, 2009 12:22 AM
Binding IEnumerable result set of a LINQ query prakashbpl .NET Framework 3.5 0 November 11th, 2008 04:41 AM
LINQ to SQL in TheBeerHouse zhoux BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 October 1st, 2008 07:37 AM
LINQ to SQL example for TBH - Wrox Blox Errata dparsons BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 April 26th, 2008 09:25 AM
using Sql server 2000 with LINQ shashanksinha SQL Server 2000 2 August 20th, 2007 10:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.