Optimize queries for Union, Except, Join with LINQ and C#

Question

I have 2 objects (lists loaded from XML) report and database (showed bellow in code) and i should analyse them and mark items with 0, 1, 2, 3 according to some conditions

TransactionResultCode = 0; // SUCCESS (all fields are equivalents: [Id, AccountNumber, Date, Amount])
TransactionResultCode = 1; // Exists in report but Not in database 
TransactionResultCode = 2; // Exists in database but Not in report 
TransactionResultCode = 3; // Field [Id] are equals but other fields [AccountNumber, Date, Amount] are different.

I'll be happy if somebody could found time to suggest how to optimize some queries. Bellow is the code:

THANK YOU!!!

        //TransactionResultCode = 0 - SUCCESS
        //JOIN on all fields
        var result0 = from d in database
                      from r in report
                      where (d.TransactionId == r.MovementID) &&
                             (d.TransactionAccountNumber == long.Parse(r.AccountNumber)) &&
                             (d.TransactionDate == r.MovementDate) &&
                             (d.TransactionAmount == r.Amount)
                      orderby d.TransactionId
                      select new TransactionList()
                      {
                          TransactionId = d.TransactionId,
                          TransactionAccountNumber = d.TransactionAccountNumber,
                          TransactionDate = d.TransactionDate,
                          TransactionAmount = d.TransactionAmount,
                          TransactionResultCode = 0
                      };

        //*******************************************
       //JOIN on  [Id] field
       var joinedList = from d in database
                         from r in report
                         where d.TransactionId == r.MovementID
                         select new TransactionList()
                         {
                             TransactionId = d.TransactionId,
                             TransactionAccountNumber = d.TransactionAccountNumber,
                             TransactionDate = d.TransactionDate,
                             TransactionAmount = d.TransactionAmount
                         };

        //Difference report - database 
        var onlyReportID = report.Select(r => r.MovementID).Except(joinedList.Select(d => d.TransactionId));

        //TransactionResultCode = 1 - Not Found in database
        var result1 = from o in onlyReportID
                      from r in report
                      where (o == r.MovementID)
                      orderby r.MovementID
                      select new TransactionList()
                      {
                          TransactionId = r.MovementID,
                          TransactionAccountNumber = long.Parse(r.AccountNumber),
                          TransactionDate = r.MovementDate,
                          TransactionAmount = r.Amount,
                          TransactionResultCode = 1
                      };

        //*******************************************

        //Difference database - report 
        var onlyDatabaseID = database.Select(d => d.TransactionId).Except(joinedList.Select(d => d.TransactionId));

        //TransactionResultCode = 2 - Not Found in report
        var result2 = from o in onlyDatabaseID
                      from d in database
                      where (o == d.TransactionId)
                      orderby d.TransactionId
                      select new TransactionList()
                      {
                          TransactionId = d.TransactionId,
                          TransactionAccountNumber = d.TransactionAccountNumber,
                          TransactionDate = d.TransactionDate,
                          TransactionAmount = d.TransactionAmount,
                          TransactionResultCode = 2
                      };

        //*******************************************

        var qwe = joinedList.Select(j => j.TransactionId).Except(result0.Select(r => r.TransactionId));

        //TransactionResultCode = 3 - Transaction Results are different (Amount, AccountNumber, Date, )
        var result3 = from j in joinedList
                      from q in qwe
                      where j.TransactionId == q
                      select new TransactionList()
                      {
                          TransactionId = j.TransactionId,
                          TransactionAccountNumber = j.TransactionAccountNumber,
                          TransactionDate = j.TransactionDate,
                          TransactionAmount = j.TransactionAmount,
                          TransactionResultCode = 3
                      };

Answer 1

you may try something like below:

public void Test()
{
    var report = new[] {new Item(1, "foo", "boo"), new Item(2, "foo2", "boo2"), new Item(3, "foo3", "boo3")};
    var dataBase = new[] {new Item(1, "foo", "boo"), new Item(2, "foo22", "boo2"), new Item(4, "txt", "rt")};

    Func<Item, bool> inBothLists = (i) => report.Contains(i) && dataBase.Contains(i);
    Func<IEnumerable<Item>, Item, bool> containsWithID = (e, i) => e.Select(_ => _.ID).Contains(i.ID);

    Func<Item, int> getCode = i =>
                                    {
                                        if (inBothLists(i))
                                        {
                                            return 0;
                                        }
                                        if(containsWithID(report, i) && containsWithID(dataBase, i))
                                        {
                                            return 3;
                                        }
                                        if (report.Contains(i))
                                        {
                                            return 2;
                                        }
                                        else return 1;
                                    };

    var result = (from item in dataBase.Union(report) select new {Code = getCode(item), Item = item}).Distinct();
}

public class Item
{
    // You need also to override Equals() and GetHashCode().. I omitted them to save space
    public Item(int id, string text1, string text2)
    {
        ID = id;
        Text1 = text1;
        Text2 = text2;
    }

    public int ID { get; set; }
    public string Text1 { get; set; }
    public string Text2 { get; set; }
}

Note that you need to either implement Equals() for you items, or implement an IEqualityComparer<> and feed it to Contains() methods.