Linq Where Clause - Accessing a tables column name.

Question

Hello all,

I need a bit of inspiration from somewhere and hoping someone, anyone could help.

This stems from a previous thread i created whereby I want to be able to do a autocomplete search on a complete data table i.e. all the data from all the columns.

I have created a stored procedure that pulls together the autocomplete items and the columns these items are derived from i.e.

tblAutoCompleteItems:

  Item Column
Item1 Product
Item2 Product
Item3 Product
Red   Category1
Green Category1
Blue  Category1
Small Category2
Medium   Category2
Large Category2

I've sorted the textbox autocomplete and that works fine. Based upon the client selection, a label displays which associated column this item is generated from (so the client can check they are happy with what their search will return). Lovely.

However, the tricky bit, and the bit I need help with, is pulling back another data set based upon the criteria they have selected. The other data set looking something along the lines of...

tblProductInfo:

Product        Category1   Category2
Item1       Red       Big
Item2       Red       Small
Item3       Blue        Small

For example, a user autocompletes for 'Item1', the textbox shows this and the label displays 'Product' Alternatively, a user may want to search by Category1 so searches for 'Red', this is presented in the text box and the label displays 'Category1'

When looking at tblProductInfo, I need to somehow query the dataset so that it looks at the autocomplete column and the autocomplete item.

So when I hit a button or whatever, the linq looks at the column first, says "oh I know, I need to look at Category1 and then pull back anything with Red in it" or "ok that's a Product, I'll head over to the Product column first and then pull back the data associated with Item1"

I'm struggling in two parts (1) How to access the column names within a table and (2) construct the where clause in the linq.

Any help, tips or advice gratefully received - even if it means a complete strategy rethink.

I'm attmepting this using c#, LINQ and asp.net.

By the by - I realise it is not ideal having the autocomplete look across a complete table but this is out of my hands...sadly.

Answer 1

Hey,

There are a few ways to get the metadata; you can pull it from the database through a stored procedure (SQL, Oracle have views that can extract a table's metadata), or if you are using LINQ to SQL, you can use Reflection to reflect on the class that represents tblProductInfo.

To construct the where clause, you will want to use dynamic LINQ: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

As this allows you to use string column names to perform the query.

HTH.

Answer 2

Unbelievably, I've worked it out myself! If anyone stumbles across this and looking for something similar, here is some lovely pseudo code:

public static List<tblInfo> GetProductInfo(string memberid, string locationid, string column, string acitem)
        {

           MyEntities getproductinfo = new MyEntities ();

            var r = (from p in getproductinfo.tblProductInfo 
                     where p.MemberId == memberid &&
                              p.LocationId == locationid
                     select p);

            if (column == "Product")
                r = r.Where(p => p.Product == acitem);

            if (column == "Category1")
                r = r.Where(p => p.Category1 == acitem);

return r.OrderBy(p => p.Product).ThenBy(p => p.Category1).ToList();

• Where column = the label text (in the example above) acitem = the entry in the autocomplete textbox memberID & locationID are just sessions used throughout.

Answer 3

I didn't run it through the compiler, but I think the syntax is good. Should result in a IEnumerable

var r = (from p in getproductinfo.tblProductInfo 
         where p.MemberId == memberid 
            && p.LocationId == locationid
            && column == "Product" ? p.Product == acitem :
               column == "Category1" ? p.Category1 == acitem : true
         orderBy p.Product
         thenBy p.Category1
         select p);

Answer 4

A slight modification on yours:

 MyEntities getproductinfo = new MyEntities (); 

 var r = (from p in getproductinfo.tblProductInfo  
          where p.MemberId == memberid && 
              p.LocationId == locationid 
          order by p.Product, p.Category1
          select p); 

 if (column == "Product") 
       r = r.Where(p => p.Product == acitem); 

 if (column == "Category1") 
       r = r.Where(p => p.Category1 == acitem); 

 return r.ToList();

LINQ will put the Where in the proper place.