5

I have given up trying to create a linq query to retrieve a sql server view which is a union between two tables. I will now try to create a linq union.

I have two views, MemberDuesPaid and MemberDuesOwed. They have the same fields in both; (BatchNo, TranDate, DebitAmount, CreditAmount, ReceiptNo, CheckNo, SocSecNo).

I also have a helper class in my application which is called MemberTransaction. It has all the same properties.

How how do i do a union between the two tables where socSecNo = the ssn passed in? I want to union the two tables and return an IEnumerable collection of MemberTransaction. After the two tables are unioned together i want to have the collection that is returned ordered by trandate in descending order.

Bill G
  • 161
  • 1
  • 4
  • 11

2 Answers2

6

You can do it in a Linq Union query:

var infoQuery =
    (from paid in db.MemberDuesPaid 
    select new MemberTransaction() {
        BatchNo = paid.BatchNo,
        TranDate = paid.TranDate,
        DebitAmount = paid.DebitAmount,
        CreditAmount = paid.CreditAmount,
        ReceiptNo = paid.ReceiptNo,
        CheckNo = paid.CheckNo,
        SocSecNo = paid.SocSecNo})
    .Union
        (from owed in db.MemberDuesOwed
        select new MemberTransaction() {
        BatchNo = owed.BatchNo,
        TranDate = owed.TranDate,
        DebitAmount = owed.DebitAmount,
        CreditAmount = owed.CreditAmount,
        ReceiptNo = owed.ReceiptNo,
        CheckNo = owed.CheckNo,
        SocSecNo = owed.SocSecNo});

That should return you a set with everything combined into a single list.

[Edit]

If you want distinct values, you can do something like this after the above statement (you can do it inline if you bracket everything, but this is simpler to explain):

infoQuery = infoQuery.Distinct();

The variable infoQuery will by this time be populated entirely with objects of type MemberTransaction rather than the two disparate types in the union statement.

Jane S
  • 1,417
  • 16
  • 21
  • 1
    `Union` already removes duplicate values. It's a set based operation, and as a result it returns a set as a result. Sets don't contain duplicate values. Adding `Distinct` afterwards is superfluous. – Servy Jun 27 '14 at 14:45
1

Assuming you've got two collections, one representing each view:

var paid = new List<MemberDuesPaid>();

var owed = new List<MemberDuesOwed>();

Convert both collections above to instances of the third class before performing the union:

var everyone
    = paid.Select(x => new MemberTransaction { BatchNo = x.BatchNo, ... })
          .Union(owed.Select(x => new MemberTransaction { BatchNo = x.BatchNo, ... }))
          .Where(x => x.SocSecNo == ssn)
          .OrderByDescending(x => x.TranDate)
          .ToList();

Now you've got a collection of MemberTransaction, but there's nothing to indicate how one MemberTransaction equals another. So if you just run the above, you'll end up with everything from both collections in the result, instead of a true union.

You have to tell it what makes two instance equal, by implementing IEquatable<T> on the MemberTransaction class.

public class MemberTransaction : IEquatable<MemberTransaction>
{
    public int BatchNo { get; set; }
    public DateTime TranDate { get; set; }
    public decimal DebitAmount { get; set; }
    public decimal CreditAmount { get; set; }
    public int ReceiptNo { get; set; }
    public int CheckNo { get; set; }
    public int SocSecNo { get; set; }

    public bool Equals(MemberTransaction other)
    {
        return BatchNo == other.BatchNo
               && TranDate.Equals(other.TranDate)
               && DebitAmount == other.DebitAmount
               && CreditAmount == other.CreditAmount
               && ReceiptNo == other.ReceiptNo
               && CheckNo == other.CheckNo
               && SocSecNo == other.SocSecNo;
    }
}
Grant Winney
  • 65,241
  • 13
  • 115
  • 165
  • That does seem a little overly complex. You can get the result without modifying the existing classes using Linq :) – Jane S Jun 26 '14 at 00:58
  • Yes it will return all rows, but in this case the data sources are independent so I would assume that is what the OP wants. :) (I was going to suggest appending `Distinct()` but I haven't tested it). – Jane S Jun 26 '14 at 01:18
  • When I test it I only get distinct records returned. – Jane S Jun 26 '14 at 01:31
  • Oh, I should have said that I bracketed the entire query (ie, both halves of the union), then ran `Distinct()` on that. That way you're working apples with apples of the same type of `MembershipTransaction` and therefore comparison will work. – Jane S Jun 26 '14 at 01:40