CODEWITHSUNDEEP
c#linqlinq-query-syntax
sming
sming

Reputation: 811

Conditional logic with linq query syntax

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?

Upvotes: 1

Views: 1519

Answers (1)

Servy
Servy

Reputation: 203842

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;

Upvotes: 1

Related Questions