i know that i must be missing something but i can not for the life of
me figure it out.
i have a table with three columns report#,line# and page#. I am
linking this table to its self to get the range of lines that match
this page#. the sql works just fine but i can not seem to get it to
work in linq
table:
report#| line#| page#
0 | 33 | 1
0 | 90 | 2
0 | 146 | 3
1 | 33 | 1
1 | 90 | 2
1 | 147 | 3
1 | 204 | 4
sql:
SELECT a.rn AS r, a.ln AS f, a.pg AS p, MIN(b.ln) AS l
FROM test.header AS a INNER JOIN
test.header AS b ON a.rn = b.rn
where a.ln < b.ln
GROUP BY a.rn, a.ln, a.pg
linq:
var q =
from a in x
from b in x
where a.rn == b.rn && a.ln < b.ln
orderby a.rn, a.ln, b.ln ascending
group a by new { r = a.rn, f = a.ln } into c
select new { c.Key, l = c.Min(b => b.ln) };
this should return
r | f | l |
0| 33| 90
0| 90 | 146
1| 33 | 90
1| 90 | 147
1| 147| 204
instead it returns
0| 33| 33
0| 90 | 90
1| 33 | 33
1| 90 | 90
1| 147| 147
so i assume the error is in the join operation but im not sure
where.
i was playing with this some more and if i do it in two querys like so
it works but not in one? how can i do it in one query and y is it not
working?
var x = new[]
{
new {rn=0,ln=33,pg=1},
new {rn=0,ln=90,pg=2},
new {rn=0,ln=146,pg=3},
new {rn=1,ln=33,pg=1},
new {rn=1,ln=90,pg=2},
new {rn=1,ln=147,pg=3},
new {rn=1,ln=204,pg=4}
};
var y =
from a in x
from b in x
where a.rn == b.rn && a.ln < b.ln
select new { r = a.rn, f = a.ln, l=b.ln };
var z =
from a in y
group a by new { r=a.r, f= a.f } into b
select new { b.Key.r,b.Key.f, l=b.Min(a => a.l) };
thanks again for helping.
--
You received this message because you are subscribed to the Google
Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML
Web Services,.NET Remoting" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en
or visit the group website at http://megasolutions.net
