C# Linq Query with foreign Key relationship

Question

Please consider the following scenario,

I have a two tables SplitRequest and SplitRequestApproval.

SplitRequest has a collection of SplitRequestApproval while SplitRequestApproval has one of SplitRequest

What the above means is that there is a one to many relationship between SplitRequest and SplitRequestApproval

SplitRequestApproval has ApproverUserId SplitRequestId DateApproved etc... as column. while SplitRequest has the actual request details.

The question then is I want a linq query that will get List<SplitRequest> that a logged in user (Approver) has not attended too.

So if user A with ID 123 Logs in, and navigates to the SplitRequest View, The view should only show request he has not attended to yet. therefore if he approves a SplitRequest wiht ID 12 that item should not be part of the request visible to him anymore. SplitRequestApproval gets a new row with SplitRequestId as 12 and Userid 123

How can this be achieved with linq.

var request = await _context.SplitRequestApproval
   .Include(c => c.SplitRequest)
   .Where(c => c.ApproverUserId != loggedInUserId)
   .Select(c => c.SplitRequest)
   .ToListAsync();

this is returning 0 items. whereas there are SplitRequest the loggedin user has not attended to

A solution I got but i considered it a long route


//gets the list of splitRequests
 var splitRequests = await  _context.SplitRequest.ToListAsync();

//loops through each request
            foreach (var item in splitRequests)
            {
//check if the request has been approved by the user
                var IsExistApprovalForLoggedInUser = await _context.SplitRequestApproval.AnyAsync(c => c.SplitRequestId == item.SplitRequestId && c.ApproverUserId == loggedInUserId);
                if (IsExistApprovalForLoggedInUser)
                {
//if yes remove from list
                    splitRequests.Remove(item);
                }
            }

I think there should be a linq query that will give me same result and faster

Check out this question stackoverflow.com/questions/5640259/… — David R
– David R, Commented Mar 30, 2021 at 22:25
What do you mean by a SplitRequest which the logged in user has not attended to? Is this every splitRequest where.ApproverUserId != loggedInUserId ? — Jack
– Jack, Commented Mar 30, 2021 at 22:26
From eyeballing the code, it looks like your Linq code is only selecting SplitRequests that do have a SplitRequestApproval where the user is someone other than the current user. So SplitRequests that don't have a SplitRequestApproval at all won't be included. — BenM
– BenM, Commented Mar 30, 2021 at 22:27

BenM · Accepted Answer · 2021-03-31 00:51:41Z

1

Your Linq code is selecting SplitRequestApprovals where the user is not the current user, then navigating to the SplitRequest.

This means that SplitRequests without any SplitRequestApprovals at all won't be selected, which I think is where your problem is. Also, SplitRequests with approvals from multiple users will be selected multiple times.

What you want to do is select SplitRequests that don't have any SplitRequestApprovals by the current user. Something like this:

var request = await _context.SplitRequest
    .Where(c => !c.SplitRequestApprovals.Any(c => c.ApproverUserId == loggedInUserId))
    .ToListAsync()

edited Mar 31, 2021 at 0:51

answered Mar 30, 2021 at 23:03

BenM

5394 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Linq Query with foreign Key relationship

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related