We help IT Professionals succeed at work.

Implement SQL IN clause in LINQ

ExpertHelp79
ExpertHelp79 asked
on
Hello
in the below query
where c.categoryid == catid
categoryid is a integer feild in the table, i need to implement IN , but i am unable to get the contains method. Please help me to implement IN clause in the below code

TIA
SOS
public void LoadSubCategory(CheckBoxList chkSubCat, string catid)
        {
            try
            {
                chkSubCat.DataSource = from c in GAEntity.subcategorymasters
                                    orderby c.subcatdisplayname
                                    where c.categoryid == catid && c.status == 1 && c.deleted == 0
                                       select new { c.subcatid, c.subcatdisplayname };


                chkSubCat.DataTextField = "subcatdisplayname";
                chkSubCat.DataValueField = "subcatid";
                chkSubCat.DataBind();
            }
            catch (Exception Ex)
            {

            }
        }

Open in new window

Comment
Watch Question

Commented:
Hello,

Why do you need an IN whereas you have only a single value ? To have an IN clause, you need to have an array of int and call somethin like the following :
public void LoadSubCategory(CheckBoxList chkSubCat, string catid)
        {
            try
            {
int[] myArray=new int[]{catId, 12 };
                chkSubCat.DataSource = from c in GAEntity.subcategorymasters
                                    orderby c.subcatdisplayname
                                    where myArray.Contains(c.categoryid) && c.status == 1 && c.deleted == 0
                                       select new { c.subcatid, c.subcatdisplayname };


                chkSubCat.DataTextField = "subcatdisplayname";
                chkSubCat.DataValueField = "subcatid";
                chkSubCat.DataBind();
            }
            catch (Exception Ex)
            {

            }
        }

Open in new window

Author

Commented:
cannot convert type string to int

int[] myArray=new int[]{catId, 12 };

Author

Commented:
catid has value like (1,2,3,4)

Commented:
Sorry, I missed the point that catId is a string

string[] myArray=new string[]{catId, 12 };

Open in new window

Author

Commented:
Error at
myArray.Contains(c.categoryid)
=================================

Error      15      'string[]' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.Queryable.Contains<TSource>(System.Linq.IQueryable<TSource>, TSource)' has some invalid arguments      

Author

Commented:
string[] myArray=new string[]{catId, 12 };
error: at {catId, 12 };
Error      13      Cannot implicitly convert type 'int' to 'string'      
Commented:
if the parenthesis are in the string, you also need to take them out :
catId=catId.subString(1,catId.Length-2);

Open in new window


Then, you need to parse the string to build an int array :
string[] catIds=catId.split(',');
int[] cat=new int[catIds.Length];
for(int i=0;i<catIds.Length;i++)
cat[i]=int.Parse(catIds[i]);
 chkSubCat.DataSource = from c in GAEntity.subcategorymasters
                                    orderby c.subcatdisplayname
                                    where cat.Contains(c.categoryid) && c.status == 1 && c.deleted == 0
                                       select new { c.subcatid, c.subcatdisplayname };


                chkSubCat.DataTextField = "subcatdisplayname";
                chkSubCat.DataValueField = "subcatid";
                chkSubCat.DataBind();

Open in new window


To have the Contains method working, you need
using System.Linq;

Open in new window

Author

Commented:
cat.Contains(c.categoryid)  at this line

Error      14      'int[]' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.Queryable.Contains<TSource>(System.Linq.IQueryable<TSource>, TSource)' has some invalid arguments      

Commented:
Did you add the using to your existing usings on the top of your code file ?

Author

Commented:
yes its there

Commented:
Can you try replacing cat.Contains(c.categoryid) by Enumerable.Contains(cat, c.categoryid) ?

Author

Commented:
Error      13      The type arguments for method 'System.Linq.Enumerable.Contains<TSource>(System.Collections.Generic.IEnumerable<TSource>, TSource)' cannot be inferred from the usage. Try specifying the type arguments explicitly.      

still the error
var cat = ( from c in catid.Split( ',' ) select int.Parse( c ) ).ToList();

Explore More ContentExplore courses, solutions, and other research materials related to this topic.