How to sort parent table data based on their reference count in child table using LINQ c#?-CodePudding

I want to sort parent table by their reference count(a foreign key) in another child table. Suppose

The parentTbl

Id
Name

ChildTbl

Id
parentId
detials

I want to select only the parentTbl columns sorted by their reference count in ChildTbl by joining them like bellow:

var parents = from p in context.parentTbl
          join c in context.ChildTbl on p.Id equals c.parentId
          orderby count(c.parentId)
          select distinct p;

I think I also need to group them before ordering but I am not sure how to achieve this.

Note: There is no navigational parent child property. Just a foreign key in the child table(that I mentioned) and I want to count by that foreign key column

CodePudding user response：

Please Check this out.

// Query the parentTbl table and join it to the childTbl table
var query = from p in parentTbl
            join c in (
               from c2 in childTbl
               group c2 by c2.parentId into g
               select new { parentId = g.Key, referenceCount = g.Count() }
            ) on p.Id equals c.parentId
            orderby c.referenceCount descending
            select p;

// Execute the query and print the sorted records
foreach (var p in query)
{
   Console.WriteLine($"{p.Name}: {p.ReferenceCount} references");
}

and the SQL version

 SELECT p.*
FROM parentTbl p
JOIN (
   SELECT parentId, COUNT(*) AS referenceCount
   FROM childTbl
   GROUP BY parentId
) AS c ON p.Id = c.parentId
ORDER BY c.referenceCount DESC;

CodePudding user response：

Does this helps ?

// sort parents by number of children using linq
   var sortedParents = from p in parents
                       join c in children on p.id equals c.parentId into pc
                       orderby pc.Count() descending
                       select new { parent = p, children = pc };