Using conditional statements in linq query c#

Question

I have 2 queries. First gets the full name of a committee head if the committee has more that 1 member.

var result = await db.ExpertCommittees
    .Where(f => f.Id == committeeId)
    .Where(f => f.ExpertCommitteeMembers.Count > 1)
    .Select(f => f.ExpertCommitteeMembers
    .Where(m => m.IsCommitteeHead)
    .FirstOrDefault().Expert.FullName)
    .FirstOrDefaultAsync();

The second one gets the full name of the only committee member if the committee has only 1 member

var result2 = await db.ExpertCommittees
    .Where(f => f.Id == committeeId)
    .Where(f => f.ExpertCommitteeMembers.Count == 1)
    .Select(f => f.ExpertCommitteeMembers
    .FirstOrDefault().Expert.FullName)
    .FirstOrDefaultAsync();

Is it possible to check how many members does the committee have and then return the correct name all in the same query? Or do I first have to check how many members does the committee have and then run the appropriate query seperatly?

Why m.IsCommitteeHead is not enough? Are there rows where f.ExpertCommitteeMembers.Count == 1 and there is no Head? ... edit: even if you may try order desc by IsCommitteeHead and take first — Selvin
– Selvin, Commented Jan 9, 2020 at 13:57
From what I understand, the query doesn't run until called (if(result2.something){}). With that, you can't get a count, you'll have to do .Count(). Sorry if misunderstanding — Oyyou
– Oyyou, Commented Jan 9, 2020 at 13:57
It's possible to be the only person in the committee and not be the committee head — Tomas
– Tomas, Commented Jan 9, 2020 at 14:00

D-Shih · Accepted Answer · 2020-01-09 14:02:23Z

3

If I understand correctly you can try to let condition into inner linqwhere

var result = await db.ExpertCommittees
    .Where(f => f.Id == committeeId)
    .Select(f => f.ExpertCommitteeMembers.Where(m => 
                 (m.IsCommitteeHead && 
                 f.ExpertCommitteeMembers.Count > 1)||f.ExpertCommitteeMembers.Count == 1).FirstOrDefault().Expert.FullName)
    .FirstOrDefaultAsync();

answered Jan 9, 2020 at 14:02

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

Comments

Muhammad Nasir · Accepted Answer · 2020-01-09 14:02:53Z

1

you can Create a create anonymous object in linq query select. which will contain FullName and count.

var result = await db.ExpertCommittees
    .Where(f => f.Id == committeeId)
    .Where(f => f.ExpertCommitteeMembers.Count > 1)
    .Select(f => new
    {
        FullName = f.ExpertCommitteeMembers
    .Where(m => m.IsCommitteeHead)
    .FirstOrDefault().Expert.FullName,
        Count = f.ExpertCommitteeMembers.Count
    })
    .FirstOrDefaultAsync();

answered Jan 9, 2020 at 14:02

Muhammad Nasir

2,2045 gold badges38 silver badges66 bronze badges

Comments

Holger · Accepted Answer · 2020-01-09 14:16:46Z

A generated Join, as it is generated from ExpertCommittees to ExpertCommitteeMembers, with a Navigation-Collection, will always do a Left Join, what you want is an Inner Join. It will give you only items with entities in both tables.

This will be something like

    db.ExpertCommittees.Join(db.ExpertCommittemember, x=>someid, y=>somid, 
          (comittee, member) => new { comittee, member});

But this will give you one line per member.... with the possibility to filter by "IsComitteeHead" or whatever.

You can append Grouping, or use a GroupJoin directly, to have a List of "Commitees", each with a List of it's members ...and only if there are members.

Without knowing the data structure, ID's, Foreign-Keys and table name, we cannot produce a valid query.

Collectives™ on Stack Overflow

Using conditional statements in linq query c#

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related