GroupBy then Select with conditional record addition-CodePudding

I am having LINQ query in which I have to response with a result set which is depending on inner field collection. I have done the thing via LINQ query then a foreach but I wanted to avoid foreach loop and do it somehow from group by

List<ResultModel> result = new List<ResultModel>();           
var tempResultSet = _context.MainRecordTable.Where(h => h.id)
    .Select(lev => new 
    {
        Contacts = (lev.basetable2 != null 
                            && lev.basetable2.basetable3 != null 
                            && lev.basetable2.basetable3.basetable6 != null
                            &&  lev.basetable2.basetable3.basetable6.Any(h=>h.contact != null)
                            ? lev.basetable2.basetable3.basetable6.Where(h=>h.contact != null).Select(h=>h.contact).Distinct() : null),
        Key1 = lev.basetable5 != null ? lev.basetable5.Id : null,
        Key2 = lev.basetable2 != null && lev.basetable2.basetable3 != null && lev.basetable2.basetable3.basetable4 != null ?
                            lev.basetable2.basetable3.basetable4.id      : null

    })
    .Distinct()
    .ToList();

foreach (var x in tempResultSet)
{
    if (x.Contacts != null)
    {
        foreach (var contact in x.Contacts)
        {
            result.Add(new ResultModel
            {
                Key1 = x.Key1,
                Key2 = x.Key2,                           
                ContactKey = contact.id
            });
        }
    }
    else
    {
        result.Add(new ResultModel
        {
            Key1 = x.Key1,
            Key2 = x.Key2                        
        });
    }
}

return result;

My Final Linq query is like

return _context.baseTable1.Where(h => h.id == Id ).Select(lev => new { Contact = (lev.baseTable2 != null && lev.baseTable2.baseTable3 != null && lev.baseTable2.baseTable3.baseTable5.Any(h => h.contact != null) ? lev.baseTable2.baseTable3.baseTable5.Where(h => h.contact != null).GroupBy(h => h.contact).Select(c=>c.FirstOrDefault().contact) : null), Key1 = lev.office != null ? lev.office.id, : null, Key2 = lev.baseTable2 != null && lev.baseTable2.baseTable3 != null && lev.baseTable2.baseTable3.baseTable4 != null ? lev.baseTable2.baseTable3.baseTable4.id : null

        }).ToList().SelectMany(x =>
        {
            if (!x.Contact.Any())
            {
                return new List<FinalModel> { new FinalModel { Key1 = x.Key1, Key2 = x.Key2 } };
            }
            else
                return x.Contact.Select(contact => new FinalModel()
                {
                    ContactKey = contact.id,
                    Key1 = x.Key1,
                    Key2 = x.Key2
                });
        });

CodePudding user response：

You can use SelectMany to flatten the array, e.g.:

List<ResultModel> result = _context.MainRecordTable.Where(h => h.id)
    .Select(lev => new 
    {
        Contacts = (lev.basetable2 != null 
                            && lev.basetable2.basetable3 != null 
                            && lev.basetable2.basetable3.basetable6 != null
                            &&  lev.basetable2.basetable3.basetable6.Any(h=>h.contact != null)
                            ? lev.basetable2.basetable3.basetable6.Where(h=>h.contact != null).Select(h=>h.contact).Distinct() : null),
        Key1 = lev.basetable5 != null ? lev.basetable5.Id : null,
        Key2 = lev.basetable2 != null && lev.basetable2.basetable3 != null && lev.basetable2.basetable3.basetable4 != null ?
                            lev.basetable2.basetable3.basetable4.id      : null

    })
    .Distinct()
    .ToList()
    .SelectMany(x => {
      if (x.Contacts == null)
        return new ResultModel[]  
        {
          Key1 = x.Key1, 
          Key2 = x.Key2,
        };
      else 
        return x.Contacts.Select(contact => new ResultModel() 
          { 
            Key1 = x.Key1, 
            Key2 = x.Key2, 
            ContactKey = contact.id,
          }).ToArray();        
    })
    .ToList();

In the SelectMany, it is checked whether the contact list is set; if not, a single item is returned, otherwise, an item for each contact is returned.

CodePudding user response：

