Microsoft Dynamics CRM (Archived)

How to write query with inner join and where clause in mscrm 2013

(0) Share

Report

Posted on by EmployeeOcta

HI All,

I want to write a query which is in sql server

like below.

select * from annotaionbase ab inner join contactbase cb

on ab.objectid=cb.contactid and ab.isdocument=true.

The same above query i want to convert as per mscrm queryexpression or whatever required

using c# language.

kindly help me to solve this question.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

Hi,

You can use fetchxml to achieve this.

Please have a look at below link.

stackoverflow.com/.../fetchxml-with-outer-joins-and-nested-inner-joins-is-it-possible-to-do-an-outer

Mansoor

Was this reply helpful? Yes No
Suggested answer

Rajkumar Rajaraman on at

Like (0)

Report

Hi,

Take a look to the following link:

stackoverflow.com/.../how-to-inner-join-with-query-expression-crm

Hope this helps.

Regards,

R.Rajkumar

"Please mark my answer as verified if you found it helpful"

Was this reply helpful? Yes No
EmployeeOcta 6 on at

Like (0)

Report

In my scenario i want to get columns from both the tables.

like

select ab.x,ab.y ,cb.x,cb.y from annotaionbase ab inner join contactbase cb

on ab.objectid=cb.contactid and ab.isdocument=true

Was this reply helpful? Yes No
Suggested answer

Rajkumar Rajaraman on at

Like (0)

Report

Hi,

Take a look for the sample.

var query_join1 = from c in svcContext.ContactSet

join a in svcContext.AccountSet

on c.ContactId equals a.PrimaryContactId.Id

select new

{

c.FullName,

c.Address1_City,

a.Name,

a.Address1_Name

};

Try it. Hope this helps.

Regards,

R.Rajkumar

"Please mark my answer as verified if you found it helpful"

Was this reply helpful? Yes No
EmployeeOcta 6 on at

Like (0)

Report

I want to use the queryexpression and filterexpression in my code.

so please suggest me answers based on the required functionality , considering the late bound into my c# code behind.

Was this reply helpful? Yes No
Suggested answer

Rajkumar Rajaraman on at

Like (0)

Report

Hi,

Refer the following link for your scenario:

msdn.microsoft.com/.../hh547457.aspx

Hope this helps.

Regards,

R.Rajkumar

"Please mark my answer as verified if you found it helpful"

Was this reply helpful? Yes No
Suggested answer

Royal King 27,686 on at

Like (1)

Report

Below code should work, you can add all the attributes you need in fetchxml for note as well as contact

var fetch = " <fetch version=\"1.0\" output-format=\"xml-platform\" mapping=\"logical\" distinct=\"false\">" +

" <entity name=\"annotation\">" +

" <attribute name=\"subject\" />" +

" <attribute name=\"notetext\" />" +

" <attribute name=\"filename\" />" +

" <attribute name=\"annotationid\" />" +

" <order attribute=\"subject\" descending=\"false\" />" +

" <filter type=\"and\">" +

" <condition attribute=\"isdocument\" operator=\"eq\" value=\"1\" />" +

" </filter>" +

" <link-entity name=\"contact\" from=\"contactid\" to=\"objectid\" alias=\"ab\">" +

" <attribute name=\"address1_county\" />" +

" <attribute name=\"address1_city\" />" +

" <filter type=\"and\">" +

" <condition attribute=\"contactid\" operator=\"eq\" uiname=\"+19172166873@tmomail.net\" uitype=\"contact\" value=\"{356E1E1A-7EB1-E411-80E3-C4346BAC3974}\" />" +

" </filter>" +

" </link-entity>" +

" </entity>" +

"</fetch>";

var response = slos.RetrieveMultiple(new FetchExpression(fetch));

Was this reply helpful? Yes No
EmployeeOcta 6 on at

Like (0)

Report

Thanx for your updates friends.

i have used the linq query to retrieve the required fields into the program.

But problem is arrising here with Timeout , as there are more than 80,000 records

in my database.

so i want to fetch all of those at ones .

Can anybody has the idea of How can i retrieve the records at ones using organizationproxy class into our code.

Thanx in advance.

Was this reply helpful? Yes No
Suggested answer

Rajkumar Rajaraman on at

Like (0)

Report

Hi,

Refer the following links:

www.avtex.com/.../using-linq-in-crm-2011-plugins

lakshmanindian.wordpress.com/.../2

Hope this helps.

Regards,

R.Rajkumar

"Please mark my answer as verified if you found it helpful"

Was this reply helpful? Yes No