Linq to SQL execute where clause only if external condition exists (if variable = x)

Question

I'm extracting a list of candidates that have each completed X number of forms. When initiating the report the report manager can select specific forms to report on or leave the field blank to get all forms.

Here's an example of my code:

var candidates = from candidate in BPData.Candidates
                    join programGroup in BPData.ProgramGroups on candidate.GroupID equals programGroup.GroupID
                    where candidate.GroupID == programGroupID
                    from u in BPData.Users.Where(u => u.UserID == candidate.UserID).DefaultIfEmpty()
                    let formEntries = from formReport in BPData.FormReports
                                    where formReport.CandidateID == candidate.CandidateID
                                    where (from form in forms
                                            select form.FormID).Contains(formReport.FormID)
                                    join actionFormEntry in BPData.ActionFormEntries on formReport.ReportKey equals actionFormEntry.ValidationKey
                                    orderby formReport.FormID
                                    select new FormEntryViewModel
                                    {
                                        ... removed for brevity
                                    }
                    select new CandidateFormEntriesViewModel
                    {
                        CandidateID = candidate.CandidateID,
                        CandidateName = candidate.Name,
                        FormEntries = (List<FormEntryViewModel>)formEntries
                    };

The following line of code:

where (from form in forms select form.FormID).Contains(formReport.FormID)

Works well if the report manager selected specific forms to report on. But, if they leave the field blank this won't work. It's supposed to come back with all forms, but obviously won't because of the way I've setup the condition.

My question:
Is it possible to do some form of condition on the where clause to cater for this (Note - this is just my rough thoughts):

where (forms.Count() > 0) ? **keep the same code as my query above** : true

This approach actually works. It extracts ALL forms if forms.Count() == 0 and only specific forms when forms.Count() > 0. But is this the right way to go about it?

SoronelHaetir · Accepted Answer · 2018-01-19 18:33:26Z

1

I would say use a logical or, not ternary, since this is a where you don't care about incorporating the result into the rest of the query, that sort of thing is when conditional linq becomes complicated.

where (forms.Count() == 0 || **keep the same code as my query above**)

answered Jan 19, 2018 at 18:33

SoronelHaetir

15.6k1 gold badge18 silver badges26 bronze badges

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

3 Comments

Tone Over a year ago

Could you use !forms.Any() instead of forms.Count() == 0 in linq-to-sql? Not sure whether it would actually generate different SQL but I'd use that form typically in linq.

Jacques Over a year ago

Since both the ternary and your suggestion will work, are there any other reasons to use one over the other apart from the query becoming complicated?

Jacques Over a year ago

PS. I did try your suggestion and it does work. I just want to get an answer to my previous question to you before I mark it as the answer.

Ctznkane525 · Accepted Answer · 2018-01-19 18:31:44Z

0

How about this?

where (from form in forms select form.FormID).Count==0 or (from form in forms select form.FormID).Contains(formReport.FormID)

answered Jan 19, 2018 at 18:31

Ctznkane525

7,5453 gold badges19 silver badges41 bronze badges

Collectives™ on Stack Overflow

Linq to SQL execute where clause only if external condition exists (if variable = x)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related