Wikipedia:Reference desk/Archives/Computing/2020 November 14

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

November 14

Count only rows linking to another table in Linq to SQL

I ran to a problem today at work. There is a Linq to SQL query in a C# class similar to this:

from rowA in dc.TableA select rowA.Id, (from rowB in dc.TableB where rowB.IdA == rowA.Id select rowB.IdA).Count()

The intent of this query is to select all rows in TableA with their ID numbers and the number of rows in TableB linking to those rows. This query works all OK, but I found out that what is actually needed is not the count of all rows in TableB linking to the specific rows in TableA, but instead the count of all rows in TableB linking to both the specific rows in TableA and to at least some row in a third table, let's call it TableC. The contents of the rows in TableC don't matter, it's enough that they exist. In other words, I want to add a where exists condition. How can I do that in Linq to SQL? JIP | Talk 00:16, 14 November 2020 (UTC)

You might try adding one of the following conditions to the subselect:

	&& dc.TableC.Any(rowC => rowC.IdB = rowB.Id)
	&& (from rowC in dc.TableC where rowC.IdB = rowB.Id).Any()
	&& (from rowC in dc.TableC select rowC.IdB).Contains(rowB.Id)

I haven't tried them, so I don't know for sure which will translate successfully to SQL or how efficient each might be. -- Tom N talk/contrib 01:49, 14 November 2020 (UTC)

I used the first one and it did the job. I haven't tested which of them is the most efficient. JIP | Talk 15:31, 17 November 2020 (UTC)