CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entities
Michael Brennt
Michael Brennt

Reputation: 751

Nested linq to entities sum strange result

I need a smart head who can explain me what in my first-try query went wrong ;)

At first I've tried to do all in one query which I considered trivial (freeArea1) but I got suspicious results so I've tried 'brute force' debug approach (foreach) and there the score was right. After few tries I decided to split my query into two (freeArea2 and freeArea3) hoping for some explanation. But again freeArea2 - freeArea3 == dif so both subsums were fine...

So what I'm asking about is why? my first query gives me irrational result.

(FA1 < 0; FA2 > FA3)

var user = db.UserSet.First(u => u.Login == HttpContext.User.Identity.Name);
var freeArea1 = db.CadastralParcelSet
                  .Where(cp => cp.UserId == user.Id)
                  .Sum(cp => cp.CadastralParcelArea - cp.AgriculturalParcels.Sum(ap => ap.AgriculturalParcelArea));

var freeArea2 = db.CadastralParcelSet
                  .Where(cp => cp.UserId == user.Id)
                  .Sum(cp => cp.CadastralParcelArea);

var freeArea3 = db.CadastralParcelSet
                  .Where(cp => cp.UserId == user.Id)
                  .Sum(cp => cp.AgriculturalParcels.Sum(ap => ap.AgriculturalParcelArea));

decimal dif = 0;
foreach (var cp in db.CadastralParcelSet.Where(c => c.UserId == user.Id))
{
    var carea = cp.CadastralParcelArea;
    var aarea = cp.AgriculturalParcels.Sum(a => a.AgriculturalParcelArea);
    dif += carea - aarea;
}

Upvotes: 0

Views: 136

Answers (1)

Michael Brennt
Michael Brennt

Reputation: 751

Okay. I think I've found my answer. Like I'd previously suggested in my comment:

if: 

cp.AgriculturalParcels.Count() == 0

then probably this:

cp.AgriculturalParcels.Sum(ap => ap.AgriculturalParcelArea)

returns NULL and whole subsum:

.Sum(cp => cp.CadastralParcelArea - cp.AgriculturalParcels.Sum(ap => ap.AgriculturalParcelArea));

seems to be omitted...

SOLUTION:

var freeArea = db.CadastralParcelSet
                 .Where(cp => (cp.UserId == user.Id))
                 .Sum(cp => cp.CadastralParcelArea - (cp.AgriculturalParcels.Count() == 0 ? 0 : cp.AgriculturalParcels.Sum(ap => ap.AgriculturalParcelArea)));

Upvotes: 1

Related Questions