Linq to CRM early bound queries

I first discovered linq to crm and early bound classes way back in 2011. The sky (well the office ceiling) literally dawned on me when i discovered it. Along the way, i’ve learned its merits, pitfalls, nuances. Until this day, it’s still the query of choice on the server side, over query expression and fetchxml. But this post is not about pros/cons analysis. There are plenty out there. There’s a saying – “you learn something new every day” and here’s what i’ve learned today. It’s about join queries and returned objects.

In early bound generated classes, crmsvcutil generates two things in regards to N:1 relationship: an entityreference field and a class variable of type of the N:1. I’ve used the latter in lazy loading features before (when i’m feeling lazy coding) in portal development (because it’s only available in microsoft.xrm.client and not microsoft.xrm.sdk.client, which is available in plugin) Today, i found that i can actually populate it with selected fields in a join select query:

var invoicedata = (from inv in ctx.InvoiceSet
join invdetail in ctx.InvoiceDetailSet on inv.InvoiceId equals invdetail.InvoiceId.Id
join p in ctx.ProductSet on invdetail.ProductId.Id equals p.ProductId
where inv.InvoiceId == invid
select new InvoiceDetail()
{
invoice_details = new Invoice()
{
InvoiceId = inv.InvoiceId,
Name = inv.Name,
InvoiceNumber = inv.InvoiceNumber
},
product_invoice_details = p
}).ToList();


So as you can see, at the end of the query, in addition to the root level invoice detail data, the select actually instantiates the N:1 invoice, and product inside it. pretty sweet huh?

Web API 2 datetime object

Yes… datetime field type is complex. It’s one of the most notorious data type. It’s a developer’s hate-hate relationship (ie. there’s no love any time.. ever..) from crm timezone dependent date time field to integration.

Today i face with this one – i put up a web api that reads from database that serves data to a crm workflow assembly. so technology wise, server is running latest web api, and client is workflow assembly. long story short, trouble comes in serialization/deserialization. Normally, there’s no problem, unless ….

you have a date time field. so the webapi get returns a list of poco, with one field being datetime type. The string on the receiving end looks great, but DataContractJsonSerializer says it wants this ugly format: “\/Date(1496445804940-0400)\/”.

restriction is below:

1. unless you get into ilmerge, sandbox workflow assembly cannot use newtonsoft json.net. So you’re stuck with DataContractJsonSerializer

After struggling for half a day, i found a solution. apparently, webapi2 serializes poco automatically for you to json before send it over the wire. Nice. Now, how. the latest of course! newton json.net library. Hence the nice format. However, DataContractJsonSerializer doesn’t know how to deserialize it.. so the solution –

get webapi 2 to use the old date time format:

GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.DateFormatHandling = Newtonsoft.Json.DateFormatHandling.MicrosoftDateFormat;

CRM 2015 BPF back button

why you’re interested in that button you ask… well when the record is deactivated, the navigation buttons are still there, and they’ll spin and spin and do nothing. clients hate that, we have to fix that, and on to our next unsupported code to show the user what not to do since we can’t completely hide it.

