Answered by:
null from Sum in a subquery
Question

If I do an inmemory query on a numeric array:Dim mynums() As Integer = {1, 2, 3, 4, 5}
Dim result = From i In mynums _
Select New With {i, .startpos = (From x In mynums _
Where x < i _
Select x).Sum() + 1}which will return the expected result of:
1 1
2 2
3 4
4 7
5 11
But, if I create a sql server table to emulate this and do the following..Dim result = from i in DC.nynums _
select new with {.seq = i.seq, _
.startpos = (from x in DC.mynums _
where x.seq < i.seq _
select x.seq).Sum() + 1}I unfortunately get:
1 nothing
2 2
3 4
4 7
5 11How can I get the subselect to actually return a value when it doesn't find any rows to build the sum ?
Answers

Ok, I think I have it now..
I found GetValueOrDefault, which as the name suggests, get the value or returns a default value..
Dim Result = from i in DC.Mynums _
select new with {.seq = i.seq, _
.startpos = (from x in DC.Mynums _
where x.seq < i.seq _
select x.seq).Sum().GetValueOrDefault(0) + 1}however, this gets upset with: 'GetValueOrDefault' is not a member of 'Integer'
So you then have to cast the seq column to nullable..
select new integer?(x.seq)).Sum().GetValueOrDefault(0) + 1}
Then I get the expected results.
All replies

HI
Code Snippetvar result = (from i in db.Numbers
select new MyNumber
{
Code = i.Code,
Pluss = (from x in db.Numbers
where x.Code < i.Code
select x.Code).Count() == 0 ? i.Code : (from x in db.Numbers
where x.Code < i.Code
select x.Code).Sum() + 1
}).ToList();
Here is generated query in sql
Code SnippetSELECT [t0].[Code],
(CASE
WHEN ((
SELECT COUNT(*)
FROM [dbo].[Numbers] AS [t1]
WHERE [t1].[Code] < [t0].[Code]
)) = 0 THEN [t0].[Code]
ELSE ((
SELECT SUM([t2].[Code])
FROM [dbo].[Numbers] AS [t2]
WHERE [t2].[Code] < [t0].[Code]
)) + 1
END) AS [Pluss]
FROM [dbo].[Numbers] AS [t0]

Ok, I think I have it now..
I found GetValueOrDefault, which as the name suggests, get the value or returns a default value..
Dim Result = from i in DC.Mynums _
select new with {.seq = i.seq, _
.startpos = (from x in DC.Mynums _
where x.seq < i.seq _
select x.seq).Sum().GetValueOrDefault(0) + 1}however, this gets upset with: 'GetValueOrDefault' is not a member of 'Integer'
So you then have to cast the seq column to nullable..
select new integer?(x.seq)).Sum().GetValueOrDefault(0) + 1}
Then I get the expected results.