CODEWITHSUNDEEP
sqllinqfilterwhere-clause
user990574
user990574

Reputation: 21

LINQ to SQL: subfilter in where conditions

This question may have been answered somewhere (and if so I would love to have the link!). But since I don't know what type of linq query I'm trying to do, I haven't been able to find anything to help me.

I already have a very simple query setup to get all records of type DomainSequences.

var query = db.DomainSequences;

There are different types of domain sequences. I want to get all of them. However, for records that have a DomainType attribute equal to 'AT', then I only want to return some of those of 'AT' records (ie. do a where clause on those to filter the 'AT' records but still want to return all non 'AT' records.

More simply put, I want to get all DomainSequence records, but for the records that have a DomainType == "AT", then return those only if they meet certain conditions.

I can think of a way of doing it by doing something like:

 query.Where(x => x.DomainType != "AT" || (x.DomainType == "AT" && AT conditions....));

I think this should work but the problem comes when I have to do subfilters on other columns and then it starts to get messier and complicated very quickly.

Ideally, I would like to do something like

 query.Where(x => x.DomainType == "AT" || x.DomainType == "KS")
      .WhereIf(y => y.DomainType == "AT" then apply filter on those)
      .WhereIf(z => z.DomainType == "KS" then apply filter to these);

I'm not sure if there is a way to do this type of subfilter in LINQ or in SQL (though I imagine there is). Any suggestions on how this could be done relatively cleanly?

Thanks!

Upvotes: 2

Views: 547

Answers (2)

Anthony Pegram
Anthony Pegram

Reputation: 126834

It's really not all that different than what you might write in SQL. In fact, if you would try the query expression syntax approach, you might find that particular connection easier to make.

var query = from domain in db.DomainSequences 
            where (domain.DomainType == "AT" && domain.Foo == 42 && ...)
                || (domain.DomainType == "KS" && domain.Foo = 117 && ...)
            select domain;

That maps nicely to the SQL you might expect to write 

SELECT *
FROM DomainSequences
WHERE
    (DomainType = 'AT' AND Foo = 42 AND ...)
OR 
    (DomainType = 'KS' AND Foo = 117 AND ...)

You could, of course, keep it in the fluent extension method syntax using lambdas, of course, it's just a single .Where(...) invocation, after all.

Upvotes: 2

Stu
Stu

Reputation: 15769

query.Where(x => x.DomainType == "AT" || x.DomainType == "KS")
     .Where(y => y.DomainType != "AT" || other filter)
     .Where(z => z.DomainType != "KS" || other filter);

Upvotes: 1

Related Questions