How to write LINQ IN clause query which will work as LIKE operator as well?

Question

How we can write a LINQ query for following select sql query:

string brandid="1,2,3"
string bodystyleid="1,2,3"
-------------------
-----------------

select * from car
where brandid in (brandid)
and bodystyleid in (brandid)
----------------------
-------------------

My specific requirement is that if brandid or bodystyleid is blank(if user does not select any checkbox of a particular search option) query should return all record for that particular where condition.

Please guide me.

Thanks,

Paul

Answer 1

In order to fulfil your requirement about returning all items if none are specified, you need to check for the lists being empty.

var brands = brandid.Split(',').Select(x => Int32.Parse(x));
var styles = bodystyleid.Split(',').Select(x => Int32.Parse(x));

var result = from c in car
             where (!brands.Any() || brands.Contains(c.brandid))
                  && (!styles.Any() || styles.Contains(c.bodystyleid))
             select c;

(similar to sgmoore's solution, but includes the check for no brand/style specified)

I've not actually checked how this gets converted back to SQL - it may be more efficient to use a flag to indicate whether there are any values:

var brands = ....;   // As above
bool anyBrands = brands.Any()
var result = from c in car
             where (!anyBrands || brands.Contains(c.brandid))
               .....

Answer 2

Is bodystyleid meant to check brandid or bodystyleid? (I am assuming bodystyleid, however have wrote the query to match the query in the question (brandid))

As a start you could do:

var results = (from c in car
               where c.brandid.Contains(brandid)
               && c.bodystyleid.Contains(brandid)
               select c).ToList();