nullable 합계가있는 Linq 쿼리

nullable 합계가있는 Linq 쿼리

---

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId
              select v.Points).Sum()
}

이 쿼리를 받았지만 예외로 투표가 없으면 실패합니다.

The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.

sum이 nullable int가 아닌 int를 반환하고 sum에 int를 제공하기 때문에 나는 그 이유를 가정합니다. 입력은 동일한 오류 만 제공하므로 sum은 int에서만 작동합니다.

이것에 대한 좋은 해결 방법이 있습니까?

---

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId
              select v.Points ?? 0).Sum() 
}

편집-좋아, 이것에 대해 ... (당신의 모델을 모르기 때문에 다시 촬영 ...) :

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId)
              .Sum(v => v.Points) 
}

---

nullable 형식의 Sum을 사용하고 싶으므로 값을 nullable로 캐스팅 해보십시오.

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId
              select v.Points).Sum(r => (decimal?) r.Points)
}

귀하의 문제는 여기에서 자세히 설명됩니다.

http://weblogs.asp.net/zeeshanhirani/archive/2008/07/15/applying-aggregates-to-empty-collections-causes-exception-in-linq-to-sql.aspx

---

"v.Points"가 십진수라고 가정하면 다음을 사용하십시오.

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId
              select (decimal?) v.Points).Sum() ?? 0
}

---

nullabe 십진수로 캐스팅하는 것을 좋아하지 않는다면 ToList () 메서드와 함께 Linq To Objects를 사용해 볼 수도 있습니다.

LinqToObjects 빈 컬렉션의 합계는 0이고, 여기서 LinqToSql 빈 컬렉션의 합계는 null입니다.

---

이것을 확인하십시오 :

var count = db.Cart.Where(c => c.UserName == "Name").Sum(c => (int?)c.Count) ?? 0;

따라서 문제의 근원은 다음과 같은 SQL 쿼리입니다.

SELECT SUM([Votes].[Value])
FROM [dbo].[Votes] AS [Votes]
WHERE 1 = [Votes].[UserId] 

NULL을 반환합니다.

---

간단하지만 효과적인 해결 방법은 Points.Count> 0 인 투표 수만 합산하는 것이므로 null 값은 없습니다.

from i in Db.Items
select new VotedItem
{    
  ItemId = i.ItemId,
  Points = (from v in Db.Votes
            where b.ItemId == v.ItemId &&
            v.Points.Count > 0
            select v.Points).Sum()
}

---

믹스에 다른 방법을 추가하기 위해 :)

Where(q=> q.ItemId == b.ItemId && b.Points.HasValue).Sum(q=>q.Points.Value)

비슷한 시나리오가 있었지만 합산 할 때 추가 필드를 비교하지 않았습니다.

Where(q => q.FinalValue.HasValue).Sum(q=>q.FinalValue.Value);

---

Points가 Int32 목록이라고 가정하면 다음과 같습니다.

var sum = Points.DefaultIfEmpty().Sum(c => (Int32)c ?? 0)

---

나는 같은 문제가 있었다. 빈 목록 조합으로 해결했습니다.

List<int> emptyPoints = new List<int>() { 0 };

from i in Db.Items
select new VotedItem
{
 ItemId = i.ItemId,
 Points = (from v in Db.Votes
           where b.ItemId == v.ItemId
           select v.Points).Union(emptyPoints).Sum()
}

"Points"가 정수인 경우 작동합니다.

---

같은 경우라고 생각합니다. 나는 그것을 해결했다. 이것은 내 해결책입니다.

var x = (from a in this.db.Pohybs
                 let sum = (from p in a.Pohybs
                            where p.PohybTyp.Vydej == true
                            select p.PocetJednotek).Sum()
                 where a.IDDil == IDDil && a.PohybTyp.Vydej == false
                 && ( ((int?)sum??0) < a.PocetJednotek)
                 select a);

도움이 되었기를 바랍니다.

---

        (from i in Db.Items
         where (from v in Db.Votes
                where i.ItemId == v.ItemId
                select v.Points).Count() > 0
         select new VotedItem
         {
             ItemId = i.ItemId,
             Points = (from v in Db.Items
                       where i.ItemId == v.ItemId
                       select v.Points).Sum()
         }).Union(from i in Db.Items
                  where (from v in Db.Votes
                         where i.ItemId == v.ItemId
                         select v.Points).Count() == 0
                  select new VotedItem
                  {
                      ItemId = i.ItemId,
                      Points = 0
                  }).OrderBy(i => i.Points);

이것은 작동하지만 매우 예쁘거나 읽기 어렵습니다.

---

I had a similar issue and came up with the solution of getting whatever I was trying to get out of the database, do a count on those and then only if I had anything returned do a sum. Wasn't able to get the cast working for some reason so posting this if anyone else had similar issues.

e.g.

Votes = (from v in Db.Votes
          where b.ItemId = v.ItemId
          select v)

And then check to see if you've got any results so that you don't get null returned.

If (Votes.Count > 0) Then
    Points = Votes.Sum(Function(v) v.Points)
End If

---

Similar to previous answers, but you can also cast the result of the entire sum to the nullable type.

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (decimal?)((from v in Db.Votes
              where b.ItemId == v.ItemId
              select v.Points).Sum()) ?? 0
}

Arguably this better fits what is really going on but it has the same effect as the cast in this answer.

---

Thought I would throw another solution out there. I had a similar issue and this is how I ended up solving it:

Where(a => a.ItemId == b.ItemId && !b.IsPointsNull()).Sum(b => b.Points)

참고URL : https://stackoverflow.com/questions/696431/linq-query-with-nullable-sum