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.
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.