Conditional navigation properties in EF Core

Question

I have a User table with a corresponding UserAudits table that tracks the changes. The operation type is stored in the audit table (insert, update, delete).

I want to create a navigation property on my User entity so I can fetch the "Inserted" record from the audit table. Is this possible? Usually when setting up a navigation property there is no way to specify extra conditions on the join.

User:

public class User
{
    public int Id {get; set;}
    public string Name {get;set;}
    public UserAudit InsertedAuditRecord {get; set;} // <-- How to configure this?
}

UserAudit:

public class UserAudit
{
    public int AuditId {get; set;
    public int Id {get; set;}
    public string Name {get; set;}
    public string OperationType {get; set;}
}

I know I could create a one-to-many navigation property on my User entity and then query that:

ICollection<UserAudit> UserAudits {get; set;}
UserAudit InsertedAuditRecord => UserAudits.First(ua => ua.OperationType = "insert");

But this would require bringing the entire audit table for the user into memory just to find a single record.

Surely there's some way to accomplish this?

@GertArnold I'm currently on 2.2.2 but can upgrade to 3 if necessary as my application is still in development. — Codemunkie, Oct 27 '19 at 21:52

score 2 · Answer 1 · answered Dec 26 '20 at 15:07

2

I guess what you're looking for (which is Include() using Where() clause in it), is now added to .net 5. have a look at this answer.

answered Dec 26 '20 at 15:07

Rzassar

2,117
1
33
55

score -1 · Answer 2 · answered Oct 27 '19 at 22:54

-1

A Where (First) on the child record does not bring the whole child object back.

answered Oct 27 '19 at 22:54

tymtam

31,798
8
86
126

Please can you elaborate? – Codemunkie Oct 27 '19 at 23:08
What's your sql when you do the query? – tymtam Oct 27 '19 at 23:11
If i'm using an `ICollection` it is null unless I call `Include(u => u.UserAudits)` which brings back all records. – Codemunkie Oct 27 '19 at 23:29

Conditional navigation properties in EF Core

2 Answers2