Query Data Using LINQ C# With Filter string[] array?

Question

Based on the documentation here: http://docs.composite.net/Data/AccessingDataWithCSharp/How-to-Query-Data-Using-LINQ#_How_to_Query

I need to query data in a table using a filter in type string[int1,int2,int3..] and can't work out how to go about it.

The string[] comes from a different table field which stores id values of a multiselect element on a form:

Table 'Profile' (AGlobal.profile) contains columns:

Id   Types(profile_accomtypes)
1    1,2
2    4,7
3    12,4,6
4    3,6,9

Then I have a static table 'TypeDesc' (ALocal.proptype) listing a total of 12 'Type' values:

Id   Description(proptype_names)
1     The first description
2     The second description
........
12    The twelfth description

I created a strongly coded class enabling me to easily handle the form content on submit from the client. Within the form was a couple of multiselects (one of them being 'Types" above in the Profile datatype table.) Each of the multiselects are passed to the server in serialized JSON format where I string.Join the 'Types' values with a comma separator to save into the Profile.Types column.

Now I want to serve the selections in a profile page to the client by loading the Types string[] of Profile Id and using the int id values to filter the TypeDesc table to only select the Type values with Description so that I can render the descriptions as a bullet list on the client.

The filter Types in the Profile table are always id integers My code I'm using is:

var myProftype =
   (from d in connection.Get<AGlobal.profile>() // find multiselected type string values
   where d.Id == StUserSet.utoken
   select d).First();
   string sProftype = myProftype.profile_accomtypes;                
   string[] sTypes = sProftype.Split(',');

// now filter proptypes to sTypes
var myTAccomtypes =
   (from d in connection.Get<ALocal.proptype>() // get all the types from the DB
   where(r => sTypes.Contains(r.Field<int>("Id"))) //Lambda ?
   select d).All;

StringBuilder sb = new StringBuilder(0); //create a bullet list string
// Loop over strings
    foreach (string s in myTAccomtypes)
    {
          sb.append("<dd>"+ s +"</dd>");
    }
   TuAccomtypes = sb.ToString();  // pass string to JQuery Taconite as part of AJAX response to alter DOM.

I have an error on the Lambda trying to filter my types. In VS2010:

Error = Cannot convert lambda expression to type bool because its not a delegate type.

I also do not know how to go about parsing the sTypes variables to int (if I need to) so that the filter works :(

Where am I going wrong? Is there a cleaner way to filter a dataset against a comma separated list queried from a column field within a db table?

Thank you for any help/ideas in advance. Martin.

Answer 1

Once splitting the string var from the original Linq query (which identified a single field with a joined string of comma separated id numbers.) I wasn't able to use "Contains" properly. I cast the second Linq query ToList which evaluated the collection.

Then instead of working with a full result I limited the result to just the id and name fields.

Relying on an article posted by Vimal Lakhera:

http://www.c-sharpcorner.com/uploadfile/VIMAL.LAKHERA/convert-a-linq-query-resultset-to-a-datatable/ I converted the result set into a DataTable which allowed easy looping and selection of fields to output as an html string as part of a JQuery Taconite callback.

Here's what works for me...

// now filter proptypes to selected Types|
var myTAccomtypes = from d in connection.Get<ALocal.proptype>().ToList()
    // ToList() will evaluate collection, you cannot pass sTypes array of integers to a sql query, at least not in that way
    where sTypes.Contains(d.proptype_id.ToString())
    select new { d.proptype_id, d.proptype_name };

    DataTable AcomType = LINQToDataTable(myTAccomtypes);

    StringBuilder sb = new StringBuilder();
    // Loop over table rows
    foreach (var row in AcomType.Rows.OfType<DataRow>().Take(19))  // will .Take up to a maximum of x rows from above
    {
         sb.Append("<dd>");
         sb.Append(row["proptype_name"].ToString());
         sb.Append("</dd>");
     }
     HldUserSet.TuAccomtypes = sb.ToString();
     //HldUserSet.TuAccomtypes = string.Join(",", myTAccomtypes); //Check query content

Using Vimal's 'LINQToDataTable" with the tweak in the LINQ request means that I can use the class in numerous places of the site very quickly.

This works a treat for those with a single string of joined id's in the form of "2,7,14,16" that need to be split and then used to filter against a wider collection matching the id's from the string to record id numbers in a different collection.

Answer 2

I'm not entirely sure about your model, but I think this will work for you. I changed your linq, and combined some statements. I'm also casting your Id field to string so that it can be found correctly in the array.Contains() function. You might want to do the reverse of casting your strings to ints and comparing that way, but that's up to you.

    var myProftype = profiles.First(p => p.Id == StUserSet.utoken);
    string sProftype = myProftype.profile_accomtypes;
    string[] sTypes = sProftype.Split(',');
    var myTAccomtypes = propTypes.Where(r => sTypes.Contains(r.Field<int>("Id").ToString()));
    StringBuilder sb = new StringBuilder(0);

    foreach (PropType s in myTAccomtypes)
    {
        sb.Append("<dd>" + s.Description + "</dd>");
    }