Hi,
We have a jtable which have some columns whose value is calculated based on some conditions.
For example-
var documents = await documentsQuery
.OrderBy(input.Sorting)
.PageBy(input)
.Select(x => new DocumentListDto
{
Id = x.Id,
Title = x.Title,
File_Size_Presenter = x.File_Size + " KB",
Document_Approval_Type = x.Entity_Document_Approval_Types.Approval_Type,
Document_Approval_Type_ID_FK = x.Document_Approval_Type_ID_FK,
CreationTime = x.CreationTime,
Approvers = x.Entity_Approvers.Count(),
Status = x.Is_Publish ? "Published" :
x.Document_Approval_Type_ID_FK == (int)Entity_Document_Approval_Types.DEFAULT_TYPES.Sequential ?
(x.Entity_Approvers.Count(a => a.Approver_Type_ID_FK == (int)Entity_Approvers_Types.DEFAULT_TYPES
.Basic) == x.Entity_Approvers.Count(a => a.Is_Approved == true && a.Approver_Type_ID_FK == (int)Entity_Approvers_Types.DEFAULT_TYPES
.Basic) ? "Approved" : "In progress") // Sequential
:
(x.Approvers_Required <= (x.Entity_Approvers.Count(a => a.Is_Approved == true && a.Approver_Type_ID_FK == (int)Entity_Approvers_Types.DEFAULT_TYPES
.Basic)) ? "Approved" : "In progress"), // Numbered
Status2 = x.Is_Publish ? "Document has been published" :
x.Document_Approval_Type_ID_FK == (int)Entity_Document_Approval_Types.DEFAULT_TYPES.Sequential ?
(x.Entity_Approvers.Count(a => a.Approver_Type_ID_FK == (int)Entity_Approvers_Types.DEFAULT_TYPES
.Basic) == x.Entity_Approvers.Count(a => a.Is_Approved == true && a.Approver_Type_ID_FK == (int)Entity_Approvers_Types.DEFAULT_TYPES
.Basic) ?
x.Entity_Approvers.FirstOrDefault(a => a.Approver_Type_ID_FK == (int)Entity_Approvers_Types.DEFAULT_TYPES
.Original_Writer).Is_Approved != true ? "Waiting on original writer" : "Waiting final publishing"
: "Waiting on approvers") // Sequential
:
(x.Approvers_Required <= (x.Entity_Approvers.Count(a => a.Is_Approved == true && a.Approver_Type_ID_FK == (int)Entity_Approvers_Types.DEFAULT_TYPES
.Basic)) ? x.Entity_Approvers.FirstOrDefault(a => a.Approver_Type_ID_FK == (int)Entity_Approvers_Types.DEFAULT_TYPES
.Original_Writer).Is_Approved != true ? "Waiting on original writer" : "Waiting final publishing" : "Waiting on approvers"), // Numbered
Author = x.AbpUser_OriginalWriterUser.Name + " " + x.AbpUser_OriginalWriterUser.Surname,
DocumentAdmin = x.AbpUser_DocumentAdminUser.Name + " " + x.AbpUser_DocumentAdminUser.Surname
})
.ToListAsync();
Here, columns like Status, Status2, Author, DocumentAdmin does not exists in database. Applying filter on them would mean to first fetch all data and then filter it, which does not seem like any efficient way. We are also using paging and sorting and we cannot lose this functionality. (Sorting is not working on calculated columns. So, as a bonus, if you have a solution to that, it'll be much appretiated :) )
I believe many of you, like me, would have banged your head on wall to do this correctly. So, how could this be handled in an elegant way?
Thanks!
5 Answer(s)
-
0
Hi,
Why don't you try to add whereIf in your query.
private void GetMyList(bool? isPublished) { var documents = await documentsQuery .WhereIf(isPublished.HasValue, x=> x.Is_Publish = isPublished.Value) .OrderBy(input.Sorting) .PageBy(input) .Select(x => new DocumentListDto { Id = x.Id, Title = x.Title, File_Size_Presenter = x.File_Size + " KB", Document_Approval_Type = x.Entity_Document_Approval_Types.Approval_Type, Document_Approval_Type_ID_FK = x.Document_Approval_Type_ID_FK, CreationTime = x.CreationTime, Approvers = x.Entity_Approvers.Count(), Status = x.Is_Publish ? "Published" : "Pending", Status2 = x.Is_Publish ? "Document has been published" : "Pending Approval" Author = x.AbpUser_OriginalWriterUser.Name + " " + x.AbpUser_OriginalWriterUser.Surname, DocumentAdmin = x.AbpUser_DocumentAdminUser.Name + " " + x.AbpUser_DocumentAdminUser.Surname }) .ToListAsync(); } -
0
Hmm.. for simplicity sake I did not post entire conditions.
Please check my updated question. I have included full condition there.
Your solution will not work when user wants to search for a status by text. Any other ideas? Some thoughts I have -
- Creating a View using above query and then querying that for filters.
- Creating a Table-Valued function using above query and then querying that for filters.
But these will require queries/functions to be stored on SQL Server which I do not like. Any other way to use do this purely using C# ?
Thanks!
-
0
Hi @smartlayer,
I'm not sure if it is possible to write a LinQ query to satisfy those conditions without fetching all data from DB. If you can't manage to do that, best option is to create a view I guess.
-
0
I found a beatuiful way to do this.. So I thought to share it here as well.
There is lilbrary called Delegate Decomplier..
On high level, we create readonly properties with NotMapped and Computed(provided by DelegateDecompiler) attributes and add all logic in those properties.
When we write query, we only call extra method ".DecompileAsync()" and then ".ToListAsync()", it will automatically modifies the query that can be understood by SQL.
If you want to learn more about it, must read: https://daveaglick.com/posts/computed-properties-and-entity-framework
-
0
@smartlayer great :)
