QueryExpression With Multiple Join Conditions

(1) Share

Report

Posted on by Community Member

Hi,

I am trying to translate the following SQL query to a QueryExpression:

SELECT  a.AccountId, o.opportunityid
FROM    dbo.Account a
        INNER JOIN dbo.Opportunity o ON o.CustomerId = a.AccountId
        LEFT JOIN dbo.new_creditpackagefile cpf ON cpf.new_accountid = o.CustomerId
                                                             AND cpf.new_opportunityid = o.OpportunityId
                                                             AND cpf.new_documenttype = 32;

Here's how I would rewrite the above SQL to be possible via QueryExpression:

SELECT  DISTINCT a.AccountId, o.opportunityid
FROM    dbo.Account a
        INNER JOIN dbo.Opportunity o ON o.CustomerId = a.AccountId
        LEFT JOIN dbo.new_creditpackagefile cpf ON cpf.new_accountid = o.CustomerId AND cpf.new_documenttype = 32
	LEFT JOIN dbo.new_creditpackagefile cpf2 ON cpf2.new_opportunityid = o.OpportunityId AND cpf2.new_documenttype = 32

The downside of the second query is that it uses another left join and the DISTINCT operator.

Questions:

Do you think the additional join and DISTINCT operator would have a meaningful performance hit?
Will there be support for multiple join conditions in the future?

Thank you very much!

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

Srujan K 317 on at

Like (0)

Report

Yes there would be definitely a performance hit when you use distinct , best thing is to get the RAW data from CRM and play around with LINQ queries.

Was this reply helpful? Yes No
ashlega 34,477 on at

Like (0)

Report

As an option, you might create a text field on the opportunity entity, store a combination of those guids there (accountid_opportunitid), do the same for the new_creditpackagefile entity, and, then, use a join on those new fields

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for taking the time to answer my question. The LINQ route might be easiest without having to create any additional attributes.

By the way: here's another answer that I really liked from CRMUG forums: https://goo.gl/481Wqt

I did not even think to simplify the SQL query.

Thank you,

Tony

Was this reply helpful? Yes No