CODEWITHSUNDEEP
c#linq
Andre Rubnikowich
Andre Rubnikowich

Reputation: 475

Linq - Getting count from unrelated table

How to get to execute a select count(*) from UnrelatedTable where documentLink_Id = x.documentLink_id and add the resulted count to totalTimeAccessed. Both table (PublishedContent and UnrelatedTable have the documentLink_id) in common. Then I would like to select only the rows that have a count greater then 0

        var dbData = publishedontext.PublishedContent
                    .Where(x => x.Status > PublishedStatus.Released)
                        .OrderBy(x => x.Title)
                    .Select(x => new DownloadsPerContetnItemReportRows()
                    {
                        title  = x.Title,
                        documentLink_Id = x.DocumentLink_Id,
                        statustatus = x.Status,
                        totalTimeAccessed = "Select count from an unrelated table"
                     }

Upvotes: 0

Views: 53

Answers (1)

Mrinal Kamboj
Mrinal Kamboj

Reputation: 11480

Following code shall work:

totalTimeAccessed = PublishedContent
                    .Join(UnrelatedTable,pc => pc.DocumentLink_Id,
urt => urt.DocumentLink_Id,(pc,urt) => new {pc,urt})
                    .Where(y => pc.Count > 0 && urt.Count > 0)

How it Works

  • Joined PublishedContent with UnrelatedTable on DocumentLink_Id
  • On the result checked whether Count > 0 for both PublishedContent and UnrelatedTable projections

Would need few modifications based on your specific requirements. In case Count refers to the number of rows not a Column as projected, then Join will anyway give the matching rows, then we don't need the Where clause

Upvotes: 1

Related Questions