CODEWITHSUNDEEP
c#sqllinqlinq-to-sqllinq-to-entities
ArMaN
ArMaN

Reputation: 2407

Condition in Linq Expression

I have a LINQ expression that joins two tables. I want to conditionally check another boolean value: (notice text between ********* below)

bool status = testfunc();

var List = 
    from t in Houses
    join k in Tbl_HouseOwner on t.HouseCode equals k.HouseCode
    where k.ReqCode== t.ReqCode 
    *********** if (status) { where k.Name.Contains(Name) } **********
    select new
    {
        ...
        name = k.Name,
        ...
    };

Upvotes: 1

Views: 150

Answers (3)

tmaj
tmaj

Reputation: 34947

I do it this way:

        IQueryable<X> r = from x in Xs
                                  where (x.Status == "Active")
                                  select x;
        if(withFlagA) {
            r = r.Where(o => o.FlagA == true);
        }

To fit this to your example, firstly you could do this:

 IQueryable<YourOwnerType> filteredOwners = Tbl_HouseOwner;
 if( status ) {
     filteredOwners = filteredOwners.Where( o => o.Name.Contains(Name) );
 }

Then substitute Tbl_HouseOwner with filteredOwners.

var List = 
from t in Houses
join k in filteredOwners on t.HouseCode equals k.HouseCode
where k.ReqCode== t.ReqCode 
//Nothing here 
select new
{
    ...
    name = k.Name,
    ...
};

Now, you may know this, but the point here is that the initial .Where does not 'reach out' to the database. Your query doesn't get executed either until you start enumerating it (e.g. foreach) or call a method like ToList(), First(), FirstOrDefault(). This means you can call .Wheres after your selects if you prefer and the final query will still be efficient.

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1499770

An alternative option to dasblinkenlight's answer (which should work fine) is to build up the query programmatically. In this case you're effectively changing the right hand side of the join, so you could use:

IQueryable<Owner> owners = Tbl_HouseOwner;
if (status)
{
    owners = owners.Where(k => k.Name.Contains(Name));
}

Then:

var query = from t in Houses
            join k in owners on t.HouseCode equals k.HouseCode
            where k.ReqCode == t.ReqCode
            select new { ... };

Which approach is the most suitable depends on your scenario. If you want to add a variety of different query filters, building it up programmatically can be cleaner - and make the final SQL easier to understand for any given query. For a one-off, dasblinkenlight's approach is simpler.

Also note that in LINQ to Objects at least, it would be more efficient to join on both columns:

var query = from t in Houses
            join k in owners 
            on new { t.HouseCode, t.ReqCode } equals new { k.HouseCode, k.ReqCode }
            select new { ... };

In any flavour of LINQ which translates to SQL, I'd expect this to be optimized by the database or query translation anyway though.

Upvotes: 4

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You can use status to mask the condition, like this:

where k.ReqCode == t.ReqCode && (!status || k.Name.Contains(Name))

If the status is false, the OR || will succeed immediately, and the AND && will be true (assuming that we've got to evaluating the OR ||, the left-hand side of the AND && must have been true). If the status is true, on the other hand, the k.Name.Contains(Name) would need to be evaluated in order to finish evaluating the condition.

Upvotes: 7

Related Questions