CODEWITHSUNDEEP
c#linq
Shazoo
Shazoo

Reputation: 785

Combining two LINQ statments into one

Hi I have some LINQ code that I want to run but the criteria depends on whether the country variable that I pass to the function is null or not.

So, if the country variable is null I want to run : -

  var resorts = from r in db.WRESORT
                      where new[] { "AUSTRIA", "FRANCE", "ITALY" }.Contains(r.COUNTRY)

and if country has a value I want to run

  var resorts = from r in db.WRESORT where r.COUNTRY == country

The question is, is there a way to combine the 2 statement into one?

Thanks

Upvotes: 2

Views: 67

Answers (4)

Jon Hanna
Jon Hanna

Reputation: 113382

If it really had to be done as a single query, then:

var resorts = from r in db.WRESORT
  where (
    country == null ? new[] { "AUSTRIA", "FRANCE", "ITALY" } : new[]{country})
    .Contains(r.COUNTRY);

But why not:

var resorts = country == null
  ? from r in db.WRESORT
    where (new[] { "AUSTRIA", "FRANCE", "ITALY" }.Contains(r.COUNTRY)
  : from r in db.WRESORT where r.COUNTRY == country;

Many databases and other providers can do better with a single equality check (=) than a contains (IN (…)), and since the type of the query is going to be the same either way, this will work, and produce a query of the same type. What harm that the code branch happens outside of the linq-y bit?

In this case you can also go for the more readable:

IQueryable<Resort> resorts;
if(country == null)
  resorts = from r in db.WRESORT
    where (new[] { "AUSTRIA", "FRANCE", "ITALY" }.Contains(r.COUNTRY);
else
  resorts = from r in db.WRESORT where r.COUNTRY == country;

Because the type parameter of the IQueryable isn't anonymous. If you had something like .Select(r => new{r.COUNRY, r.Name}) then this wouldn't be possible.

Upvotes: 1

sentece
sentece

Reputation: 166

intersection:

var resorts = 
from r in db.WRESORT
where new[] { "AUSTRIA", "FRANCE", "ITALY" }.Contains(r.COUNTRY) && r.COUNTRY == country;

or

union:

var resorts = from r in db.WRESORT
                      where new[] { "AUSTRIA", "FRANCE", "ITALY" }.Contains(r.COUNTRY) || r.COUNTRY == country;

Upvotes: -1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

May be this?:

var resorts = from r in db.WRESORT 
              where (country != null && r.COUNTRY == country) ||
                    (country == null && new[] { "AUSTRIA", "FRANCE", "ITALY" }.Contains(r.COUNTRY))

Upvotes: 1

Satpal
Satpal

Reputation: 133453

You can use check if country has value then create an array using country otherwise create using predefined list

var arr = string.IsNullOrEmpty(country) 
    ? new[] { "AUSTRIA", "FRANCE", "ITALY" }
    : new[] { country };

var resorts = from r in db.WRESORT where arr.Contains(r.COUNTRY);

Upvotes: 5

Related Questions