CODEWITHSUNDEEP
c#linq
user2943399
user2943399

Reputation: 31

How to combine the multiple part linq into one query?

Operator should be ‘AND’ and not a ‘OR’.

I am trying to refactor the following code and i understood the following way of writing linq query may not be the correct way. Can somone advice me how to combine the following into one query. 

        AllCompany.Where(itm => itm != null).Distinct().ToList();

        if (AllCompany.Count > 0)
        {
            //COMPANY NAME
            if (isfldCompanyName)
            {
                AllCompany = AllCompany.Where(company => company["Company Name"].StartsWith(fldCompanyName)).ToList();
            }
            //SECTOR
            if (isfldSector)
            {
                AllCompany = AllCompany.Where(company => fldSector.Intersect(company["Sectors"].Split('|')).Any()).ToList();
            }
            //LOCATION
            if (isfldLocation)
            {
                AllCompany = AllCompany.Where(company => fldLocation.Intersect(company["Location"].Split('|')).Any()).ToList();

            }
            //CREATED DATE
            if (isfldcreatedDate)
            {
                AllCompany = AllCompany.Where(company => company.Statistics.Created >= createdDate).ToList();
            }
            //LAST UPDATED DATE
            if (isfldUpdatedDate)
            {
                AllCompany = AllCompany.Where(company => company.Statistics.Updated >= updatedDate).ToList();
            }

            //Allow Placements
            if (isfldEmployerLevel)
            {
                fldEmployerLevel = (fldEmployerLevel == "Yes") ? "1" : "";

                AllCompany = AllCompany.Where(company => company["Allow Placements"].ToString() == fldEmployerLevel).ToList();
            }

Upvotes: 0

Views: 81

Answers (3)

Rob Lyndon
Rob Lyndon

Reputation: 12651

Here's a sneaky trick. If you define the following extension method in its own static class:

public virtual IEnumerable<T> WhereAll(params Expression<Predicate<T> filters)
{
    return filters.Aggregate(dbSet, (acc, element) => acc.Where(element));
}

then you can write

var result = AllCompany.WhereAll(itm => itm != null,
    company => !isfldCompanyName || company["Company Name"].StartsWith(fldCompanyName),
    company => !isfldSectorn || fldSector.Intersect(company["Sectors"].Split('|')).Any(),
    company => !isfldLocation || fldLocation.Intersect(company["Location"].Split('|')).Any(),
    company => !isfldcreatedDate || company.Statistics.Created >= createdDate,
    company => !isfldUpdatedDate || company.Statistics.Updated >= updatedDate,
    company => !isfldEmployerLevel || company["Allow Placements"].ToString() == fldEmployerLevel)
    .Distinct()
    .ToList();

Upvotes: 0

Grzegorz W
Grzegorz W

Reputation: 3517

Firstly, unless AllCompany is of some magic custom type, the first line gives you nothing. Also I have a doubt that Distinctworks the way You want it to. I don't know the type of AllCompany but I would guess it gives you only reference distinction.

Either way here'w what I think You want:

fldEmployerLevel = (fldEmployerLevel == "Yes") ? "1" : "";

var result = AllCompany.Where(itm => itm != null)
    .Where(company => !isfldCompanyName || company["Company Name"].StartsWith(fldCompanyName))
    .Where(company => !isfldSector|| fldSector.Intersect(company["Sectors"].Split('|')).Any())
    .Where(company => !isfldLocation|| fldLocation.Intersect(company["Location"].Split('|')).Any())
    .Where(company => !isfldcreatedDate|| company.Statistics.Created >= createdDate)
    .Where(company => !isfldUpdatedDate|| company.Statistics.Updated >= updatedDate)
    .Where(company => !isfldEmployerLevel|| company["Allow Placements"].ToString() == fldEmployerLevel)
    .Distinct()
    .ToList();

Edit:

I moved Distinct to the end of the query to optimize the processing.

Upvotes: 2

user1788956
user1788956

Reputation:

How about trying like this; 

AllCompany = AllCompany .Where(company => (company => company.Statistics.Created >= createdDate)) && (company.Statistics.Updated >= updatedDate));

If every part of query is optional (like created date, last update date..) then you can build linq query string.

Upvotes: 0

Related Questions