0

The following are some stuffs behind the scene.

public class User : BaseTransactionModel
{
    public Guid UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Transaction
{

    public int TransactionId { get; set; }
    public string TransactionName { get; set; }
    public int TransactionHistoryId { get; set; }

    public virtual TransactionHistory TransactionHistory { get; set; }
}

public class TransactionHistory
{
    public int TransactionHistoryId { get; set; }
    public DateTime CreatedDateTime { get; set; }
    public Guid UserId { get; set; }
    public int TransactionId { get; set; }

    public virtual ICollection<User> Users { get; set; }
    public virtual ICollection<Transaction> Transactions { get; set; }
}

public class BaseTransactionModel
{

    public int TransactionHistoryId { get; set; }
    public int TransactionId { get; set; }

    public virtual TransactionHistory TransactionHistory { get; set; }
    public virtual Transaction Transaction { get; set; }
}

With the above entities, I am trying to get all the Transactions from a User on a specified Date.

So I tried including both User and TransactionHistory on the Transaction DbSet:

Context.Transaction
    .Include(t => t.User)
    .Include(t => t.TransactionHistory)
    ...

I wanted to add a where clause that filter the transaction from the date in transaction history entities:

Context.Transaction
    .Include(t => t.User)
    .Include(t => t.TransactionHistory)
    .Where(t => t.TransactionHistory.CreatedDateTime <= StartDateFilter && t.TransactionHistory.CreatedDateTime >= EndDateFilter)

but could not able to do so. Since TransactionHistory is also a list of entity (or whatever it is called).

What could be the possible solution to get what I am trying to achieve ?

PS: I am new to EF and the title could be misleading. If so, feel free to update it.

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
JF-Mechs
  • 1,083
  • 10
  • 23
  • Your model-classes don't look like they're supposed to. Try to research, how relationships should be defined in classes (with navigation-properties). – Nikolaus Dec 06 '17 at 05:56
  • @Nikolaus I fix my grievance on the sample classes above. Thanks for the suggestion I'll make sure to take a look on it. – JF-Mechs Dec 06 '17 at 06:35

3 Answers3

0

You might need to rethink your class design e.g. 

public class User
    {
        public Guid UserId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public virtual ICollection<Transaction> TransactionHistory { get; set; }
    }

    public class Transaction
    {    
        public int TransactionId { get; set; }
        public string TransactionName { get; set; }
        public DateTime CreatedDateTime { get; set; }
    }

Then to get the transactions like you require your query could be something like this:

 var result = Context.User
                .Where(u => u.UserId == myguid)
                .Select(t => new
                {
                    UserId = t.UserId,
                    Transactions = t.TransactionHistory.Where(x => x.CreatedDateTime >= StartDateFilter && x.CreatedDateTime <= EndDateFilter)
                })
                .ToList();
majita
  • 1,278
  • 14
  • 24
  • "You might need to rethink your class design"... I know that this could be one of the solutions but I don't want to do it because this is someone else's project that I am trying to explore and I'm avoiding on modifying any of its class models. If there is no possible solution other than this, then it could be my last resort. – JF-Mechs Dec 06 '17 at 08:48
0

What you are looking is to be able to filter included entities.

There is two way to filter include Entity:

  • Using a projection (See @majita answer)
  • Using a third party library

Disclaimer: I'm the owner of the project Entity Framework Plus

The EF+ Query IncludeFilter allow easily filter included entities.

Context.Transaction
    .IncludeFilter(t => t.User)
    .IncludeFilter(t => t.TransactionHistory
        .Where(x => x.TransactionHistory.CreatedDateTime <= StartDateFilter && x.TransactionHistory.CreatedDateTime >= EndDateFilter))

Under the hood, the library does exactly a projection.

One limitation is all Include but now be called with IncludeFilter even if no filter is specified such as the User.

Wiki: EF+ Query Include Filter

Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
-1

Try this instead

Context.Transaction
    .Where(t => t.TransactionHistory.CreatedDateTime <= StartDateFilter && t.TransactionHistory.CreatedDateTime >= EndDateFilter)
.Select(x=> new {x.User,x.TransactionHistory});

OR This

Context.Transaction
.Where(t => t.TransactionHistory.CreatedDateTime <= StartDateFilter && t.TransactionHistory.CreatedDateTime >= EndDateFilter)
.Include(t => t.User)
.Include(t => t.TransactionHistory)
Daniyal Humayun
  • 66
  • 10