CODEWITHSUNDEEP
c#linq
fdsafdsafdsafdsafs
fdsafdsafdsafdsafs

Reputation: 169

C# Linq to find a record that matches a condition, if that record is found return all related records

My problem: I am searching for Account transactions that occurred on a specific day. If I find a transaction for that day, I then need to gather the history of transactions for the account and bundle it together. My current solution requires 2 linq statements to recover the data properly. But I have a few other DB calls, so I am trying to lessen the load

My Current Solution: First, I use a linq statement to gather all transactions that occur that day and return only the account number. Then I re-run almost the same query, but this time with everything I need and using the account number from the first query in the where clause. I am also finding that linq does not like my anonymous type being used in the where clause, so I convert the account numbers to a list in the interim.

My Request: Can anyone help me find a more efficient way to recover the data that I need? And if anyone can suggest a change I can make with the anonymous issue, I would appreciate it. Error: Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context.

My Code:

public ICollection<ACCOUNT_TRANS> GetTransactionsByDate(DateTime date)
    {
        //Get account transactions that occur on this date
        var results = this.ACCOUNT_TRANS
                          .Select(at => new { at.FK_ACCOUNT, at.COMPLETION_DATE })
                          .Where(at => at.COMPLETION_DATE.Value.Day == date.Day &&
                                        at.COMPLETION_DATE.Value.Month == date.Month &&
                                        at.COMPLETION_DATE.Value.Year == date.Year)
                          .ToList();
        //Extract Account Number and removes the anonymous nature of the data
        var accountNums = results.Select(r => r.FK_ACCOUNT).ToList();

        //Return Transaction history for all changed changed
        var results2 = this.ACCOUNT_TRANS
                           .Include(at => at.ACCOUNT_TABLE1)
                           .Include(at => at.ACCOUNT_TABLE2)
                           .Include(at => at.ACCOUNT_TABLE3)
                           .Include(at => at.ACCOUNT_TABLE4)
                           .Where(at => accountNums.All(r => r == at.FK_ACCOUNT))
                           .ToList();

        return results2;
    }

Solved

Issue solved, because I my head was twisted from trying so many things. Here is what the code should be. Nice and simple the way Linq intended to be:

public ICollection<ACCOUNT_TRANS> GetTransactionsByDate(DateTime date)
    {
        //Return Transaction history for all changed changed
        var results = this.ACCOUNT_TRANS
                           .Include(at => at.ACCOUNT_TABLE1)
                           .Include(at => at.ACCOUNT_TABLE2)
                           .Include(at => at.ACCOUNT_TABLE3)
                           .Include(at => at.ACCOUNT_TABLE4)
                           .Where(at => at.COMPLETION_DATE.Value.Day == date.Day &&
                                        at.COMPLETION_DATE.Value.Month == date.Month &&
                                        at.COMPLETION_DATE.Value.Year == date.Year)
                           .ToList();

        return results;
    }

Upvotes: 2

Views: 2014

Answers (1)

M Moore
M Moore

Reputation: 66

Try this query, just this query.

    var results2 = this.ACCOUNT_TRANS
                   .Include(at => at.ACCOUNT_TABLE1)
                   .Include(at => at.ACCOUNT_TABLE2)
                   .Include(at => at.ACCOUNT_TABLE3)
                   .Include(at => at.ACCOUNT_TABLE4)
                   .Where(at => this.ACCOUNT_TRANS
                            .Where(a => at.COMPLETION_DATE.Value.Day == date.Day &&
                                a.COMPLETION_DATE.Value.Month == date.Month &&
                                a.COMPLETION_DATE.Value.Year == date.Year)
                            .Select(a => a.FK_ACCOUNT).Contains(at.FK_ACCOUNT))
                   .ToList();

Upvotes: 2

Related Questions