How can I get an "A" attribute that is related to ONLY ONE "B" attribute in a table? (SQL/LINQ)

Question

Suppose I have this table:

Image	Perimeter
a	1
b	1
b	2
d	3
e	1

I want to return the images that have relationship with only ONE perimeter.

The expected result would be images "a,d,e" because image "b" has relationship with perimeter "1" and "2".

The objective is to remove the releated image when I delete the perimeter. But if it is linked to another perimeter, I can't remove it.

How can I write this query with LINQ?

I think it would be something like this:

SELECT "ImageId" 
WHERE "PerimeterId" = PerimeterId IN 
(
SELECT "ImageId"
GROUP BY "ImageId"
HAVING COUNT("PerimeterId") = 1
)

but I don't know how to convert it to LINQ.

Answer 1

You could use a NOT EXISTS

var query = dbo.Table
    .Where(t => !dbo.Table.Any(t2 => t.Image = t.Image && t.Perimeter != t2.Perimeter));