LINQ and dynamic where clauses

[Mike Collins]

I am trying to set up a dynamic search using linq. I believe the syntax is
correct, but cannot confirm it because when I try to cast my
Session[“Employeesâ€] from a List<> to IQueryable<>, I get a cast error
“Unable to cast object of type System.Collections.Generic.List to type
System.Linq.IQueryableâ€. Is there a way to cast List<> to IQueryable<>, or is
there a different way I need to be doing this?

protected void btnSearch_Click(object sender, EventArgs e)
{
try
{
if (Session["Employees"] != null)
{
IQueryable<BusinessLogic.Services.UserProfile> employees =
(IQueryable<BusinessLogic.Services.UserProfile>)(List<BusinessLogic.Services.UserProfile>)Session["Employees"];

switch (ddSearchCriteria.SelectedValue)
{
case "UserName":
employees.Where(c => c.Username ==
txtSearchCriteria.Text);
break;

case "Email":
employees.Where(c => c.EmailAddress ==
txtSearchCriteria.Text);
break;

default: //LastName
employees.Where(c => c.Person.LastName ==
txtSearchCriteria.Text);
break;
}

BindGrid(employees.Select(c =>
c).ToList<BusinessLogic.Services.UserProfile>());
}
else
{
List<BusinessLogic.Services.UserProfile> employees = null;
Shared.BusinessLogic.UserSearchFilter filter = new
UserSearchFilter();

switch (ddSearchCriteria.SelectedValue)
{
case "UserName":
filter.Username = txtSearchCriteria.Text;
break;

case "Email":
filter.EmailAddress = txtSearchCriteria.Text;
break;

default: //LastName
filter.LastName = txtSearchCriteria.Text;
break;
}

PagedResult<BusinessLogic.Services.UserProfile> pagedResult =
SearchEmployees(filter);
employees =
pagedResult.Results.ToList<BusinessLogic.Services.UserProfile>();
BindGrid(employees.ToList<BusinessLogic.Services.UserProfile>());
}
}
catch (ThreadAbortException) { }
catch (Exception ex) { ExceptionHelper.Publish(ex); }
}

 

[mesut]

Hi Mike, there are several ways of doing this. But I got this way
working...

I hope it helps,

cheers, mesut


objDataContext = DatabaseFactory.GetLinq();
List<Linq.tblCost> LocalTable;
var query = (from costing in objDataContext.tblCosts
select costing);

//the where code below (2 lines) are
replace by dynamic where
//where costing.ProductID == productID &&
//costing.BusinessUnit == businessUnit

if (productID != 0) query = query.Where(cost =>
cost.ProductID == productID);
if (!string.IsNullOrEmpty(businessUnit)) query =
query.Where(cost => cost.BusinessUnit == businessUnit);

LocalTable = query.ToList();

return LocalTable;

 

[Mike Collins]

Thanks for the reply, but I am not able to get it to work yet. Can you, or
anyone, offer some input.

Below is what I tried...also I decided that I needed to use SQLMethods.Like

List<.BusinessLogic.Services.UserProfile> employees =
(List<.BusinessLogic.Services.UserProfile>)Session["Employees"];
var query = from p in employees
select p;

switch (ddSearchCriteria.SelectedValue)
{
case "UserName":
query.Where(c => SqlMethods.Like(c.Username, "%" + txtSearchCriteria.Text
+ "%"));
break;

case "Email":
query.Where(c => SqlMethods.Like(c.EmailAddress, "%" +
txtSearchCriteria.Text + "%"));
break;

default: //LastName
query.Where(c => SqlMethods.Like(c.Person.LastName, "%" +
txtSearchCriteria.Text + "%"));
break;
}

employees = query.ToList();

BindGrid(employees);

 

[bruce barker]

the where clause in a Linq query does not set a property, its a method
(actually an extension method) that returns another IQueryable object that
the Select method (which also return an IQueryable) can be called on. your
code is throwing away the results of the Where.




-- bruce (sqlwork.com)

 

[Mike Collins]

So, it seems that I do not need to cast my Session object to an IQueryable
object then...since that line does not work anyway. I can just cast it to a
list<>, then put the results of the where into an IQueryable object at that
time. Is that correct?

the where clause in a Linq query does not set a property, its a method
(actually an extension method) that returns another IQueryable object that
the Select method (which also return an IQueryable) can be called on. your
code is throwing away the results of the Where.




-- bruce (sqlwork.com)

I am trying to set up a dynamic search using linq. I believe the syntax is
correct, but cannot confirm it because when I try to cast my
Session[“Employeesâ€] from a List<> to IQueryable<>, I get a cast error
“Unable to cast object of type System.Collections.Generic.List to type
System.Linq.IQueryableâ€. Is there a way to cast List<> to IQueryable<>, or is
there a different way I need to be doing this?

protected void btnSearch_Click(object sender, EventArgs e)
{
try
{
if (Session["Employees"] != null)
{
IQueryable<BusinessLogic.Services.UserProfile> employees =
(IQueryable<BusinessLogic.Services.UserProfile>)(List<BusinessLogic.Services.UserProfile>)Session["Employees"];

switch (ddSearchCriteria.SelectedValue)
{
case "UserName":
employees.Where(c => c.Username ==
txtSearchCriteria.Text);
break;

case "Email":
employees.Where(c => c.EmailAddress ==
txtSearchCriteria.Text);
break;

default: //LastName
employees.Where(c => c.Person.LastName ==
txtSearchCriteria.Text);
break;
}

BindGrid(employees.Select(c =>
c).ToList<BusinessLogic.Services.UserProfile>());
}
else
{
List<BusinessLogic.Services.UserProfile> employees = null;
Shared.BusinessLogic.UserSearchFilter filter = new
UserSearchFilter();

switch (ddSearchCriteria.SelectedValue)
{
case "UserName":
filter.Username = txtSearchCriteria.Text;
break;

case "Email":
filter.EmailAddress = txtSearchCriteria.Text;
break;

default: //LastName
filter.LastName = txtSearchCriteria.Text;
break;
}

PagedResult<BusinessLogic.Services.UserProfile> pagedResult =
SearchEmployees(filter);
employees =
pagedResult.Results.ToList<BusinessLogic.Services.UserProfile>();
BindGrid(employees.ToList<BusinessLogic.Services.UserProfile>());
}
}
catch (ThreadAbortException) { }
catch (Exception ex) { ExceptionHelper.Publish(ex); }
}