Fetch null field attribute in CRM 365

(0) Share

Report

Posted on by Community Member

Hi,

I have list account entity. I want to order them based on custom field defined for account entity which is defined as optional that means it can have null values. I am using late bound entity for some purpose. If I try to sort them based on custom field it give "key not found" exception.
Since that field may have some value in other account entity, I want to sort them based on ascending order.
Something similar below

var result = accountEntitites.OrderBy(p => p.customField == null)
                 .ThenBy(p => p.customField);

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

ashlega 34,477 on at

Like (0)

Report
Hi Sachin,

could you post your actual code here? I don't think customField is how the attribute called in your CRM organization?

Update: Sorry, disregard.. I see it was just an explanation of what you need.. Try this maybe?

var result = accountEntitites.OrderBy(p => !p.contains("customField"))

.ThenBy(p => !p.contains("customField") ? null : (type)p["customField"]);

Was this reply helpful? Yes No
Suggested answer

shivaram 3,315 on at

Like (0)

Report

Hi Sachin,

while retrieving records only, you can write a condition.

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

OrganizationServiceContext orgServiceContext = new OrganizationServiceContext(service);

var accountRecords = context.CreateQuery("account").Where(a => a.GetAttributeValue<string>("fax") != null).OrderBy(a => a.GetAttributeValue<string>("fax")).ToList();

Then you will get only records which having fax value (in ACcount Entity fax also optional field).

You can try in this way also.

Hope it helps you.

Best regards,

Shivaram.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Alex,

Thanks for your reply. Your code seem to be working as expected.

However I would like to extend my question here.

If sorting is on one field, then your solution is working. But I would like to sort on more than one field.

I am fetching list of field and sorting order through an configuration entity.

In current scenario, I am sorting account entity based on fields received by above configuration entity.

Something like below pseudo code,

foreach (var duplicateParameter in

duplicateParameterResponse.Entities)

{

///

var sortedResult = accountEntites.Orderby(a=> (string)duplicateParameter["spl_field"]);

/// In above line, I want commutative sorting like orderby followed by thenby based on duplicateParameterResponse.Entities count

}

Was this reply helpful? Yes No
Suggested answer

ashlega 34,477 on at

Like (0)

Report

Hi Sachin,

I think this might work for you:

1. Instead of doing that sorting when querying the data, get the data first and use ToList() to get a list. You are still going to have to retrieve all the data, so it does not matter that much when you sort (while getting the data or after, once you have a list)

2. Sort the list - you can use all kinds of conditions, and you can use functions(unlike in the Xrm linq query):

parts.Sort(delegate(Entity x, Entity y)

{

Use C# to compare x to y

});

msdn.microsoft.com/.../b0zbh7b6(v=vs.110).aspx

Was this reply helpful? Yes No
Suggested answer

Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

You don't need to Order By the null values and the by the other values.

When you order by the field in ascending order, the null/empty values will appear first.

var result = accountEntitites.OrderBy(p => p.customField);

Was this reply helpful? Yes No
ashlega 34,477 on at

Like (0)

Report

It seems that Sachin is trying to sort by multiple fields at the same time. That's why I was suggesting to do that sorting on the client side - the way I understand it, if there are a few attributes with null values, the idea is to have records where all attributes are null on top, then some other records, etc. Not sure if it's possible with Fetch/Linq, but it should not be a problem with C# on the client side.

Was this reply helpful? Yes No
Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

Hi Alex,

I saw his original question, which has the same field name. For some reason I didn't see the replies when I answered :)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Apologies, If i didn't make it clear. I agree to your point no 1, I am retrieving all account entity prior to sort.

More explanation about issue.

duplicateParameterResponse.Entities[0]["field"] = "name";

duplicateParameterResponse.Entities[0]["order"] = "asc"

duplicateParameterResponse.Entities[1]["field"] = "address1_city";

duplicateParameterResponse.Entities[1]["order"] = "asc"

duplicateParameterResponse.Entities[2]["field"] = "membernumber"

duplicateParameterResponse.Entities[2]["order"] = "desc"

"name", "address1_city" and "membernumber" are nullable data type fields defined in Account, Contact and Lead entity. I want to make it generic solution hence using late bound entity rather than early bound entity.

For current scenario consider Account entity only.

Now what I want to achieve is,

Foreach "field" and "order" defined in duplicateParameterResponse.Entities

For first iteration,

var someExpression = accountEntities.OrderBy(p => !p.Contains((string) duplicateParameter[0]["field"])).

ThenBy(p => !p.Contains((string) duplicateParameter[0]["field"])

? null : (string) duplicateParameter[0]["field"]);

For subsequent iteration,

someExpression = someExpression.ThenBy(p => !p.Contains((string) duplicateParameter[index]["field"])).

ThenBy(p => !p.Contains((string) duplicateParameter[index]["field"])

? null : (string) duplicateParameter[index]["field"])

Expression generated through Foreach loop should be executed only once which will give sorted list of account entities.

I am not able to generate expression which will have sorting on multiple fields.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

I have figure it out solution to my problem. Solution is in two part.

Part 1: Add missing field attribute with null value in account Attribute collection.

foreach (var duplicateParameter in duplicateParameterResponse.Entities)
{
if (!accountEntity.Contains([string]duplicateParameter("field")))
{
accountEntity.Attributes.Add(new KeyValuePair<string, object>([string]duplicateParameter("field"), null));
}
}

it should be done for all account entity for which sorting has to be carried out.
Above code will make sure that I will have all attribute on which sorting will be applied.

Part 2: Use Generic sorting with linq and lambda expression

Using linq and lambda expression I was able to sort on multiple properties, and expression is getting evaluated only once. It does take of properties with value.

PS: Thanks guys for replying on my post and suggesting few option to get is solved.

Was this reply helpful? Yes No