Conditional logic with linq query syntax

Question

I have the below conditional which executes one of two queries that solely differ by the additional clause

&& acc.Fb_DataSourceKey == dskId

Here's the conditional

var statData = dskId != -1 ? from s in dao.fb_statdata
                            join acc in dao.fb_datasourceadaccount
                            on s.Fb_DataSourceAdAccountId equals acc.Id
                            where s.ReportTypeId == 1
                            && acc.Fb_DataSourceKey == dskId
                            group new { s, acc.Fb_DataSourceKey }  by new { s.Fb_DataSourceAdAccountId, s.Start_time } into res
                            select res
                            :               
                            from s in dao.fb_statdata
                            join acc in dao.fb_datasourceadaccount
                            on s.Fb_DataSourceAdAccountId equals acc.Id
                            where s.ReportTypeId == 1
                            group new { s, acc.Fb_DataSourceKey }  by new { s.Fb_DataSourceAdAccountId, s.Start_time } into res
                            select res;

Now, thanks to https://stackoverflow.com/a/2850048/1170932 I know that I can change it to the below

            var statData = from s in dao.fb_statdata
                            join acc in dao.fb_datasourceadaccount
                            on s.Fb_DataSourceAdAccountId equals acc.Id
                            where s.ReportTypeId == 1
                            group new { s, acc.Fb_DataSourceKey } by new { s.Fb_DataSourceAdAccountId, s.Start_time } into res
                            select res;

            if (singleDsk)
                statData = from s in statData where s.Key.Fb_DataSourceAdAccountId == dskId select s;

This eliminates the code duplication but causes a horribly inefficient outer join to be used (understandably, really). FTR we're using MySQL for our DB.

The outer join is unfortunately unacceptably slow. Is there a way to do the query-style conditional without generating an outer join and without duplicating code?

Have you looked at something like [PredicateBuilder](http://www.albahari.com/nutshell/predicatebuilder.aspx)? — Preston Guillot, Mar 28 '14 at 18:05
Just please don't use this code. It's terribly unreadable. I would attempt to answer but I'm having too much trouble reading the code. — evanmcdonnal, Mar 28 '14 at 18:05
@evanmcdonnal - people are always asking for real life code rather than foo&bar or product&user. It's not that bad if you know the context and so on. — sming, Mar 28 '14 at 23:18
I dislike query syntax to begin with, add in a short hand if-else and I would berate you in a code review. — evanmcdonnal, Mar 28 '14 at 23:24
@evanmcdonnal all due respect but I don't think I'd listen. I like both query style and the short-hand in this scenario. — sming, Mar 29 '14 at 14:00

score 1 · Accepted Answer · answered Mar 28 '14 at 18:06

Do the filter before you do the join, not after.

var accounts = dao.fb_datasourceadaccount.AsQueryable();

if(dskId != -1)
    accounts = accounts.Where(acc => acc.Fb_DataSourceKey == dskId);

var statData = from s in dao.fb_statdata
                join acc in accounts
                on s.Fb_DataSourceAdAccountId equals acc.Id
                where s.ReportTypeId == 1
                group new { s, acc.Fb_DataSourceKey } 
                by new { s.Fb_DataSourceAdAccountId, s.Start_time } 
                into res
                select res;

Conditional logic with linq query syntax

1 Answers1