I am new to Linq and trying to query an entity to get the max() value.
The pdmDocDrawingNumbers are 9999999, 524244222235, 1 and some others.
Only maxvalue0 has the correct value 524244222235.
maxvalue1 to maxvalue3 has the wrong result 9999999.
What is wrong with the query for maxvalue1 to maxvalue3?
protected override void OnButtonClicked(IClickContext context)
{
IEntityContext entityContext = context.EntityContext;
ulong maxvalue0 = entityContext.Documents()
.Where(d => d.IsA("/Document/pdmDocType2DCADFile/"))
.Select(d => d.Get<ulong>("/Document/pdmDocType2DCADFile/pdmDocDrawingNumber"))
.ToList()
.Max();
ulong maxvalue1 = entityContext.Documents()
.Where(d => d.IsA("/Document/pdmDocType2DCADFile/"))
.Select(d => d.Get<ulong>("/Document/pdmDocType2DCADFile/pdmDocDrawingNumber"))
.Max();
ulong maxvalue2 = (from document in entityContext.Documents()
where document.IsA("/Document/pdmDocType2DCADFile/")
select document.Get<ulong>("/Document/pdmDocType2DCADFile/pdmDocDrawingNumber")).Max();
ulong maxvalue3 = entityContext.Documents()
.Where(d => d.IsA("/Document/pdmDocType2DCADFile/"))
.Select(d => d)
.Max(d => d.Get<ulong>("/Document/pdmDocType2DCADFile/pdmDocDrawingNumber"));
this.Dialogs.Information("Max Drawing0 Number: " + maxvalue0.ToString());
this.Dialogs.Information("Max Drawing1 Number: " + maxvalue1.ToString());
this.Dialogs.Information("Max Drawing2 Number: " + maxvalue2.ToString());
this.Dialogs.Information("Max Drawing3 Number: " + maxvalue3.ToString());
}
The entities Documents stored in the PDM System ProFile (https://documenter.getpostman.com/view/4153732/RWTrLvrZ?version=latest)
Thanks for your help!
Edit1: I have now tried to narrow down the problem further.
ulong maxvalue1 = ((IEnumerable<ulong>)(entityContext.CreateQuery<IDocument>().Where(d => d.IsA("/Document/pdmDocType2DCADFile/"))
.Select(d => d.Get<ulong>("/Document/pdmDocType2DCADFile/pdmDocDrawingNumber"))))
.Max();
Gives the correct result 524244222235
ulong maxvalue2 = ((IQueryable<ulong>)(entityContext.CreateQuery<IDocument>().Where(d => d.IsA("/Document/pdmDocType2DCADFile/"))
.Select(d => d.Get<ulong>("/Document/pdmDocType2DCADFile/pdmDocDrawingNumber"))))
.Max();
Gives the wrong result 9999999
So it looks like the difference is between IQueryable.Max() and IEnumerable.Max().
Maybe I am also using IQueryable.Max() in a wrong way.
Edit2: The request is converted from an appserver into a sql request to a mssql server. The appserver is closed source so I can't analyse what exactly happens. However, the api has a function with which I can issue the sql query for certain statements.
This request:
entityContext.CreateQuery<IDocument>().Where(d => d.IsA("/Document/pdmDocType2DCADFile/"))
.Select(d => d.Get<ulong>("/Document/pdmDocType2DCADFile/pdmDocDrawingNumber"));
is converted by the app server into the following request:
SQL DrawingO Number: SELECT [eav].*
FROM (
SELECT
(tO).[1.#ID],
[tO].[1.4 TYPE],
[tO).[1.4c1]
FROM (
SELECT
(1.#1D] = [1.#.d].[DO_IDNR],
[1.4STATUS] = [1.#.stat_3].[SP_KENN],
[1.4#TYPE] = CAST (3 AS TINYINT),
(1.#VC] = [1.4.d].[DO_VERSIONCOUNT],
(1.4VI] = (1.4.d].[DO_VERSIONINDEX],
[1.41] = [1.4.dv_11).[DV_VSTL1]
FROM [dbo].[DOKSTAMN] AS [1.#.d]
INNER JOIN [dbo].[DOKVAR] AS [1.#.dv_11] ON (((1.4.d].[DO_DTNR] = 11) AND
((1.4.d].[DO_IDNR] = [1.4.dv_11].[.DV_IDNR]))
INNER JOIN [dbo].[SPERREN] AS [1.#.stat_3] ON ((10 =[1.#.stat_3].[SP_ART]) AND ((3=
[1.#.stat_3].[SP_OBJTYP]) AND ([1.#.d].[DO_IDNR] = [1.#.stat_3].[SP_OBJIDNR])))
WHERE ((1.4.d].[DO_DTNR] = 11)
) AS [tO]
CROSS APPLY [dbo].[PRO_TVF_AuthorizeProjectRole}((t0].[1.4ID], [tO].[1.4TYPE],
[tO].[1.4STATUS], 2) AS [1.#.auth_p]
WHERE (([tO].[1.#V1] = [tO].[1.4VC]) AND ([1.4.auth_p].[P01] = 1))
) AS[t1]
CROSS APPLY (
VALUES (([t1].[1.#ID], [t1].[1.4#7TYPE], ‘dv_11.DV_VSTL1;, CAST ([t1].[1.4c1] AS SQL_VARIANT),
NULL)
) AS [eav]([ID], [TYPE], [KEY], [VALUE], [CLOB])
ORDER BY
[t1].[1.4TYPE] ASC ,
(t1].[1.41D] ASC
Edit3: Thank you! It was the data type in the database. The field:
"/Document/pdmDocType2DCADFile/pdmDocDrawingNumber"
is a string in the database. I have created a new field:
"/Document/pdmDocType2DCADFile/pdmDocDrawingBaseNumber"
with the data type number. With this field the function:
int maxvalue2 = entityContext.CreateQuery<IDocument>()
.Where(d => d.IsA("/Document/pdmDocType2DCADFile/"))
.Select(d=>d.Get<int("/Document/pdmDocType2DCADFile/pdmDocDrawingBaseNumber"))
.Max();
returns the correct result, like the variant that pulls all values from the database into a list and then evaluates them locally.
I guess using this version with pulling all strings from the database isn't good, because the datatype of the database isn't visible:
maxvalue0 = entityContext.CreateQuery<IDocument>()
.Where(d => d.IsA("/Document/pdmDocType2DCADFile/"))
.Select(d => d.Get<int>"/Document/pdmDocType2DCADFile/pdmDocDrawingNumber"))
.ToList()
.Max();
i suppose this would be better for clarity:
maxvalue1 = entityContext.CreateQuery<IDocument>()
.Where(d => d.IsA("/Document/pdmDocType2DCADFile/"))
.Select(d => int.Parse(d.Get<string>("/Document/pdmDocType2DCADFile/pdmDocDrawingNumber")))
.ToList()
.Max();
However, I think the sql server is faster at searching data. So I think a variant where the sql server does the conversion (if a string field is used) including the max() function would be better. Unfortunately, I have not yet found a way to implement this. Does anyone of you have any ideas?