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?

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
– evanmcdonnal, Commented Mar 28, 2014 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
– sming, Commented Mar 28, 2014 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
– evanmcdonnal, Commented Mar 28, 2014 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
– sming, Commented Mar 29, 2014 at 14:00

Servy · Accepted Answer · 2014-03-28 18:06:41Z

1

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;

answered Mar 28, 2014 at 18:06

Servy

204k27 gold badges347 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sming Over a year ago

so simple. Much appreciated!

Collectives™ on Stack Overflow

Conditional logic with linq query syntax

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related