Converting a fetchXML query into LINQ

(0) Share

Report

Posted on by Khaled Rimawi

145

Hi ,

I am trying to convert this fetchXML query into LINQ:

But i am struggling in doing that , could any one help please?

I have the same question (0)

All responses (5)

Answers (1)

Joana Pinto 740 on at

Like (0)

Report

Hi,

Just a question, do you really need it to be on LINQ? Converting to QueryExpression is a better option (I think). In terms of performance and being easier.

Cheers

Was this reply helpful? Yes No
Khaled Rimawi 145 on at

Like (0)

Report

Hi Joana , i'm just learning LINQ and i want to see how this will be converted into it

Was this reply helpful? Yes No
cloflyMao 25,210 on at

Like (0)

Report

Hi Khaled,

i build code with your business logic.

            OrganizationServiceContext orgContext = new OrganizationServiceContext(organizationService);

            var myLinqQuery = from emailEntity in orgContext.CreateQuery("email")

            join activityPartyEntity in orgContext.CreateQuery("activityparty")

            on emailEntity["activityid"] equals activityPartyEntity["activityid"]

            join queueEntity in orgContext.CreateQuery("queue")

            on activityPartyEntity["activityid"] equals queueEntity["queueid"]

            where(emailEntity["directioncode"]).Equals(true)

            where((int) emailEntity["statuscode"]).Equals(3) ||

             ((int) emailEntity["statuscode"]).Equals(4)

            orderby(emailEntity["modifiedon"]) descending

            select new {

             subject = emailEntity.GetAttributeValue < string > ("subject"),

              regardingobjectid = emailEntity.GetAttributeValue < EntityReference > ("regardingobjectid"),

              sender = emailEntity.GetAttributeValue < EntityCollection > ("from"),

              to = emailEntity.GetAttributeValue < EntityCollection > ("to"),

              statuscode = emailEntity.GetAttributeValue < OptionSetValue > ("statuscode"),

              modifiedon = emailEntity["modifiedon"],

              activityid = emailEntity["activityid"],

            };

            StringBuilder builder = new StringBuilder();

            foreach(var results in myLinqQuery) {

             builder.Append("============================================" + "\n");

             builder.Append(" modifiedon: " + results.subject + "\n");

             if (results.regardingobjectid != null) {

              builder.Append(" regarding: " + results.regardingobjectid.Name + "\n");

             } else {

              builder.Append(" regarding: none" + "\n");

             }

             foreach (var sendItem in results.sender.Entities)
{
builder.Append(" sender: " + sendItem.GetAttributeValue<string>("addressused") + "\n");
}
foreach (var receiveItem in results.to.Entities)
{
builder.Append(" receiver: " + receiveItem.GetAttributeValue<string>("addressused") + "\n");
}

             builder.Append(" statuscode: " + results.statuscode.Value + "\n");

             builder.Append(" modifiedon: " + Convert.ToDateTime(results.modifiedon).ToString("d") + "\n");

             builder.Append(" activityid: " + results.activityid + "\n");

             builder.Append("============================================");

            }

            Console.WriteLine(builder.ToString());

Snippet of LINQ is similar to SQL,

just import OrganizationServiceContext to work with LINQ query,

and give columns specific primary type in new statement,

also pay attention to lookup field, partylist field.

Regards,

Clofly

Was this reply helpful? Yes No
Suggested answer

RaviKashyap 55,410 Moderator on at

Like (0)

Report

Hi,

You can try LINQPad. Refer below-

blogs.it.ox.ac.uk/.../

Hope this helps.

Was this reply helpful? Yes No
Verified answer

LeoAlt 16,331 Moderator on at

Like (0)

Report

Hi partner,

I suggest that you could convert the fetch xml to sql query first and then convert to linq.

In this case, the sql query should be

"select e.subject,e.regardingobjectid, e.from, e.to, e.prioritycode, e.statuscode, e.modifiedon, e.activityid,

from emial e

inner join activityparty a on e.activityid = a.activityid

inner join queue q on a.queueid = q.partyid

where

e.directioncode=1 and e.statuscode in (6,8)“

Then I convert it to linq:

var querylist= from e in emial

join a in activityparty on e.activityid = a.activityid

join q in queue on a.queueid = q.partyid

select new

{

e.subject.......(The fields you need to query)

}

Note that:

The alias of the entity and the field of the query need to correspond one to one

Hope it helps.

Best Regards,

Leo

Was this reply helpful? Yes No