I have a bunch of checkboxes that are dynamically generated from a query. They include "tags" or "keywords" that a user has chosen to associate with projects. These go into a separate table, and I display only the distinct values for the checkboxes. (Because
a tag might be used for more than one project.)
So, when the user clicks the checkboxes, I'd like to display all the projects that possess those tags/keywords.
I'm able to grab all the keywords in a string, using a foreach...
This will return a string with a space between each tag they check.
But I want to put that into a linq query. So, if they search for one tag (let's call it "poopy"), it would be
var qrygetprojects = (from p in db.projects
join tp in db.TagToProject on p.projectID equals tp.projectID
where tp.TagName == tagstring2
select new
{
tp.projectID,
p.project
}).ToList();
Works just fine when only one tag is checked.
where tp.tagname == "poopy"
But how do I create a linq query where it doesn't care how many are checked? It gets tricky, because I want to show those projects that have ALL of the checked tags. (Not "ANY" but "ALL")...
so my query is basically "SELECT projectID from TagToProject WHERE tagName in ('poopy','foo','nexttag')... but only display those where the count of those projects is at least equal to the NUMBER of tags being searched.
I suppose it depends on how your keywords or tags in your database are stored. I'll assume they are also a space delimited string for this example :
//Gather your keywords into an array
string[] keywords = tagstring2.Split(' ');
//Only get the projects where all of the keywords are contained in your item
var query = (from p in db.projects
join tp in db.TagToProject on p.projectID equals tp.projectID
where !keywords.Except(tp.tagName.Split(' ')).Any()
select new
{
tp.projectID,
p.project
}).ToList();
That will determine if all of the keywords are a subset of any of your items within your database.
So I tinkered with this a bit last night and the following should work for your current structure. You'll basically seperate it into two queries. The first will map each of your ProjectIDs to their cooresponding set of Keyword values, which will be a space-seperated
string :
//This performs your join and then maps and then joins your keywords into a space-seperated string
var results = (from p in db.projects
join tp in db.TagToProject on p.projectID equals tp.projectID
select new {
ProjectID = tp.ProjectID,
Keywords = string.Join(" ", projects.Where(project => project.projectID == tp.projectID).Select(k => k.Keyword).ToArray()) }).Distinct();
from that - you can use another query to perform your logic check and find the projectId that cooresponds to your original query :
//This will find the necessary matches
var query = from r in results
where !keywords.Except(r.Keywords.Split(' ')).Any()
select r.ProjectID;
Here is a very basic full example which includes example classes (to emulate your DB and DB Entities)
Classes
public class TagProject
{
public string ProjectID { get; set; }
public TagProject(string id)
{
ProjectID = id;
}
}
public class Project
{
public string ProjectID{ get; set;}
public string Keyword{ get; set;}
public Project(string id, string keyword)
{
ProjectID = id;
Keyword = keyword;
}
}
Code & Queries
//Gather your keywords into an array
string[] keywords = { "poopy","foo" };
//Your Tag Projects
List<TagProject> TagToProject = new List<TagProject>{ new TagProject("12345"), new TagProject("12488"), new TagProject("12722"), new TagProject("12399")};
//Your Projects and Keywords Table
List<Project> projects = new List<Project>{ new Project("12345", "poopy"), new Project("12488", "poopy"), new Project("12722", "foo"), new Project("12399", "poopy"), new Project("12399", "foo") };
//Your "Mapping" Query
var results = (from p in projects
join tp in TagToProject on p.ProjectID equals tp.ProjectID
select new {
ProjectID = tp.ProjectID,
Keywords = string.Join(" ", projects.Where(project => project.ProjectID == tp.ProjectID).Select(k => k.Keyword).ToArray()) }).Distinct();
//Your Results Query
var query = from r in results
where !keywords.Except(r.Keywords.Split(' ')).Any()
select r.ProjectID;
All of this would be within a submit method or a button click event (in the code-behind).
You should have access to the .Where() method, if not try adding .AsEnumerable() behind your projects variable :
dezinnia
Member
18 Points
144 Posts
dynamic Linq WHERE clause based on string from checkboxes
Jan 25, 2013 07:43 PM|LINK
I have a bunch of checkboxes that are dynamically generated from a query. They include "tags" or "keywords" that a user has chosen to associate with projects. These go into a separate table, and I display only the distinct values for the checkboxes. (Because a tag might be used for more than one project.)
So, when the user clicks the checkboxes, I'd like to display all the projects that possess those tags/keywords.
I'm able to grab all the keywords in a string, using a foreach...
if (TagCheckbox.Checked) { tagstring2 = tagstring2 + TagCheckbox.Text + " "; }This will return a string with a space between each tag they check.
But I want to put that into a linq query. So, if they search for one tag (let's call it "poopy"), it would be
var qrygetprojects = (from p in db.projects join tp in db.TagToProject on p.projectID equals tp.projectID where tp.TagName == tagstring2 select new { tp.projectID, p.project }).ToList();Works just fine when only one tag is checked.
But how do I create a linq query where it doesn't care how many are checked? It gets tricky, because I want to show those projects that have ALL of the checked tags. (Not "ANY" but "ALL")...
so my query is basically "SELECT projectID from TagToProject WHERE tagName in ('poopy','foo','nexttag')... but only display those where the count of those projects is at least equal to the NUMBER of tags being searched.
Does any of that make sense?
Rion William...
All-Star
27896 Points
4618 Posts
Re: dynamic Linq WHERE clause based on string from checkboxes
Jan 25, 2013 07:59 PM|LINK
I suppose it depends on how your keywords or tags in your database are stored. I'll assume they are also a space delimited string for this example :
//Gather your keywords into an array string[] keywords = tagstring2.Split(' '); //Only get the projects where all of the keywords are contained in your item var query = (from p in db.projects join tp in db.TagToProject on p.projectID equals tp.projectID where !keywords.Except(tp.tagName.Split(' ')).Any() select new { tp.projectID, p.project }).ToList();That will determine if all of the keywords are a subset of any of your items within your database.
dezinnia
Member
18 Points
144 Posts
Re: dynamic Linq WHERE clause based on string from checkboxes
Jan 25, 2013 10:01 PM|LINK
I love that idea (maybe it makes sense to redo the structure!), but the keywords are stored in separate records like so:
So, if you picked "poopy" three projects would show up.
If you picked "foo" two projects would show up.
If you checked "poopy" AND "foo" only one project would show up. (12399)
Rion William...
All-Star
27896 Points
4618 Posts
Re: dynamic Linq WHERE clause based on string from checkboxes
Jan 26, 2013 01:10 PM|LINK
So I tinkered with this a bit last night and the following should work for your current structure. You'll basically seperate it into two queries. The first will map each of your ProjectIDs to their cooresponding set of Keyword values, which will be a space-seperated string :
//This performs your join and then maps and then joins your keywords into a space-seperated string var results = (from p in db.projects join tp in db.TagToProject on p.projectID equals tp.projectID select new { ProjectID = tp.ProjectID, Keywords = string.Join(" ", projects.Where(project => project.projectID == tp.projectID).Select(k => k.Keyword).ToArray()) }).Distinct();from that - you can use another query to perform your logic check and find the projectId that cooresponds to your original query :
//This will find the necessary matches var query = from r in results where !keywords.Except(r.Keywords.Split(' ')).Any() select r.ProjectID;Here is a very basic full example which includes example classes (to emulate your DB and DB Entities)
Classes
public class TagProject { public string ProjectID { get; set; } public TagProject(string id) { ProjectID = id; } } public class Project { public string ProjectID{ get; set;} public string Keyword{ get; set;} public Project(string id, string keyword) { ProjectID = id; Keyword = keyword; } }Code & Queries
//Gather your keywords into an array string[] keywords = { "poopy","foo" }; //Your Tag Projects List<TagProject> TagToProject = new List<TagProject>{ new TagProject("12345"), new TagProject("12488"), new TagProject("12722"), new TagProject("12399")}; //Your Projects and Keywords Table List<Project> projects = new List<Project>{ new Project("12345", "poopy"), new Project("12488", "poopy"), new Project("12722", "foo"), new Project("12399", "poopy"), new Project("12399", "foo") }; //Your "Mapping" Query var results = (from p in projects join tp in TagToProject on p.ProjectID equals tp.ProjectID select new { ProjectID = tp.ProjectID, Keywords = string.Join(" ", projects.Where(project => project.ProjectID == tp.ProjectID).Select(k => k.Keyword).ToArray()) }).Distinct(); //Your Results Query var query = from r in results where !keywords.Except(r.Keywords.Split(' ')).Any() select r.ProjectID;dezinnia
Member
18 Points
144 Posts
Re: dynamic Linq WHERE clause based on string from checkboxes
Jan 28, 2013 06:51 PM|LINK
Wow. Thank you!
I'll dig into it right away to see how it might work!
Cheers!
Rion William...
All-Star
27896 Points
4618 Posts
Re: dynamic Linq WHERE clause based on string from checkboxes
Jan 28, 2013 06:55 PM|LINK
I tested it out on my local machine and it appeared to work as intended. Let me know if you need to tweak it any or have any additional questions.
dezinnia
Member
18 Points
144 Posts
Re: dynamic Linq WHERE clause based on string from checkboxes
Jan 28, 2013 07:49 PM|LINK
Ok, couple of questions for this noob...
on this line:
Keywords = string.Join(" ", projects.Where(project => project.projectID == tp.projectID).Select(k => k.Keyword).ToArray()) }).Distinct();Intellisense tells me that my table doesn't have a definition for "Where"...
And then this line:
where !Keyword.Except(r.Keywords.Split(' ')).Any()Intellisense tells me that that first Keyword isn't in the current context.
This is all wrapped in a method, right? Would it be an onclick event for the submit button?
Rion William...
All-Star
27896 Points
4618 Posts
Re: dynamic Linq WHERE clause based on string from checkboxes
Jan 28, 2013 07:53 PM|LINK
Right.
All of this would be within a submit method or a button click event (in the code-behind). You should have access to the .Where() method, if not try adding .AsEnumerable() behind your projects variable :
Keywords = string.Join(" ", projects.AsEnumerable().Where( ...I'm not sure about the Intellisense issues. Does it throw any errors when compiling?
dezinnia
Member
18 Points
144 Posts
Re: dynamic Linq WHERE clause based on string from checkboxes
Jan 29, 2013 07:12 PM|LINK
well, I figured out I needed to have the "db." in front of the table. Duh. So the "where" is fine.
But the "Keywords" part...
do I still use your array bit?
string[] keywords = tagstring2.Split(' ');And do I still want all of this in the click event?
foreach (DataListItem item in TagDataList.Items) { if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem) { CheckBox TagCheckbox = (CheckBox)item.FindControl("TagCheckbox"); HiddenField TagNameHidden = (HiddenField)item.FindControl("TagNameHidden"); string tagstring2 = TagCheckbox.Text.ToString(); if (TagCheckbox.Checked) { tagstring2 = tagstring2 + TagCheckbox.Text + " "; string[] keywords = tagstring2.Split(' ');I'm trying things, and it's not throwing errors, but it's not coming up with any results, either.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: dynamic Linq WHERE clause based on string from checkboxes
Jan 31, 2013 01:28 AM|LINK
You have to use "OR" or "IN" for a collection of SQL statement, or just use something like array's IndexOf method:
string[]values = xxx.Split(' ');
var result = from item in xxx.AsEnumerable()
where Array.IndexOf(values,item.Property)>=0
select item;