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

score 6 · Accepted Answer · answered May 24 '12 at 18:07

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))
               .....

thanks it has worked for me. Would you please suggest me, in this scenario, performance wise writing Stored procedure or using LINQ which one will be better? — Paul, May 25 '12 at 16:44
@Paul - see for yourself, run Sqlserver's trace tool to see what SQL is generated, then see if you can write better SQL. Go with whatever has the better performance or execution plan. — gbjbaanb, Sep 30 '13 at 13:17

Darren · Answer 2 · 2012-05-24T17:44:03.683

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();

edited May 24 '12 at 17:44

answered May 24 '12 at 17:37

Darren

68,902
24
138
144

score 2 · Answer 3 · answered May 24 '12 at 17:52

var brandids     = brandid    .Split(',').Select(n => int.Parse(n)).ToList();
var bodyStyleids = bodystyleid.Split(',').Select(n => int.Parse(n)).ToList();


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

score 0 · Answer 4 · answered May 24 '12 at 17:50

the Ids you have are as strings with comma delimiter, you need them to be collections like List of the same type as your Ids of the Car table, so if brandid column is int then brandids has to be List<long>, then you can do

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

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

4 Answers4