CODEWITHSUNDEEP
linq
JBeckton
JBeckton

Reputation: 7111

Linq help with SQL in() statement

I need help writing a Linq statement that does the same as the following sql query...

select col1, col2, col3
from table1
where col1 = 'foo'
and col2 = 0
and IsNull(col3, '') IN('A','')

thanks

Upvotes: 0

Views: 90

Answers (3)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47038

from t in context.table1
where
   t.col1 == "foo"
&& t.col2 == 0
&& (new string[]{"A", string.Empty}).Contains(t.col3.DefaultIfEmpty(string.Empty))
select new {t.col1, t.col2, t.col3};

Upvotes: 3

Tom
Tom

Reputation: 3374

The Contains operator is a special one, which you can say something along the lines of

List<string> col3s= new List<string>() { "A", "B" };

from c in table1
where c.col1 = "foo" && && c.col2 = 0 && col3s.Contains(c.col3)
select new { c.col1, c.col2, c.col3 };

Upvotes: 2

Jan
Jan

Reputation: 16032

var q = from row in YourTableObject
        where row.col1 = "foo"
              && row.col2 = 0
              && (string.IsNullOrEmpty(row.col3) || row.col3 == "A")
        select new {
                       col1,
                       col2,
                       col3
                    }

Upvotes: 1

Related Questions