How can I build a LINQ IN clause query that also acts as a LIKE operator?

entity-framework in-clause linq linq-to-entities sql-like

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

1
4
5/24/2012 5:35:33 PM

Accepted Answer

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))
               .....
6
5/24/2012 6:07:16 PM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow