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);
}