var stageBackActionContainer = $(“div#stageBackActionContainer”);
if (stageBackActionContainer != null) {
stageBackActionContainer.unbind(“click”);
stageBackActionContainer.bind(“click”,
function () { alert(“user: if you read your instruction manual, you’ll know that this is not a valid action.”); }

This hack is upping the ugliness level, because it’s unbinding the ootb click (which fails anyway) and bind our own to it. It’s not as pretty as just piggypacking on whatever was there, for example adding your own event handler to whatever’s already there.

crm 2015 subgrid event

if you’re one of the unfortunate soul (on premise, and scms) who’s stuck on CRM 2015 update 0.2 and is screaming for the goodies offered online with their latest update 1.0, then subgrid event is probably one of the most sought after functionality.

Our Scenario today:

Trigger form rollup on crud change of grid. One of the most common requirement, but yet, no supported way of doing it. But here’s the unsupported way. It works on crm 2015 update 0.2.

First, get the crm mvvc grid view control. They call it the gridcontrollite. Whatever that means.

var grid = document.getElementById(“VerificationList”).control;

add refresh event handler:

grid.add_onRefresh(function () {alert(“asdf”)});

Another goodies out of this tip is that, the grid is refreshed by other page level async events as well, such as active/inactive. So if you want to tap into that kind of events (for us, we’re trying to disable back/forward button of bpf on deactivation), you can use that too.

bpf field onchange

Let’s start by saying that this is not supported in crm.

field on change is usually defined in the form designer. Since bpf designer doesn’t offer ways to change property of its fields, the only way is to add change through the dom.

In the case of optionset control, it’s made up of label and select control (show label in display node, show select control and hide label in edit mode).

To start, we get the control, which is part of xrm:

var ctrl = xrm.page.getControl(“name”)

Then, we go get the view:
var v=  ctrl.get_editView()

In the case of optionset control, the edit element is a select control:

var select = v.get_editElement()

change handler is inserted via jquery:

select.change(function() {alert(“select changed”);});

modifiedby and on

does your workflow carry this undesired effect of updating the modified by/on attributes? Most of the posts i have found deal with it by creating a custom modified on/by attributes and have a preupdate plugin to populate that field. That was our initial thought and then run into the issue of how to filter out preupdate event from that workflow. Well in v4, there used to be a calleorigin from the plugin context which you can use. Even that only tells you if it comes from application, asyncoperation. It doesn’t really tell you exactly from which workflow is the update is originated from.

Here’s how we did it and its based on the fact that you can prevent an update request from updating any attributes by removing that attributes from the target parameter.

1) create a custom bool field which the workflow instance will set to true. Call it “LeaveModifiedAttributesAlone”

2) Create a plugin to trigger on that field only, at a pre-update stage.

3) In the plugin code, remove the modified on/by attributes and also the LeaveModifiedAttributesAlone so it will remain null

4) Change your workflow you want to filter to, in addition to the regular operation, set the bool to true, so the plugin will trigger.

That’s it… its the most elegant solution i can come up with..

Dynamics UR6

Microsoft must be hard at work. After the much liked update UR5, UR6 containing multiple fixes have just been released. 

http://bingsoft.wordpress.com/2012/01/12/dynamics-crm-2011-rollup-6-now-available/

CRM Plugin configuration

There are quite a few ways to introduce configuration data into plugins:

1) unsecure/secure configuration

2) configuration entity

3) webservice.

There are pros and cons to each one of them, which one have you been using?

EF:2 L2S:0

Another great thing about EF is the way m:m relationship is handled. It will recognize the bridge table between the two tables and will generate the m:m link in the EDMX model accordingly. This makes inserting a much simpler task and the code required a lot easier to read:

Tbl1 tbl1 = new Tbl1();
// populates the fields of tbl1
Tbl2 tbl2 = new Tbl2();
// populates the fields of tbl2

// associate the two tables
tbl1.Tbl2 = tbl2

ctx.Tbl1.AddObject(tbl1);
ctx.SaveChanges();

That’s it! No bridge table Ma!

More posts coming up on how to update and delete (equally easy, i promise!)

EF4: 1, L2S: 0

LINQ brings so much value to the table when it’s querying data. But when it comes time to doing the other 3 CRUD operations, things start to tangle up a bit. As simple as delete by PK, say a GUID, it have become cumbersome, and sometimes counter-intuitive.

The most intuitive way:

1) Query first (by PK)
2) Delete
3) ChangeOnSubmit

The less intuitive way (disconnected manner, ie. you know the PK you want to delete):

1) instantiate an object with only the PK value populated (the rest of the fields can be blank)
2) attach
3) DeleteOnSubmit

Adding to the fine subtlety, that only works in EF because it generates the following simple SQL:

delete tbl where pk_tbl = ‘@1’

In Linq to SQL, it generates:

delete tbl where pk_tbl = ‘@1’ and field1_tbl = ” and field2_tbl = ”…

So even if the rest of the fields are uninitialized with a value, they are included in the conditions… and of course, no deletion will take place. In fact it will give you an error saying that.

I’m leaning more and more to EF..