Need some help with a query and LINQ

06-30-2008 10:33 AM |

Let me show my small DB schema first:

---------------------------------------------------------
tblPhotos
---------------------------------------------------------
PhotoID  |  PhotoName
---------------------------------------------------------

---------------------------------------------------------
tblAttributes
---------------------------------------------------------
AttributeID  |  PhotoID  |  Attribute
---------------------------------------------------------

All the photos are stored in tblPhotos.  Attributes describe what is in the photo.  So let's say I have a photo with PhotoID of 2.  The photo is of a cat and a dog.  So in tblAttributes, there will be two entries for photo 2, one with the Attribute of cat, and the other with the Attribute of dog.
 

Right now I just have a ListBox that will pull all my photos from a table, and insert them into a ListBox:

     public List<Photo> GetPhotos()

     {

         DataClasses1DataContext db = new DataClasses1DataContext();

         var photos = from p in db.Photos

                            select p;

         return photos.ToList();

     }

But now, I'd like to filter the the photos based on what a user would like to see.  I have several checkboxes and a button to filter the photos.  There are options like "cat", "dog", "mouse", etc...

So if the user selects "cat" and "dog", Photo 2 will appear.  Photo 2 will also appear if they only select "cat", and again if they only select "dog".

I'm not exactly sure how to implement this, or how to design my query.  I was thinking about creating a comma delimited string based on the values checked, but I don't know how to design the query for this.  Any help would be greatly appreciated.

Re: Need some help with a query and LINQ

06-30-2008 11:31 AM |

Do you want to return the whole list photos to the Silverlight side then do the filter on that list or you want to filter on the database level?

If you want to do it on  the Silverlight code after you got the full list: Say fullList is the name of your list returned from WCF call.

 List<YourWCFService.Photo> filteredList = (from p in fullList

                                                               where p.Attribute == "cat"

                                                              select p).ToList();

If you want to do it on the DB level:

 public List<Photo> GetPhotos(string category)

     {

         DataClasses1DataContext db = new DataClasses1DataContext();

         var photos = from p in db.Photos

                            where p.Attribute == category

                            select p;

         return photos.ToList();

     }

 

Re: Need some help with a query and LINQ

06-30-2008 11:31 AM |

You should create a function return bool and do a foreach
Of course, you should also get the list of attribute list of all photos

For the function, what you have to pass is the attribute name, only pass one, then check if the corresponding check box is checked or not, and return the bool.

For the foreach, it should be implemented with the function. That is, using foreach to do all the attributes in the attribute list. It is sure that you should make the attribute list and photolist corresponding to each other for a easy work around. You should first create a variable called shown. Then, Foreach attribute list in the List,  and do a nested foreach, to see if all attribute in the each attribute list are set as true, after check each List, you can use the if case, to see the photo should be shown or not.

If you want to get the code, please let me know, as I dont know if you get what I mean.

Re: Need some help with a query and LINQ

06-30-2008 12:10 PM |

SteveWong:

You should create a function return bool and do a foreach
Of course, you should also get the list of attribute list of all photos

For the function, what you have to pass is the attribute name, only pass one, then check if the corresponding check box is checked or not, and return the bool.

For the foreach, it should be implemented with the function. That is, using foreach to do all the attributes in the attribute list. It is sure that you should make the attribute list and photolist corresponding to each other for a easy work around. You should first create a variable called shown. Then, Foreach attribute list in the List,  and do a nested foreach, to see if all attribute in the each attribute list are set as true, after check each List, you can use the if case, to see the photo should be shown or not.

If you want to get the code, please let me know, as I dont know if you get what I mean.

 

You may need to post the code, I'm not exactly sure what you mean.