Select Maximum number from a numeric field in an entity (Dynamics CRM 2016)

Question

What is the best way to retrieve the maximum value from a numeric field in an entity? Something like this in SQL Server: Select MAX(NumbericFieldName) From TableName.

I tried this:

var documentno = XrmContext.CreateQuery("nychro_traportaldocumentupload").Max(c => c.GetAttributeValue<Int32?>("nychro_portaldocumentreviewid"));

But I get the error "MAX is not supported"

What is the best way to resolve this?

While the FetchXML query is technically the correct answer, by using Order By, the LINQ query circumvents FetchXML's out-of-box limitations on Aggregates. You could also do a similar non-aggregate query in FetchXML leveraging order by , with top="1". — Aron, Jul 20 '17 at 21:03

score 2 · Answer 1 · answered Jul 20 '17 at 20:53

2

The following linq code, should be able to accomplish your requirement:

var documentno = (for a in XrmContext.CreateQuery("nychro_traportaldocumentupload")
                 orderby a.nychro_portaldocumentreviewid descending
                 select a).FirstOrDefault()

answered Jul 20 '17 at 20:53

Hackerman

11,746
2
31
41

Just to clarify, does this bring all `nychro_traportaldocumentupload` recods into memory and then perform a `FirstOrDefault` or does this execute as a single statement on the server? – jasonscript Jul 26 '17 at 01:31

score 0 · Accepted Answer · edited Jul 21 '17 at 12:16

0

You have to use fetchxml query & do a FetchExpression to fetch the result.

<fetch distinct='false' mapping='logical' aggregate='true'> 
    <entity name='nychro_traportaldocumentupload'> 
       <attribute name='nychro_portaldocumentreviewid' alias='nychro_portaldocumentreviewid_max' aggregate='max' /> 
    </entity> 
</fetch>

edited Jul 21 '17 at 12:16

jcjr

1,495
20
37

answered Jul 20 '17 at 20:46

Arun Vinoth

20,360
14
48
135

Well, I think this would be the right answer, however I'd like to know how to execute this fetch in the right way from the AdxSutioPortal Code actually. – Poyson1 Jul 20 '17 at 22:16
Look in this: https://community.adxstudio.com/products/adxstudio-portals/documentation/developers-guide/web-controls/crmdatasource/ – Arun Vinoth Jul 20 '17 at 23:26
For sure I'll mark it as good answer, however I haven't been able to retrieve the single expected value from inside adxstudioportal code. Outside Portalcode is quite simple, not the same inside. – Poyson1 Jul 21 '17 at 13:26

Select Maximum number from a numeric field in an entity (Dynamics CRM 2016)

2 Answers2