I would find it more readable to create one ResultModel subcollection for the results without contact key and another subcollection for the results with a contact key. I suspect such an approach will also be easier to maintain over time than one single Linq expression.

You could first group the temporary results so that one subcollection contains temporary results without contacts, and the other subcollection contains temporary results with contacts.

Such a grouping is easily obtained by using .ToLookup() with the appropriate condition for contains contacts:

var tempResultByContainingContacts = tempResultSet
    .ToLookup(x => x.Contacts != null && x.Contacts.Any());

(I added the && x.Contacts.Any() condition. If that does not suit your use case, you can leave it out.)

After defining the lookup table, you have:

temporary results without contacts in tempResultByContainingContacts[false]
temporary results with contacts in tempResultByContainingContacts[true]

Now, you can calculate one resulting subcollection based on the temporary results without contacts:

var resultWithoutContactKey = tempResultByContainingContacts[false]
    .Select(x => new ResultModel
    {
        Key1 = x.Key1,
        Key2 = x.Key2
    });

as well as one resulting subcollection based on the temporary results with contacts:

var resultWithContactKey = tempResultByContainingContacts[true]
    .SelectMany(x => x.Contacts
        .Select(contact => new ResultModel
        {
            Key1 = x.Key1,
            Key2 = x.Key2,
            ContactKey = contact.id
        }));

Lastly, the two resulting subcollections are concatenated to create result:

var result = resultWithoutContactKey
    .Concat(resultWithContactKey)
    .ToList();

Note:

By defining two subcollections and concatenating them, the resulting order will be different from the original order.
Good naming conventions are important. I tried to select somewhat descriptive names, but have to admit, it was difficult especially for the lookup table. Hopefully you manage to find something more appropriate.

Suggestion after question update

I have not made any changes to the logic in your new, main .Select() block, except for adding one line to verify that [...].baseTable5 != null.

I would suggest splitting up the temporary subcollections and creating the resulting subcollections, following the pattern I have described above:

var tempResult = _context.baseTable1
    .Where(h => h.id == Id)
    .Select(lev => new 
    { 
        Contact = 
            lev.baseTable2 != null && 
            lev.baseTable2.baseTable3 != null && 
            lev.baseTable2.baseTable3.baseTable5 != null && // this line was added
            lev.baseTable2.baseTable3.baseTable5
                .Any(h => h.contact != null) 
            ? lev.baseTable2.baseTable3.baseTable5
                .Where(h => h.contact != null)
                .GroupBy(h => h.contact)
                .Select(c => c.FirstOrDefault().contact) 
            : null, 
        Key1 =
            lev.office != null 
            ? lev.office.id, 
            : null, 
        Key2 =
            lev.baseTable2 != null && 
            lev.baseTable2.baseTable3 != null && 
            lev.baseTable2.baseTable3.baseTable4 != null 
            ? lev.baseTable2.baseTable3.baseTable4.id 
            : null
        })
    .ToList();

var tempResultByContainingContacts = tempResult
    .ToLookup(x => x.Contact != null && x.Contact.Any());
    
var resultWithoutContactKey = tempResultByContainingContacts[false]
    .Select(x => new FinalModel
    {
        Key1 = x.Key1,
        Key2 = x.Key2
    });

var resultWithContactKey = tempResultByContainingContacts[true]
    .SelectMany(x => x.Contact
        .Select(contact => new FinalModel
        {
            ContactKey = contact.id,
            Key1 = x.Key1,
            Key2 = x.Key2
        }));

var result = resultWithoutContactKey
    .Concat(resultWithContactKey)
    .ToList();

return result;

If possible, I would also suggest simplifying the new main .Select() block. I am not sure whether ?. notation can be translated when querying the database, though. If it cannot, please ignore the following suggestion:

var tempResult = _context.baseTable1
    .Where(h => h.id == Id)
    .Select(lev => new 
    { 
        Contact = lev.baseTable2?.baseTable3?.baseTable5?
            .Where(h => h.contact != null)
            .GroupBy(h => h.contact)
            .Select(c => c.FirstOrDefault().contact), 
        Key1 = lev.office?.id, 
        Key2 = lev.baseTable2?.baseTable3?.baseTable4?.id
    })
    .ToList();