LINQ Union between two tables with the same fields

问题:

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.

回答1:

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.

回答2:

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;
    }
}