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 to write the following select SQL query as a LINQ query

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

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

My specific need is that the query return all records for that specific where condition if brandid or bodystyleid is blank (if the user does not pick any checkbox of a certain search option).

kindly direct me.

Thanks,

Paul

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

Accepted Answer

You must verify that the lists are empty in order to fulfill your need to return all items if none are given.

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 approach; adds check for no brand/style given)

Although I haven't really verified how this is translated back into SQL, it could be more effective to use a flag to indicate if any values exist:

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