Suppose I have this table:

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.

CodePudding user response：

You could use a NOT EXISTS

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

CodePudding user response：

You can easily adapt this to only select the image part. But, if you are coming from SQL, thinking about "Selecting rows" based on a "HAVING()" group calculation, then you will want to look at the .SelectMany() LINQ method. This lets you "combine back together data partitioned into groups". While your needs are to only return "one from each group", it's easy to see where this can be adjusted. This can be run in the "C# interactive window" of SSDT 2015:

struct imagePerimeter { //this might be whatever object type it is for you...
    public string Image { get; set; } //a,b,b,d,e
    public int Perimeter { get; set; } //1,1,2,3,1
}
Func<string, int, imagePerimeter> newIP = (i, p) => new imagePerimeter() { Image = i, Perimeter = p };
List<imagePerimeter> results = new List<imagePerimeter>() { {newIP("a",1) }
    ,{newIP("b",1) }
    ,{newIP("b",2) }
    ,{newIP("d",3) }
    ,{newIP("e",1) } };

Func<imagePerimeter, string> ipImage = (ip) => ip.Image; //the Func's "ipImage" and "newIP" could just be inlined into LINQ, but it helps to see and debug at times IMO. 
var imagesWithOnePerimeter = results.GroupBy<imagePerimeter, string>(ipImage) //even in SQL, the "GROUP BY" conceptually comes first, in LINQ, it comes first in code too!
    .Select(grp => new { Image = grp.Key, PerimeterCount = grp.Count(), Details = grp }) //there's probably a more technical term, but notice how we "carry forward" the original reference to [grp]
    .Where(subTotals => subTotals.PerimeterCount == 1)
    .SelectMany(filtered => filtered.Details.AsEnumerable())
    .ToList();