CRM 2011 – CRM and LINQPad – An excellent way to test you LINQ CRM queries

If you are using CRM 2011 with an on premise then you should be taking advantage of the new LINQ capabilities.  LINQ is really good to use because it’s basically means that instead of writing SQL queries you can write .NET code to access the database.  One warning I will give you is LINQ is not compatible with CRM 2011 online I think it’s because you can’t put it in the sandbox because LINQ uses transactions, although I’m not sure of the actual reason all I am saying is be careful because you might find yourself rewriting the LINQ queries in query expression if you change to CRM 2011 online.  On the other hand if it’s an on premise solution then you should definitely use LINQ

I have written a blog entry before about CRM 2011 – getting started with LINQ

Today on Jamie Miley’s blog entry about using LINQ and CRM

There is an excellent tool which you can use to practise writing your LINQ queries and it’s called linqpad and you can read more about it and download it here 

here is a bit of a description

roject Description
LINQPad Plug-in for Microsoft Dynamics CRM 2011 lets you use LINQPad tool to query Microsoft Dynamics CRM 2011 with the CRM LINQ provider shipped with Microsoft Dynamics CRM 2011 SDK.

LINQPad (http://www.linqpad.net/) is a tool that may be described as “SQL Management Studio for LINQ queries”. LINQPad supports many standard .NET LINQ providers as well, as many of nonstandard ones. This project is intended to add support for the Microsoft Dynamics CRM 2011 LINQ-to-CRM provider.

Advertisements

CRM 4 LINQ examples

Before Microsoft released CRM 2011 they warmed up many of the features like early bound classes and LINQ with later rollups of CRM 4 and the advanced developer extensions.

I have been busy working with CRM 2011 and haven’t yet used early bound classes and linq in CRM 4 yet, as I mainly asked to do bug fixing for the CRM 4 installations we have I tend to just add to/amend what is already there rather than go a bit further and use the advanced developer extensions.  I think I am secretly hoping all the customers will upgrade to CRM 2011 where I know how to do this stuff.  This of course is ignoring the fact you can only use LINQ for on premise installations because you can’t use it with CRM 2011 online.

I saw a great article today on CRM 4 and linq from pogo69 who has recently been writing some excellent blog CRM developer blog posts (keep up the work good sir).

You can read the blog here

It’s basically shows you lots of examples of CRM 4 linq queries which as developer is exactly what you want because you can copy and adjust them, which is how developers work, once you can get an example working then that’s 90 percent of the battle done, unless it’s a really easy example which you then find impossible to change into the complex version of it you need to write.

The blog builds up the complexity of the queries

below is one of the examples but the blog posts goes into great depth and has multiple examples, so if you are thinking about using LINQ in CRM 4 this is a must read and will get you well on your way.

protected class AppointmentDataItem
{
	// appointment attributes
	public Guid AppointmentId { get; set; }
	public DateTime ScheduledStart { get; set; }
	public DateTime ScheduledEnd { get; set; }
	public DateTime Booked { get; set; }

	// case (job) attributes
	public string JobTicketNumber { get; set; }
	public Guid JobId { get; set; }

	// contact (customer) attributes
	public string CustomerName { get; set; }
	public string CustomerTelephone { get; set; }
	public string CustomerAddress { get; set; }
	public string CustomerCity { get; set; }
	public string CustomerState { get; set; }
	public string CustomerPostcode { get; set; }
	public string CalendarDisplayTitle { get; set; }
}

...

List<AppointmentDataItem> appointments =
	(
		from app in xrm.appointments
		join cs in xrm.incidents on app.regardingobjectid.Value equals cs.incidentid
		where cs.msa_partnercontactid.Value == contactId
		select new AppointmentDataItem
			{
				AppointmentId = app.activityid,
				ScheduledStart = activity.scheduledstart.GetValueOrDefault().ToLocalTime(),
				ScheduledEnd = activity.scheduledend.GetValueOrDefault().ToLocalTime(),
				Booked = activity.createdon.GetValueOrDefault().ToLocalTime()
			}
	).ToList();

...

foreach (AppointmentDataItem appointment in appointments)
{
	// get the Case object to which the Appointment is attached
	var job =
	(
		from cs in xrm.incidents
		join app in xrm.appointments on cs.incidentid equals app.regardingobjectid.Value
		where app.activityid == appointment.AppointmentId
		select cs
	).Single();

	appointment.JobId = job.incidentid;
	appointment.JobTicketNumber = job.ticketnumber;

	// get the Customer Contact to whom the Case is attached
	var customer =
	(
		from cust in xrm.contacts
		join cs in xrm.incidents on cust.contactid equals cs.customerid.Value
		join app in xrm.appointments on cs.incidentid equals app.regardingobjectid.Value
		where app.activityid == appointment.AppointmentId
		select cust
	).Single();

	appointment.CustomerName = customer.fullname;
	appointment.CustomerAddress = customer.address1_line1;
	appointment.CustomerCity = customer.address1_city;
	appointment.CustomerState = customer.address1_stateorprovince;
	appointment.CustomerPostcode = customer.address1_postalcode;
	appointment.CustomerTelephone = customer.telephone2;
	appointment.CalendarDisplayTitle = customer.fullname + " " + FormatTelephone(customer.telephone2) + "<br />" + FormatAddress(activity);
}

CRM 2011 – When to Attach an entity to the OrganizationServiceContext

I have been writing some plugins in CRM 2011 and for each plugin I use I usually setup a class which does link querying.  To do this I pass the OrganisationServiceContext

In my plugin I create this

// Get a reference to the Organization service.

IOrganizationServiceFactory factory =

(IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

IOrganizationService service = factory.CreateOrganizationService(context.UserId);

Microsoft.Xrm.Sdk.Client.OrganizationServiceContext orgContext =

new Microsoft.Xrm.Sdk.Client.OrganizationServiceContext(service);

I have now have my OrganizationServiceContext which I can use in my Linq queries

public IEnumerable<hosk_candidate> searchCandidateID(Microsoft.Xrm.Sdk.Client.OrganizationServiceContext orgContext, string candidateId)

{

try

{

IEnumerable<hosk_candidate> candidates = from c in orgContext.CreateQuery<hosk_candidate>()

where (c.hosk_CandidateHoskID.Contains(candidateId))

select c;

//hosk_candidate candidate = (hosk_candidate)this.returnOne(candidates);

return candidates;

}

catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault>)

{

// You can handle an exception here or pass it back to the calling method.

throw;

}

}

I have class in between the Plugin and Linq query class which gets the results

IEnumerable<hosk_candidate> candidates = linqQueries.searchCandidateID(this.orgContext, searchString);

The reason I have done it like this is so I can easily test the code because it isn’t in the plugin class itself.  I can create the OrganisationServiceContext easily and then pass it to the code the same way as the plugin code does.

I have found out that this works slightly unusually with regards to updating the data.  When you use Linq queries it Tracks the changes you make, these are tracked by the Service Context.

What this means is if you run a Linq query which returns some data and you then want to update the entity you plugin passed you can’t just add the object.

Initially I was working with an entity, I would then

orgContext.UpdateObject(can);

orgContext.SaveChanges();

add my entity to Context and update and then save like above, this works fine as long as you haven’t been querying different values in your linq query.

I tried to add a different entity to be updated (a different entity than the entities returned by my Linq) query and I kept getting errors thrown up.

I then worked out that I had to attach the entity and then update and save the changes.

orgContext.Attach(can)

orgContext.UpdateObject(can);

orgContext.SaveChanges();

I am now in situation where I do a linq query and if the query doesn’t return any values then I do not need to attach the entity and can just update and save it but if the linq query returns some entities then I need to attach my entity

if (recordUpdate)

{

orgContext.Attach(can);

}

orgContext.UpdateObject(can);

orgContext.SaveChanges();

 if (can.statecode == null)
                    {

                        orgContext.AddObject(can);
                    }
                    else
                    {
                       orgContext.Attach(can);
                    }

                    orgContext.UpdateObject(can);
                   orgContext.SaveChanges();

This has taken me a while to work this out.   The reason for Linq query returning or not returning results is I am setting an ID on a candidate based on surname, firstname and a counter.  I need to search to see if there are any ID’s with that value and if there are then add one to the counter but if this candidate is the first then create it, which is the reason why sometimes the linq queries returns values (and turns on tracking) and sometimes it doesn’t (new ID).

It would have probably helped if I had read this article by Microsoft

Use the Organization Service Context Class

CRM 2011 – Getting Started with LINQ in CRM 2011

I have been using LINQ with CRM 2011 and I have to say it’s awesome.  It’s a great way to code without having to write connectors to SQL databases.  I used to spend hours and days writing database code when I was a Java programmer, not to mention I am always forgetting how to write complex SQL statements joining tables etc.

LINQ is one of the areas that is well covered in the SDK, it has lots of examples and creating the dataContext is a piece of cake.  I went about learning LINQ a bit backwards because I got started with LINQ in CRM 2011 by just using the samples and then I have just finished reading a chapter on it for the 70-515 .NET 4 exam.

If you want to get started writing LINQ queries for CRM 2011, I found these pages from the SDK the most useful

Firstly you have the parts of a LINQ query

 

http://msdn.microsoft.com/en-us/library/ff681565.aspx

 

 

 

How to retrieve data using LINQ

http://msdn.microsoft.com/en-us/library/ff681573.aspx

then you have this page about the LINQ functions

 

 

An example on how to build a complex LINQ query

I would also recommend this page Use The Organisation Service Context Class, You need to know this because this creates the DataContext which LINQ uses to connect to the CRM database (which saves you writing all that troublesome code yourself)

finally to finish off you have the real winner, lots of LINQ Samples.  If you are going to click on one link then this is the one to go for because from this you will quickly understand enough to start writing your own LINQ queries.

SDK code Samples : queries

I found learning LINQ one of the easier parts of CRM 2011 and also one of the most beneficial.

These pages from the CRM 2011 – building LINQ queries

In This Section

Use LINQ to Construct a Query
Provides information about how to create LINQ queries. 

Use Late-Bound Entity Class with a LINQ Query
Learn how to use late-bound entity classes with LINQ. 

Order Results Using Entity Attributes with LINQ
Learn how to use Lookup attributes with LINQ. 

Filter Multiple Entities with LINQ
Learn how to work with multiple entities. 

Page Large Result Sets with LINQ
Learn how to use the Skip and Take operators to page query results. 

LINQ Query Examples
View LINQ sample queries. 

Sample: Create a LINQ Query
Learn how to create simple LINQ queries. 

Sample: Complex LINQ Queries
Learn how to create more complex LINQ queries. 

Sample: RetrieveMultiple With Condition Operators Using LINQ
Learn how to use retrieve multiple condition operators with LINQ. 

Sample: More LINQ Query Examples
Learn how to use Skip and TakeorderBy and complex where conditions. 

Sample: Use LINQ with Late Binding
Learn how to build LINQ queries that use late bound entities. 

 

 

CRM 2011 – OptionSetValue returned in LINQ only returns int value

I was using LINQ to retrieve multiple rows and I am loving that but I was doing a linq query to get the different address for an account.

This worked fine except I wanted to check the address type by name.  So I wanted to retrieve the Primary address.  I found out that in LINQ, optionsetvalues only return the int number value and not text/label.  To get the label you have to look it up using the metadata service.  ARGHGHGHG how frustrating.  I was searching OptionSetValue, metadata search and couldn’t find anything for ages until I saw that the samples has a couple of projects all about metadata calls.

You can find them here

it was annoying at the time because I had just figured out how to return the values in LINQ and I didn’t want to hard code the values to the int optionSet values because I thought it wouldn’t work if someone added in new address types but it wouldn’t still work if I used the address type string name.
Microsoft.Xrm.Sdk.Client.OrganizationServiceContext orgContext =                           new Microsoft.Xrm.Sdk.Client.OrganizationServiceContext(_service);
IEnumerable<CustomerAddress> addresses = from c in orgContext.CreateQuery<CustomerAddress>()                                                             join aa in orgContext.CreateQuery<Account>() on c.ParentId.Id equals aa.Id

where (c.ParentId.Id == new Guid(“92eb0d0e-b22e-e011-9645-00155d106b02”))

select c;

 

System.Console.WriteLine(“========================”);

foreach (CustomerAddress ca in addresses)                    {

System.Console.WriteLine(ca.AddressTypeCode + ” ”

+ ” ” + ca.CustomerAddressId
+ ” ” + ca.AddressTypeCode.Value

+ ” ” + ca.Line1

+ ” ” +  ca.Line2

+ ” ” + ca.Line3);

OptionSetValue opp = ca.AddressTypeCode;

System.Console.WriteLine(” ” + ca.CustomerAddressId.Value);

}

CRM 2011 – How to use guids in LINQ queries

This took me a bit of time to work out but in the end it’s very simple.  You can’t just reference a guid as a string, you have to create a guid type or cast it to be a guid.  Amusingly I had to select the guid to make find out what the guid was which totally defeats the object but I wanted to know what it was.

In the end it turned out my initially code didn’t work because I had missed a digit off, .NET to be fair did tell me my guid need to be 32 digits.

Create a guid

Guid myGuid = new Guid(“92eb0d0e-b22e-e011-9645-00155d106b02”);                             IEnumerable<Account> accounts = from a in orgContext.CreateQuery<Account>()

where (a.Id == myGuid)

{

Name = a.Name,

Address1_County = a.Address1_County,

Id = a.Id

};

Casting the String to a guid

IEnumerable<Account> accounts = from a in orgContext.CreateQuery<Account>()

where (a.Id == new Guid(“92eb0d0e-b22e-e011-9645-00155d106b02”))

{

Name = a.Name,

Address1_County = a.Address1_County,

Id = a.Id

};

CRM 2011 – Early bound LINQ queries

I have been trying to get my head around LINK and OData today.  OData was really giving me the run around, trying to bring the sample into a piece of Javascript was very frustrating.

I am a big fan of Early bound data in CRM 2011, I just like to know what I am using rather than the dynamic late binding, strong typing means you catch any problems in the code when compiling it and saves you a bunch of time debugging misspelt entites in strings.

I couldn’t figure out how to get the results of a Linq query in a early bound entity.  In link you often just use the Var variable and then LINQ converts the results into the correct type.

The SDK also didn’t seem to have any examples but I had been reading about LINQ for the 70-515 .NET web apps exam.  I knew the result had to be IEnumerable so you can go through the results.

The quick answer is you have use this

IEnumerable<Account>

instead of a

Var

here is a snippet of my code.  if anyone can tell me the easiest way to display code in WordPress that would be appreciate, please leave me some advice in the comments.  This blog post has some good information on LINQ queries

_service = (IOrganizationService)_serviceProxy;

OrganizationServiceContext orgContext =  new OrganizationServiceContext(_service);

IEnumerable<Account> accounts = from a in orgContext.CreateQuery<Account>()

select new Account

{

Name = a.Name,
Address1_County = a.Address1_County
};

System.Console.WriteLine("List all accounts in CRM");                    
System.Console.WriteLine("========================");                   
 foreach (Account a in accounts) {                        
System.Console.WriteLine(a.Name + " " + a.Address1_County);                    
}                    

System.Console.WriteLine();                    
System.Console.WriteLine("<End of Listing>");                    
System.Console.WriteLine();                    
System.Console.WriteLine();