CODEWITHSUNDEEP
dynamics-crm-2011crm
Kartik Patel
Kartik Patel

Reputation: 9623

How Can I Join On Two Columns with a LinkEntity in CRM 2011?

I am using a Link Entity to join a two entities:

LinkEntity pricelevelentity = new LinkEntity();
pricelevelentity.JoinOperator = JoinOperator.Inner;
pricelevelentity.LinkFromEntityName = "product";
pricelevelentity.LinkFromAttributeName = "productid";

pricelevelentity.LinkToEntityName = "productpricelevel";
pricelevelentity.LinkToAttributeName = "productid";
query.LinkEntities.Add(pricelevelentity);

The above is joining Product and ProductPriceLevel on the ProductId attribute. I also want to add to the join Product.uomid on ProductPriceLevel.DefaultUomId

How can i do this?

Upvotes: 0

Views: 4069

Answers (2)

Daryl
Daryl

Reputation: 18895

Not sure how James's answer was marked as the answer, but you can not join on more than one column using a linked entity. You can use James's answer to return both values, and perform a join on the client side though, but CRM doesn't support this type of Join:

Select * FROM A INNER JOIN B ON A.OneId = B.OneId AND A.TwoId = B.TwoId

If you know the value of the Product's UomId, you can add it to a where clause though:

var qe = new QueryExpression("product");
var productPriceLevel = new LinkEntity("product", "productpricelevel", "productid", "productid", JoinOperator.Inner);
qe.LinkEntities = productPriceLevel;
productPriceLevel.LinkCriteria.AddCondition("defaultuomid", ConditionOperator.Equal, product.UomId);

Which would effectively look like this:

Select * FROM A INNER JOIN B ON A.OneId = B.OneId AND B.TwoId = 10 -- the Value of whatever the single A.TwoId value is you're looking for.

Upvotes: 1

James Wood
James Wood

Reputation: 17562

If you are attempting to return a column for a linked/joined entity to be able to perform additional filtering on the client side, LinkEntity has a Column property you should use, for example:

new QueryExpression("product")
{
    ColumnSet = new ColumnSet("oumid"),
    LinkEntities =
    {
        new LinkEntity("product", "productpricelevel", "productid", "productid", JoinOperator.Inner)
        {
            Columns = new ColumnSet("defaultuomid")
        }
    }
};

Upvotes: 2

Related Questions