0

I have two independent tables, "Task" and "Report" with a common column "CustomerId".

I wish to group by these two tables by CustomerId  and combine their results into a new list. 

If some task or report does not have a matching customerId, then that task/report should have an independent group with their own customerId as key and count 1 as shown below in the required result. 

How can I achieve this in LINQ and efcore? 

Table1: Task

TaskId Description CustomerId
1 Task1 1
2 Task2 1
3 Task3 2
4 Task4 7

Table2: Report

ReportId Summary CustomerId
1 Report1 1
2 Report2 2
3 Report3 3

Want to get this result ⬇️

Required Result

Group 1:   CustomerId: 1   Count(3)

TaskId 1

TaskId 2 

ReportId 1

--------------------------------------

Group 2:   CustomerId: 2   Count(2)

TaskId 3

ReportId 2

------------------------------------------

Group 3:   CustomerId: 3   Count(1)

ReportId 3

------------------------------------------

Group 4:   CustomerId: 7   Count(1)

TaskId 4

I can join two tables and group by them but then only get matched records, I want to get all records, I have not come across a solution as yet and I would truly appreciate any help. --Update--

public class GroupedItems<T> where T : class
{
    public string GroupName { set; get; }
    public int GroupCount { get; set; }
    public IEnumerable<T> Items { set; get; }
}

I wish to get

GroupedItems<ReportTaskDTO>

where if group name is CustomerId 1 then Items should have two task and one report and group count should be 3.

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
Maryam Afzal
  • 1
  • 1
  • [LEFT JOIN](https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators#left-join) + [GROUP BY](https://stackoverflow.com/a/7325306/10646316). Anyway, show your model and what you have tried. – Svyatoslav Danyliv Feb 15 '23 at 13:18
  • Show your code. Query is simple and we do not understand complexity. – Svyatoslav Danyliv Feb 15 '23 at 14:01
  • @Svyatoslav If you look more carefully, the query is not simple (and not even doable) in EFC. Client side, yes. Server side - no way currently (out of the box). – Ivan Stoev Feb 15 '23 at 14:38
  • @MaryamAfzal, update question with result class. From fiddle it is not clear. – Svyatoslav Danyliv Feb 15 '23 at 15:11
  • I deleted the fiddle as it was not clear. I do need it in entity framework AsQuerable. @SvyatoslavDanyliv. I will update the question with result class. – Maryam Afzal Feb 15 '23 at 18:40
  • @SvyatoslavDanyliv, Thank you for your answer, I updated the question, I simplified the models but in real I have a lot of extra fields. And for ReportTaskDto I am not sure how I should set it. I need suggestions for that, so that I could get report and task as a separate item in List of Items. for example for group3 there will be only one report item and null task. – Maryam Afzal Feb 16 '23 at 09:11
  • `IEnumerable Items` tells me that you have to group items on the client side. Add to the question `Task` and `Report` model. – Svyatoslav Danyliv Feb 16 '23 at 09:22
  • No, I have to group on server side, as an example **` await combinedQuery.OrderByDescending(x => x.CustomerId) .GroupBy(d => d.CustomerId, i => i, (key, v) => new GroupedItems { GroupName = key.ToString(), GroupCount = v.Count(), Items = v.Select(x => new ReportTaskDto { }) });`** – Maryam Afzal Feb 16 '23 at 09:36

0 Answers0