Ehsan Akbar Ehsan Akbar - 1 year ago 51
C# Question

My where clause doesn't apply on my result

I have this query

_ctx.TestPackageReportDetails.GroupBy(i => new {i.Status, i.TestPackageId})
.Where(m => m.Count(i => i.Status == "Accept") == 5)

The result :

enter image description here

And this query :

var q2 = _ctx.TestPackages.ToList();

enter image description here

Now i create a join between two result :

_ctx.TestPackageReportDetails.GroupBy(i => new { i.Status, i.TestPackageId })
.Where(m => m.Count(i => i.Status == "Accept") == 5)
.Join(_ctx.TestPackages, i => i.Key.TestPackageId, m => m.Id, (i, m) => new { pack = m, det = i })
.Sum(i => i.pack.Size);

I create a joint based on
and i need the sum of size value with this condition
Where(m => m.Count(i => i.Status == "Accept") == 5)
,but it doesn't work ,and the query calculates all package size not the package size with this condition
Where(m => m.Count(i => i.Status == "Accept") == 5)

Answer Source

You need to filter out the records with the status that you don't need. Also you need to start to make use of the Elegant Query-Syntax which is more readable. Like this:

var sum = (from i in _ctx.TestPackageReportDetails
           where i.Status == "Accept"
           group i by new { i.Status, i.TestPackageId } into grouping
           where grouping.Count() == 5
           join m in _ctx.TestPackages
           on grouping.Key.TestPackageId equals m.Id
           select m.Size).Sum();