New Posts New Posts RSS Feed: Question on querying unions
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Question on querying unions
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Topic: Question on querying unions
    Posted: 01-Jul-2013 at 12:18am
I have two tables in my database. One table holds payments and the other holds charges. I have a view that unions the two tables together. When i run a query in Enterprise Manager for a specific she has a total of 292 rows. When i try to retrieve the records for her using Linq only 26 records are returned. Its like the query stops returning records before the union is finishing joining all the rows. Any suggestions? BTW this is DevForce 2010. I cannot use .net 4.5 as i have too many clients still using XP at this point.
 
Here is my query
 
     public void GetMemberTransactionsAsync(string socSecNo, Action<IEnumerable<MemberTransaction>> onSuccess,
                               Action<Exception> onFail)
        {
            var query = Manager.MemberTransactions
                      .Where(m => m.socsecno == socSecNo)
                      .OrderBy(m => m.TranDate)
                   .ExecuteAsync(
                       op =>
                       {
                           if (op.CompletedSuccessfully)
                           {
                               if (onSuccess != null)
                                   onSuccess(op.Results);
                           }
                           else
                           {
                               if (onFail != null)
                               {
                                   op.MarkErrorAsHandled();
                                   onFail(op.Error);
                               }
                           }
                       }
                );
        }
 
Here is my union and view
SELECT     socsecno, datedeb AS trandate, round(duesamount, 2) AS tranamount, 'D' AS trancode, isnull(duescatdesc, '***') AS trantype, 0 AS receipt, '' AS paidthru, isnull(note, ' ') 
                      AS remark, duesowed.duescatcd
FROM         duesowed LEFT JOIN
                      duescateg ON duesowed.duescatcd = duescateg.duescatcd
UNION ALL
SELECT     socsecno, batchdate AS trandate, round((duesamount * - 1), 2) AS tranamount, 'P' AS trancode, isnull(paytypedesc, '***') AS trantype, receiptno AS receipt, paidthru, 
                      isnull(checkno, ' ') AS remark, duescatcd
FROM         memdues LEFT JOIN
                      paytypes ON memdues.paytype = paytypes.paytype
 
Am I going to have to read both tables and merge them myself in code or can this be fixed?
 
Back to Top
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Posted: 01-Jul-2013 at 4:30am
Further research reveals the following. If i change the async call to a sync call it produces exactly the same result. So the problem appears to be that the query quits before all the rows come back. It must have to do with how SQL Server does unions. Has anyone had any luck querying unions?
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 01-Jul-2013 at 8:01am
Check the EntityKey for the MemberTransaction entity.   Each row returned from your view needs to have a unique key.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down